Monday, May 11, 2009

Selecting from a Variable TableName


A question was posted on ITToolBox asking how to select from a table based on a column. You can't use a CASE clause in the FROM statement, but the following -- though convoluted -- does work.

'New_Value' in the 'Column' statement will create a new variable named 'Table' that can then be used in the final query.
Set Verify Off Echo Off Linesize 200

Accept Key Prompt 'Enter the key for the table you wish to view (EmpNo, DeptNo): '

/*----------------------------------------------------------*/
/* Turn off the terminal so this query doesn't display */
/*----------------------------------------------------------*/
Set Term Off
Column Table_From_Case New_Value Table

Select Case Upper('&Key') When 'EMPNO' Then 'EMP'
When 'DEPTNO' Then 'DEPT'
End
As
Table_From_Case
From Dual;

Set Term On

/*----------------------------------------------------------*/
/* Select from the tablename set up with COLUMN/NEW_VALUE */
/*----------------------------------------------------------*/

Select *
From &Table
Where Rownum < 3;

No comments:

Post a Comment