Dynamic Decode function - is it possible ?

[nkshah]

thread186-1236075

i have exactly same problem of above Thread but it did not mention answer.

 

[ChrisHunt]

That thread link's not working. Maybe you could explain your problem?

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[karluk]

The subject of thread186-1236075 is dynamically translating code values into a more readable form for reporting purposes. I'm not sure why the link posted above isn't working.

 

[LKBrwnDBA]

Try this for starters:

var xrefc refcursor;

DECLARE
  v_str  VARCHAR2(10000) := 'SELECT JOB';
BEGIN
  FOR x IN (SELECT   DISTINCT deptno
            FROM     emp
            ORDER BY 1)
  LOOP
    v_str := v_str
             ||Chr(10)
             ||','
             ||'SUM(DECODE(E.DEPTNO,'''
             ||x.deptno
             ||''',e.sal,0)) AS "'
             ||x.deptno
             ||'"';
  END LOOP;
  
  v_str := v_str
           ||Chr(10)
           ||'FROM EMP E'
           ||Chr(10)
           ||'GROUP BY JOB ORDER BY JOB';
  
  dbms_output.Put_line(v_str);
  
  OPEN :xrefc FOR v_str;
END;
/
print :xrefc

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb