Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

pl/SQL for loop

DECLARE 
   a number(2);
  cnt number;
BEGIN 
    
   FOR a in 01 .. 12 LOOP 
     
     SELECT count(*) 
     INTO cnt
     FROM ECMS_ONLINE_TRANSACTION eoa
     WHERE eoa.CREATETIME BETWEEN TO_DATE('2021-01-01 01:00:00','YYYY-MM-DD HH24:MI:SS')
                              AND TO_DATE('2021-01-01 01:59:59','YYYY-MM-DD HH24:MI:SS'); 
 
     dbms_output.put_line(' month: ' || a || ' time stamp 01:00:00- 01:59:59  number of transaction :' || cnt ); 
   END LOOP; 
END;

i having an issue on changing the month according to the loop in where the clause i have used between function the month of todate function have to be changed according to the loop if a becomes 1 then the month in where clause has to be changed according to 1 if a becomes 2 the month have be changed as 2

foe example

if a =1

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

WHERE eoa.CREATETIME BETWEEN TO_DATE('2021-01-01 01:00:00','YYYY-MM-DD HH24:MI:SS')
                         AND TO_DATE('2021-01-01 01:59:59','YYYY-MM-DD HH24:MI:SS'); 

if a =2

WHERE eoa.CREATETIME BETWEEN TO_DATE('2021-02-01 01:00:00','YYYY-MM-DD HH24:MI:SS')
                         AND TO_DATE('2021-02-01 01:59:59','YYYY-MM-DD HH24:MI:SS'); 

i have used timestamp variable

>Solution :

Use this condition:

WHERE eoa.CREATETIME BETWEEN TO_DATE('2021-' || lpad(a, 2, '0') || '-01 01:00:00','YYYY-MM-DD HH24:MI:SS')
                         AND TO_DATE('2021-' || lpad(a, 2, '0') || '-01 01:59:59','YYYY-MM-DD HH24:MI:SS'); 

It might be simpler to write this (but it won’t use an index on CREATETIME if you have one):

WHERE to_char(eoa.CREATETIME, 'MM') = a
  AND to_char(eoa.CREATETIME, 'HH24' = '01'
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading