Re: Help:Changing the Source column name dynamically
Date: 20 Jul 2005 05:27:27 -0700
Message-ID: <1121862447.788028.27600_at_g49g2000cwa.googlegroups.com>
You could also use the CASE statement in your queries and return a different column based on the parameter value and just use that column in your report.
SQL> select * from emp where deptno = 20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ---------- --------- ------ --------- ------ ------ ------ 7369 SMITH CLERK 7902 17-DEC-80 800 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20
SQL> var param varchar2(10)
SQL> exec :param := 'empno'
PL/SQL procedure successfully completed.
SQL> select ename, job, hiredate,
2 case :param when 'empno' then empno
3 when 'mgr' then mgr 4 when 'sal' then sal 5 when 'comm' then comm
6 end col
7 from emp
8 where deptno = 20;
ENAME JOB HIREDATE COL
---------- --------- --------- ------
SMITH CLERK 17-DEC-80 7369 JONES MANAGER 02-APR-81 7566 SCOTT ANALYST 09-DEC-82 7788 ADAMS CLERK 12-JAN-83 7876 FORD ANALYST 03-DEC-81 7902
SQL> exec :param := 'sal'
PL/SQL procedure successfully completed.
SQL> /
ENAME JOB HIREDATE COL
---------- --------- --------- ------
SMITH CLERK 17-DEC-80 800 JONES MANAGER 02-APR-81 2975 SCOTT ANALYST 09-DEC-82 3000 ADAMS CLERK 12-JAN-83 1100 FORD ANALYST 03-DEC-81 3000
SQL> exec :param := 'mgr'
PL/SQL procedure successfully completed.
SQL> /
ENAME JOB HIREDATE COL
---------- --------- --------- ------
SMITH CLERK 17-DEC-80 7902 JONES MANAGER 02-APR-81 7839 SCOTT ANALYST 09-DEC-82 7566 ADAMS CLERK 12-JAN-83 7788 FORD ANALYST 03-DEC-81 7566
SQL> Hth
-- MJBReceived on Wed Jul 20 2005 - 14:27:27 CEST