how to implement a for loop in oracle sql

Question

I am new to oracle sql and using this subquery to find out the no. of patients in the hospital.

 Select wtt.wrt_ip_num, max(wtt.wrt_sl_num) wrt_sl_num 
        FROM W_TRANSFER_TXN wtt
  where /*wtt.wrt_ip_num='IP/20/034619' and*/ trunc(wtt.wrt_in_dt)<=TO_DATE('29-Sep-2020','DD-MON-YYYY')
              and (wtt.wrt_out_dt is null or trunc(wtt.wrt_out_dt)>=TO_DATE('29-Sep-2020','DD-MON-YYYY'))
        group by wtt.wrt_ip_num);

This gives the value for 1 day. I wanted to run a for loop where my where clause is and wanted to loop the value for 10 days such that it gives the output for 10 days. Is that possible. If so how?

How do I provide sample dta desired results are that instead of giving the values for 1 day it will give the values for 15 days — sandeep pradhan
– sandeep pradhan, Commented Oct 16, 2020 at 12:51
Why are insisting to have a for loop ? If you want for some specific number of days can be done in a query also. what exactly you want with the loop ? — Sujitmohanty30
– Sujitmohanty30, Commented Oct 16, 2020 at 13:12
@sandeeppradhan . . . It may be clear to you what your data looks like. It might be clear to you what your query is doing. It is. not obvious to other people. You can insert text tables into the query with sample data to show other people what you intend. — Gordon Linoff
– Gordon Linoff, Commented Oct 16, 2020 at 13:20
You get only 1 date because only '29-Sep-2020' can be both <= '29-Sep-2020' AND >= '29-Sep-2020'. If you want a range you need: trunc(wtt.wrt_in_dt) between to_date (low end date) and to_date(high end date)". — Belayer
– Belayer, Commented Oct 16, 2020 at 16:09

Jon Heller · Accepted Answer · 2020-10-20 05:32:47Z

When you think you need a FOR loop in a database, many times you need to generate the values to be looped through and then join those values to a table. Oracle will handle the looping for you.

There are many tricks for generating data, the simplest one is selecting from DUAL and using UNION ALL to construct a small set of data:

select wtt.wrt_ip_num, max(wtt.wrt_sl_num) wrt_sl_num 
from w_transfer_txn wtt
join
(
    select date '2020-09-20' the_date from dual union all
    select date '2020-09-21' the_date from dual union all
    select date '2020-09-22' the_date from dual union all
    select date '2020-09-23' the_date from dual union all
    select date '2020-09-24' the_date from dual union all
    select date '2020-09-25' the_date from dual union all
    select date '2020-09-26' the_date from dual union all
    select date '2020-09-27' the_date from dual union all
    select date '2020-09-28' the_date from dual union all
    select date '2020-09-29' the_date from dual
) dates
    on trunc(wtt.wrt_in_dt) = dates.the_date
group by wtt.wrt_ip_num;

A shorter, but more cryptic, way to generate 10 dates is to use a hierarchical query like this:

select wtt.wrt_ip_num, max(wtt.wrt_sl_num) wrt_sl_num 
from w_transfer_txn wtt
join
(
    select date '2020-09-20' + level - 1 the_date
    from dual
    connect by level <= 10
) dates
    on trunc(wtt.wrt_in_dt) = dates.the_date
group by wtt.wrt_ip_num;

Collectives™ on Stack Overflow

how to implement a for loop in oracle sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related