Urgent Help Required

Hi:

I have two tables, columns of second table are the values in first table. See Eg. Table1 contains all the column names of table2.

select * from table1;

deptno		name 		colname(values of this column will be columns for
table2)		datatype
--------------------------------------------------------------------------------------------------
10		empno 		col1							number
10		ename 		col2							varchar2(20)
10		sal 		col3							number
20		empno		col3							number
20		ename		col2							varchar2(20)
20		sal		col1							number

select  * from table2;
col1		col2		col3		deptno
--------------------------------------------------------------
7900		Scott		3000		10
7890		Lok		5000		10
6000		Ashu		6789		20

Go through the code below:

DECLARE
vCOLNAME VARCHAR2(30);
vVALUE VARCHAR2(200);/*variable contains column name of table2*/ vVALUE1 VARCHAR2(200);
BEGIN
SELECT colname INTO vCOLNAME FROM table1 WHERE deptno=10 AND name=empno;
DBMS_OUTPUT.PUT_LINE(vCOLNAME);
SELECT vCOLNAME INTO vVALUE FROM table2 NAME WHERE deptno=10 AND name=empno;
DBMS_OUTPUT.PUT_LINE(vVALUE);
SELECT col1 INTO vVALUE1 FROM GASPER_HOST_TRANSACTION NAMEWHERE deptno=10 AND name=empno;
DBMS_OUTPUT.PUT_LINE(vVALUE1);
END;
/

Now the situation is depending on the department i m storing the column values. So now if i have to select empno for dept10 I will retrive the column name (say empno) and then the value(stored in col1 for dept10).

Problem comes when in Pl/sql i m selecting the column name in a variable and then go to select from the other table it is considering the variable as a variable and not the column name of table2.See Code.

Is there any way around for the problem. I want to select the value for the column in table2 based on the column names(value in table1) i get from table1.

Thanks in advance

Ashutosh Received on Thu Jul 31 2003 - 08:30:33 CDT