Re: Variable Order By clause

Definately spoke too soon. I forgot that a decode must return the same datatype as the value being checked. In this case seq_no is a number (same as ord1) and issue_type is a VARCHAR2. Hence the error.

I resolved that problem by converting the columns using to_char, and setting ord1 to a varchar2.

Thanks for all your help,

~ ~ Dave

declare

   ord1 varchar2(50) := '2';

   CURSOR c_Known_Issues IS

       SELECT
         seq_no,
         issue_type,
         issue_date
      FROM   known_issues
      WHERE  system_id = 99
      ORDER BY 
       DECODE(ord1, '1',to_char(seq_no,'09999'), 
		'2',issue_type, 
		to_char(issue_date,'yyyymmdd'));
       

begin

   FOR known_Issue IN c_Known_Issues LOOP

      dbms_output.put_line(known_Issue.seq_No || '...'||known_Issue.issue_type || '...' || known_Issue.issue_date);

   END LOOP; end;
. Received on Wed Aug 11 1999 - 22:06:00 CEST