1

I have a query based on a date with get me the data I need for a given day (lets say sysdate-1):

SELECT TO_CHAR(START_DATE, 'YYYY-MM-DD') "DAY",
  TO_CHAR(TRUNC(MOD(ROUND(AVG((END_DATE - START_DATE)*86400),0),3600)/60),'FM00') || ':'
  || TO_CHAR(MOD(ROUND(AVG((END_DATE - START_DATE)*86400),0),60),'FM00') "DURATION (mm:ss)"
FROM UI.UIS_T_DIFFUSION
WHERE APPID IN ('INT', 'OUT', 'XMD','ARPUX')
AND PSTATE = 'OK'
AND TO_CHAR(START_DATE, 'DD-MM-YYYY') = TO_CHAR( sysdate-1, 'DD-MM-YYYY')
AND ROWNUM <= 22
GROUP BY TO_CHAR(START_DATE, 'YYYY-MM-DD');

Gives me this (as expected):

╔════════════╦══════════╗
║    DAY     ║ DURATION ║
╠════════════╬══════════╣
║ 2016-02-28 ║       303║
╚════════════╩══════════╝

Now I'm trying to add a loop to get the results for each day since 10-10-2015. Somehting like this:

╔═══════════╦══════════╗
║    DAY    ║ DURATION ║
╠═══════════╬══════════╣
║ 2016-02-28║       303║
╠═══════════╬══════════╣
║ 2016-02-27║       294║
╠═══════════╬══════════╣
║        ...║       ...║
╠═══════════╬══════════╣
║ 2015-10-10║        99║
╚═══════════╩══════════╝

I've tried to put the query inside a loop:

DECLARE
  i NUMBER := 0;
BEGIN
  WHILE i <= 142
  LOOP
    i := i+1;
    SELECT TO_CHAR(START_DATE, 'YYYY-MM-DD') "DAY",
    TO_CHAR(TRUNC(MOD(ROUND(AVG((END_DATE - START_DATE)*86400),0),3600)/60),'FM00') || ':'
    || TO_CHAR(MOD(ROUND(AVG((END_DATE - START_DATE)*86400),0),60),'FM00') "DURATION (mm:ss)"
    FROM UI.UIS_T_DIFFUSION
    WHERE APPID IN ('INT', 'OUT', 'XMD','ARPUX')
    AND PSTATE = 'OK'
    AND TO_CHAR(START_DATE, 'DD-MM-YYYY') = TO_CHAR(sysdate - i, 'DD-MM-YYYY')
    AND ROWNUM <= 22
    GROUP BY TO_CHAR(START_DATE, 'YYYY-MM-DD');
  END LOOP;
END;

but I'm getting this error:

Error report -
ORA-06550: line 7, column 5:
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:

Can anyone tell me how to accomplish this?

Improve this question

3 Answers 3

Reset to default
1

While bastihermann gave you the query to get all of those values in a single result set, if you want to understand the issue with your pl/sql block, the following should simplify it for you. The error relates to the fact that, in pl/sql you need to select INTO local variables to contain the data for reference within the code.

To correct (and simplify with a FOR LOOP) your block:

DECLARE
  l_day      varchar2(12);
  l_duration varchar2(30);;
BEGIN
  -- don't need to declare a variable for an integer counter in a for loop
  For i in 1..142
  LOOP
    SELECT TO_CHAR(START_DATE, 'YYYY-MM-DD'),
    TO_CHAR(TRUNC(MOD(ROUND(AVG((END_DATE - START_DATE)*86400),0),3600)/60),'FM00') || ':'
    || TO_CHAR(MOD(ROUND(AVG((END_DATE - START_DATE)*86400),0),60),'FM00') 
    INTO l_Day, l_duration
    FROM UI.UIS_T_DIFFUSION
    WHERE APPID IN ('INT', 'OUT', 'XMD','ARPUX')
    AND PSTATE = 'OK'
    AND TO_CHAR(START_DATE, 'DD-MM-YYYY') = TO_CHAR(sysdate - i, 'DD-MM-YYYY')
    AND ROWNUM <= 22
    GROUP BY TO_CHAR(START_DATE, 'YYYY-MM-DD');
    -- and here you would do something with those returned values, or there isn't much point to this loop. 
  END LOOP;
END;

Assuming you needed to do something with those values and want even more efficient, you could simplify even further with a cursor loop;

