9 Replies Latest reply: Jul 2, 2012 10:00 AM by bostonmacosx RSS

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

    bostonmacosx
      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