1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
| DECLARE
X VARCHAR2(32767);
BEGIN
X := '
SELECT to_char(days.n, ''DD/MM/YYYY HH24'') as name,
NVL(ROUND(AVG(s1.HIGH),2),-1) HIGH
FROM (SELECT TO_DATE('''||:P8_DATE_DEBUT||''', ''DD/MM/YYYYHH24'')+(level-1)/TO_NUMBER('''||:P8_ECHELLE||''') as n
FROM dual
CONNECT BY level <= TO_DATE('''||:P8_DATE_FIN||''', ''DD/MM/YYYYHH24'')+1-TO_DATE('''||:P8_DATE_DEBUT||''', ''DD/MM/YYYYHH24''))*TO_NUMBER('''||:P8_ECHELLE||''')
) days
LEFT OUTER JOIN
(SELECT trunc(RAW_DATE, :P8_TRUNC) as HO,
NVL(ROUND(AVG(VALID_VALUE),2), 0) as HIGH
FROM '||
dbms_assert.sql_object_name('SAFEGE.MSR_'||NVL(:P8_ECHELLE_EMMA,'4245')) -- NVL better than COALESCE for a simple Null check
||'@EMMASAFE.NTSIVOA'
||'
WHERE RAW_DATE >= TO_DATE('''||:P8_DATE_DEBUT||''', ''DD/MM/YYYY'') -- avoiding BETWEEN means less messing about with time components
AND RAW_DATE < TO_DATE('''||:P8_DATE_FIN||''', ''DD/MM/YYYY'')+1 -- just need to use less than target date+1 instead
AND VALIDITY = 1
GROUP BY trunc(RAW_DATE, '''||:P8_TRUNC||''')
) s1
ON days.n = S1.HO
GROUP BY days.n
ORDER BY days.n';
RETURN X;
END; |
Partager