Re: Urgent Help Required

Ashutosh wrote:

> 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

Simple problems often have simple solutions.

Step 1: Read Cobb's rules of normalization Step 2: Redesign your database to be relational

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)