PLS-00372: In a procedure, RETURN statement cannot contain an expression
I'm trying to make a function that will allow me to do a stacked bar chat in APEX. I already tried in the APEX forum to no avail so I'm moving the SQL part of my issue of here to the PL/SQL forums.
DECLARE
l_var VARCHAR2(32767);
BEGIN
l_var := 'SELECT NULL LINK,to_char(open_time,"YYYY-Q") DATIME,';
for r1 in (
SELECT DISTINCT
DECODE(REPAIR_TYPE,'DEPARTMENT PURCHASE','DEPARTMENT','USER OWNED','USER OWNED','BCCR','BCCR','','NO ENTRY')
'-'
DECODE(HARDWARE_WARRANTY_STATUS,'YES','WARRANTY','NO','NON-WARRANTY','UNKNOWN','UNKNOWN','','N/A') as STATE
from
TABLE
where
(ASSIGNMENT = 'HARDWARE REPAIR' or ASSIGNMENT = 'CONTRACTED HARDWARE REPAIR')
and
(HW_HARDWARE_TYPE_BC='LAPTOP' OR HW_HARDWARE_TYPE_BC='DESKTOP') and open_time>'7/1/2011'
)
LOOP
l_var := l_var || 'sum(decode(state,''' || r1.state || ''',"1",0)) ' || r1.state || ',';
END LOOP;
l_var := rtrim(l_var, ',');
l_var := l_var || ' FROM SMINCREQ GROUP BY to_char(open_time,"YYYY-Q") order by to_char(open_time,"YYYY-Q")';
RETURN l_var;
END
AND THE ERROR
ORA-06550: line 32, column 1:
PLS-00372: In a procedure, RETURN statement cannot contain an expression
ORA-06550: line 32, column 1:
PL/SQL: Statement ignored