Use for loop in Oracle SQL developer

Question

The following code gives me this error

ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause:
*Action: Error at Line: 26 Column: 1

for i in (7,14,21,28,35) 
loop
  Select CODE_ACCOUNTING_METHOD,
         trunc(sysdate-i, 'iw')           as week_report,
         sum(ELI_12MOB)                   as eligible_client,
         sum(client)                      as total_client,
         (sum(ELI_12MOB)/sum(client))*100 as eligible_rate
   from  pm_eli_base
   where week_start = trunc(sysdate-(360+i), 'iw')
     and code_accounting_method != 'CL'
   group by CODE_ACCOUNTING_METHOD, trunc(sysdate-i, 'iw')
   union all
   select 'Total', 
           trunc(sysdate-i, 'iw') as week_report,
           sum(ELI_12MOB)         as eligible_client,
           sum(client)            as total_client,
           (sum(ELI_12MOB)/sum(client))*100
     from pm_eli_base
    where week_start = trunc(sysdate-(360+i), 'iw')
      and code_accounting_method != 'CL'
    group by 'Total', trunc(sysdate-i, 'iw')
end loop;

Please help me with this case

This is the result I want to achieve

zundarz · Accepted Answer · 2019-08-24 12:08:58Z

Since you're using SQLDeveloper, you can declare your query in a cursor and call your cursor on select values of i

DECLARE 

CURSOR code_accounting_method(i number)
is
Select CODE_ACCOUNTING_METHOD,
         trunc(sysdate-i, 'iw')           as week_report,
         sum(ELI_12MOB)                   as eligible_client,
         sum(client)                      as total_client,
         (sum(ELI_12MOB)/sum(client))*100 as eligible_rate
   from  pm_eli_base
   where week_start = trunc(sysdate-(360+i), 'iw')
     and code_accounting_method != 'CL'
   group by CODE_ACCOUNTING_METHOD, trunc(sysdate-i, 'iw')
   union all
   select 'Total', 
           trunc(sysdate-i, 'iw') as week_report,
           sum(ELI_12MOB)         as eligible_client,
           sum(client)            as total_client,
           (sum(ELI_12MOB)/sum(client))*100
     from pm_eli_base
    where week_start = trunc(sysdate-(360+i), 'iw')
      and code_accounting_method != 'CL'
    group by 'Total', trunc(sysdate-i, 'iw');

 cam_record  CODE_ACCOUNTING_METHOD%rowtype;

BEGIN

        for i in 1 .. 35 loop
          if i in (7,14,21,28,35) then
                open code_accounting_method(i);
                fetch code_accounting_method into  cam_record;
                close code_accounting_method;
               --Now report the fields that are in cam_record;
               -- such as 
              dbms_output.put_line(cam_record.week_report);

          end if;
        end loop;
END;

btpys · Accepted Answer · 2019-08-23 10:36:09Z

1

You can't do:

for i in (7,14,21,28,35)

do:

SQL> 
declare
   type nt_type is table of number;
   nt nt_type := nt_type (7,14,21,28,35);
begin
for i in 1..nt.count loop
   dbms_output.put_line(nt(i));
end loop;
end;
/

7
14
21
28
35

PL/SQL procedure successfully completed.

answered Aug 23, 2019 at 10:36

btpys

3661 silver badge6 bronze badges

2 Comments

Dat Nguyen Thanh Over a year ago

thanks bro, it gives me this error when I put the select statement into the loop Error report - ORA-06550: line 6, column 1: PLS-00428: an INTO clause is expected in this SELECT statement 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

btpys Over a year ago

As Tejash said, you need to assign the results of your query into variables with the INTO clause.

Popeye · Accepted Answer · 2019-08-23 12:05:23Z

1

First of all you missed ; at the end of the query.

Second what are you trying to achieve by select query? Use INTO to assign values to some variable.

Update: You can use following technique apart from what @btpys suggested.

FOR J IN 1..5 LOOP
I := J*7;
...
...
END LOOP;

Cheers!!

edited Aug 23, 2019 at 12:05

answered Aug 23, 2019 at 10:31

Popeye

36k4 gold badges12 silver badges31 bronze badges

1 Comment

Dat Nguyen Thanh Over a year ago

i have added the result that i want to achieve

Adder · Accepted Answer · 2019-08-23 13:53:42Z

0

I think a for loop is the wrong tool. You should create a view, and use union all and copy and paste and replace the i by the values 7,14,21,28,35 in the selects manually. So that you have a view with a union all 5x2 times = 10 select statements.

answered Aug 23, 2019 at 13:53

Adder

5,8982 gold badges35 silver badges60 bronze badges

1 Comment

Dat Nguyen Thanh Over a year ago

I was doing exactly that, but I thought it didnt look so clean lol

Collectives™ on Stack Overflow

Use for loop in Oracle SQL developer

4 Answers 4

Comments

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related