Home » SQL & PL/SQL » SQL & PL/SQL » using a result of subquery as a column name
using a result of subquery as a column name [message #270243] Wed, 26 September 2007 04:50 Go to next message
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 #270244 is a reply to message #270243] Wed, 26 September 2007 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Of course this is a very bad design that can only lead to very poor performances.

You can only do this using PL/SQL and dynamic SQL.

Regards
Michel
Re: using a result of subquery as a column name [message #270749 is a reply to message #270243] Thu, 27 September 2007 23:24 Go to previous message
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
Previous Topic: Quary Required ., ( Column to row )
Next Topic: Oracle 10G Compliance
Goto Forum:
  


Current Time: Thu Feb 06 22:05:52 CST 2025