Re: Help:Changing the Source column name dynamically

From: <pobox002_at_bebub.com>
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

-- 
MJB
Received on Wed Jul 20 2005 - 14:27:27 CEST

Original text of this message