BEGIN
  -- don't need to declare a variable for an integer counter in a for loop
  For i_record IN
    (SELECT TO_CHAR(START_DATE, 'YYYY-MM-DD') the_Day,
    TO_CHAR(TRUNC(MOD(ROUND(AVG((END_DATE - START_DATE)*86400),0),3600)/60),'FM00') || ':'
    || TO_CHAR(MOD(ROUND(AVG((END_DATE - START_DATE)*86400),0),60),'FM00') the_duration
    FROM UI.UIS_T_DIFFUSION
    WHERE APPID IN ('INT', 'OUT', 'XMD','ARPUX')
    AND PSTATE = 'OK'
    AND TO_CHAR(START_DATE, 'DD-MM-YYYY') <= TO_CHAR( sysdate, 'DD-MM-YYYY')
    AND TO_CHAR(START_DATE, 'DD-MM-YYYY') >= TO_CHAR( sysdate-142, 'DD-MM-YYYY')  
    AND ROWNUM <= 22
    GROUP BY TO_CHAR(START_DATE, 'YYYY-MM-DD')
    ORDER BY to_char(start_date,'dd-mm-yyyy')
    )
  LOOP
    -- and here you would do something with those returned values, but reference them by record_name.field_value. 
    -- For now I will put in the NULL; command to let this compile as a loop must have at least one command inside.
    NULL;
  END LOOP;
END;

Hope that helps

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for your input. It did help.
1

First you need a "date generator"

select trunc(sysdate - level) as my_date
from dual
connect by level <= sysdate - to_date('10-10-2015','dd-mm-yyyy')

MY_DATE  
----------
2016/02/28 
2016/02/27 
2016/02/26 
....
....
2015/10/12 
2015/10/11 
2015/10/10 

 142 rows selected

an then you need to plug this generator into your query

If you are using Oracle 12c this is very easy with the help of lateral inline view

SELECT *
FROM (
    select trunc(sysdate - level) as my_date
    from dual
    connect by level <= sysdate - to_date('10-10-2015','dd-mm-yyyy')
) date_generator,
LATERAL (
  /* your query goes here */
   SELECT TO_CHAR(START_DATE, 'YYYY-MM-DD') "DAY",
   ....
   AND START_DATE >= date_generator.my_date
   AND START_DATE < date_generator.my_date + 1
   AND ROWNUM <= 22
   GROUP BY TO_CHAR(START_DATE, 'YYYY-MM-DD');
)

If you are on Oracle 11 or 10, it is still possible, but more complicated;

SELECT TO_CHAR(START_DATE, 'YYYY-MM-DD') "DAY",
  TO_CHAR(TRUNC(MOD(ROUND(AVG((END_DATE - START_DATE)*86400),0),3600)/60),'FM00') || ':'
  || TO_CHAR(MOD(ROUND(AVG((END_DATE - START_DATE)*86400),0),60),'FM00') "DURATION (mm:ss)"
FROM (
        SELECT t.* ,
               row_number() over (partition by date_generator.my_date) rn
        FROM UI.UIS_T_DIFFUSION t
        JOIN (
                select trunc(sysdate - level) as my_date
                from dual
                connect by level <= sysdate - to_date('10-10-2015','dd-mm-yyyy')
            ) date_generator
        ON ( t.UIS_T_DIFFUSION >= date_generator.my_date
             AND t.UIS_T_DIFFUSION < date_generator.my_date + 1 )
        WHERE APPID IN ('INT', 'OUT', 'XMD','ARPUX')
        AND PSTATE = 'OK'
)
WHERE rn <= 22
GROUP BY TO_CHAR(START_DATE, 'YYYY-MM-DD');

First remark - while you are not using ORDER BY clause, your query WHERE rownum <=22 is not deterministic - it may return different results on each run, because it picks 22 rows from the table according to their physical order in the table. But the physical order of rows can change in the time, Oracle doesn't guarantee any order unless ORDER BY clause is used, so your query .... returns random results.

Second remark - never use this:

AND TO_CHAR(START_DATE, 'DD-MM-YYYY') = TO_CHAR(sysdate - i, 'DD-MM-YYYY')

this prevents the database from using indices, and this may cause performance problems.
Use this instead:

START_DATE >= trunc(sysdate - i) AND START_DATE < trunc(sysdate - i + 1)
Improve this answer

1 Comment

Thanks a lot for your input.
0

If i got you right you dont need a loop for this. I guess this will do the trick:

SELECT TO_CHAR(START_DATE, 'YYYY-MM-DD') "DAY",
TO_CHAR(TRUNC(MOD(ROUND(AVG((END_DATE - START_DATE)*86400),0),3600)/60),'FM00') || ':'
|| TO_CHAR(MOD(ROUND(AVG((END_DATE - START_DATE)*86400),0),60),'FM00') "DURATION (mm:ss)"
FROM UI.UIS_T_DIFFUSION
WHERE APPID IN ('INT', 'OUT', 'XMD','ARPUX')
AND PSTATE = 'OK'
AND TO_CHAR(START_DATE, 'DD-MM-YYYY') <= TO_CHAR( sysdate, 'DD-MM-YYYY')
AND TO_CHAR(START_DATE, 'DD-MM-YYYY') >= TO_CHAR( sysdate-142, 'DD-MM-YYYY')
AND ROWNUM <= 22
GROUP BY TO_CHAR(START_DATE, 'YYYY-MM-DD');

Here you get every day between now and now-142 days.

Improve this answer

1 Comment

Thanks for your input. Your query does work, but it doesn't get the data I need. It limit the overall result to 22 rows, while I need to limit the daily results to 22 rows.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.