Home » SQL & PL/SQL » SQL & PL/SQL » How do I use SQL column values as column names (11.2.0.4.0)
How do I use SQL column values as column names [message #633817] Wed, 25 February 2015 06:55 Go to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Hi,

I am using oracle 11.2.0.4.0.

I have two tables and one table column values are column names for another table. Column values are dynamic.


CREATE TABLE PLOBJ
   (	"POID" NUMBER NOT NULL , 
	"PONAME" VARCHAR2(20 BYTE), 
	"WPID" NUMBER, 
	"PROJID" NUMBER, 
	"WFID" NUMBER, 
	"BASELINESTDT" DATE, 
	"BASELINEENDDT" DATE, 
	"FORECASTSTDT" DATE, 
	"FORECASTENDDT" DATE, 
	"ACTUALSTDT" DATE, 
	"ACTUALENDDT" DATE, 
	"PLANNEDSTDT" DATE, 
	"PLANNEDENDDT" VARCHAR2(20 BYTE), 
	"POTEMID" NUMBER);

INSERT INTO PLOBJ VALUES(1,'PO1',1,1,1,'25-FEB-15',NULL,NULL,NULL,'25-FEB-15',NULL,NULL,NULL,1);

 CREATE TABLE "RULE_POT_GDF" 
   (	"PROJECTID" NUMBER(5,0), 
	"POTEMID" NUMBER(5,0), 
	"LGDF_COL" VARCHAR2(20 BYTE), 
	"OPERATORID" VARCHAR2(5 BYTE), 
	"RGDF_COL" VARCHAR2(20 BYTE), 
	"EMAILADDRESS" VARCHAR2(30 BYTE), 
	"RVALUE" NUMBER(5,0)
   );


INSERT INTO RULE_POT_GDF VALUES(1,1,'BASELINESTDT','=','ACTUALSTDT',tt@gmail.com, null);	

POTEMID column is relationship between two tables.

In RULE_POT_GDF table LGDF_COL and  RGDF_COL values may get BASELINESTDT, ACTUALSTDT, BASELINEENDDT etc which are column names of  PLOBJ table.

I would like to retrieve the column data from plobj table based on the colum value from RULE_POT_GDF table; 



Please advice.
Re: How do I use SQL column values as column names [message #633822 is a reply to message #633817] Wed, 25 February 2015 07:21 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Why would you store column names in a table? Oracle lready maintains the metadata. Could you explain the reason for implementing such design. There might be a better approach.

If you want to stick to this, then you need to use dynamic SQL in PL/SQL. SQL expects static object names while parsing, you cannot have them dynamically.

[Updated on: Wed, 25 February 2015 07:24]

Report message to a moderator

Re: How do I use SQL column values as column names [message #633852 is a reply to message #633822] Thu, 26 February 2015 04:50 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
That is the requirement here.

Any way I have written through Dynamic Sql.

Thank you very much.
Re: How do I use SQL column values as column names [message #633857 is a reply to message #633852] Thu, 26 February 2015 05:51 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
grpatwari wrote on Thu, 26 February 2015 16:20

Any way I have written through Dynamic Sql.


If you could share it with us, we would learn from it.
Previous Topic: Order by with WITH clause
Next Topic: Nested table as in parameter
Goto Forum:
  


Current Time: Wed Apr 24 10:31:28 CDT 2024