This discussion is archived
9 Replies Latest reply: Jul 2, 2012 8:00 AM by bostonmacosx RSS

PLS-00372: In a procedure, RETURN statement cannot contain an expression

bostonmacosx Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points