using a result of subquery as a column name [message #270243] |
Wed, 26 September 2007 04:50  |
roblvl
Messages: 27 Registered: June 2003
|
Junior Member |
|
|
Hi
I have a Query like the following
SELECT * FROM TEST_DET
WHERE (SELECT M_TEST FROM T_COL_DATA) LIKE 'A234';
In the Above query , M_TEST will return a value ,which will be a column name in the TEST_DET table.
For Example ,
TEST_DET TABLE has the following structure
T_ID NUMBER(6)
T_NAM1 VARCHAR2(2000)
T_NAM2 VARCHAR2(2000)
T_NAM3 VARCHAR2(2000)
T_NAM4 VARCHAR2(2000)
T_DAT DATE
T_COL_DATA table will contain any of the above column name
it can be T_NAM1 OR T_NAM2 OR T_NAM3
Say for Example, If it contains T_NAM1 , then my query should be
SELECT * FROM TEST_DET
WHERE (T_NAM1) LIKE 'A234';
If it contains T_NAM2 , then my query should be
SELECT * FROM TEST_DET
WHERE (T_NAM2) LIKE 'A234';
How do i achieve the above . that is dynamically getting the column name from another table to compare against a value
|
|
|
|
Re: using a result of subquery as a column name [message #270749 is a reply to message #270243] |
Thu, 27 September 2007 23:24  |
SnippetyJoe
Messages: 63 Registered: March 2007 Location: Toronto, Canada
|
Member |
|
|
Perhaps something like this (untested) ...
SELECT *
FROM TEST_DET, T_COL_DATA
WHERE
CASE M_TEST
WHEN 'T_NAM1' THEN T_NAM1
WHEN 'T_NAM2' THEN T_NAM2
WHEN 'T_NAM3' THEN T_NAM3
WHEN 'T_NAM4' THEN T_NAM4
ELSE NULL
END LIKE 'A234'
;
I'm assuming T_COL_DATA only has one row (this is implied by your pseudocode).
--
Joe Fuda
SQL Snippets
|
|
|