Task: there are 100 entries in the table (rating by points). It is necessary to display 7 records with points, where the 4th line is usr_id in the select, 1 2 3 - records in which there are more points than the 4 lines and 5 6 7 records, where the points are less than the 4 lines. enter image description here In the report you need to display, highlighted by a frame, starting from the 13th line. I try to make a report using a temporary table, but nothing comes out (with an select, an error occurs).

declare rn decimal(10) DECLARE LOCAL TEMPORARY TABLE table1 (point_group varchar(10), point_code varchar(10), point_name varchar(10), USR_ID varchar(10, fio varchar(10), ball int, zball int, rn int) begin insert into table1 select point_group, point_code, point_name, USR_ID, FIO, BALL, ZBALL, rownum() over (order by ball desc) as rn from a3_user_rating_new01 select rn = rn from table1 where USR_ID = 371 select * from table1 where rn between (rn-3) and (rn+3)

Error text: Error starting at line: 45 in command - declare rn decimal (10)

DECLARE LOCAL TEMPORARY TABLE table1 (point_group varchar (10), point_code varchar (10), point_name varchar (10), USR_ID varchar (10, fio varchar (10), ball int, zball int, rn int)

begin

insert into table1 select point_group, point_code, point_name, USR_ID, FIO, BALL, ZBALL, rownum () over (order by ball desc) as rn from a3_user_rating_new01

select rn = rn from table1 where USR_ID = 371 select * from table1 where rn between (rn-3) and (rn + 3) Error report - ORA-06550: line 3, column 1: PLS-00103: Encountered the symbol "DECLARE "when expecting one of the following:

: =; not null default character 06550. 00000 - "line% s, column% s: \ n% s" * Cause: Usually a PL / SQL compilation error. * Action:

  • one
    the text of the error probably would not hurt to give. - aleksandr barakin
  • Here is the line select rn = rn from table1 where USR_ID = 371 should most likely be this select rn into rn from table1 where USR_ID = 371 . In general, it is better to provide the data, I do not believe that we need a temporary table here - Mike
  • Added a description of the table in the screenshot and the desired result. Judging by the error, the error occurs already on line 3, where I declare a temporary table. I will be happy to hear other decisions :) - Vyacheslav Groshenkov
  • I didn’t see that the tables would be made as declare, I can create, although I didn’t try to do temporary xs how it works ... But it seems to me that in the Oracle they are constantly announced such tables once, just for the beginning of any transaction they are for it are empty and after commit / rollback everything that was lost in them, like that. And now I will try to make a request - Mike

1 answer 1

This option:

 select * from ( select a.*,rownum N, max(decode(usr_id,371,rownum,0)) over() M from (select * from table1 order by ball desc ) a ) where n between M-3 and M+3
  • Thank!!!!!!!!! - Vyacheslav Groshenkov February
  • one
    @VyacheslavGroshenkov A little bit wrong, rank () must be replaced by row_number (), otherwise there will be more rows in the case of repeated ball - Mike