Hi
I have a function but I get syntax errors.
DECLARE
X VARCHAR2(32767);
BEGIN
X := '
SELECT to_char(days.n, ''DD/MM/YYYY HH24'') 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||''') n
FROM dual
CONNECT BY level <=
(SELECT ( TO_DATE('''||:P8_DATE_FIN||''', ''DD/MM/YYYYHH24'') + 1 - TO_DATE('''||:P8_DATE_DEBUT||''', ''DD/MM/YYYYHH24'') ) * TO_NUMBER('''||:P8_ECHELLE||''')
FROM dual
)
) days,
(SELECT trunc(DATE1, '''||:P8_TRUNC||''') HO,
NVL(ROUND(AVG(VALEUR),2), 0) HIGH
FROM ' || dbms_assert.sql_object_name('EVV_' || coalesce(:P184_ENAME, 'D34'))
WHERE DATE1 BETWEEN TO_DATE('''||:P8_DATE_DEBUT||'000000'', ''DD/MM/YYYYHH24MISS'') AND TO_DATE('''||:P8_DATE_FIN||'235959'', ''DD/MM/YYYYHH24MISS'')
AND CLEF_VAR =
(SELECT CLEF_VAR FROM SITE_ECHELLE WHERE SITE = '''||coalesce(:P184_ENAME, 'D34')||''')
GROUP BY trunc(date1, '''||:P8_TRUNC||''') ) s1
WHERE days.n =S1.HO (+)
GROUP BY days.n
ORDER BY days.n';
return X;
end;
The error is on this line :
WHERE DATE1 BETWEEN TO_DATE('''||:P8_DATE_DEBUT||'000000'', ''DD/MM/YYYYHH24MISS'') AND TO_DATE('''||:P8_DATE_FIN||'235959'', ''DD/MM/YYYYHH24MISS'')
and the error is :
ORA-06550: Ligne 18, colonne 4 : PLS-00103: Symbole "WHERE" rencontré à la place d'un des symboles suivants : . ( * % & = - + ; < / > at in is mod remainder not rem <exposant (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || member submultiset ORA-06550: Ligne 18, colonne 54 : PLS-00103: Symbole "000000" rencontré à la place d'un des symboles suivants : ) , * & = - + < / > at in is mod remainder not rem => <exposant (**)> <> or != or ~= >= <= <> and or like like2 lik
I cannot manage to solve this error. Any help welcome.
Best regards,
Christian