Need some urgent help

[haria_kishore]

hi

I have 3 tables - table1,table2,table3

table1 has 3 columns named col1,col2 and col3 and has 1 dummy record with values 0,1,2 respectively. The other records are my master records

col1 col2 col3
0 1 2
A B C
AA BB C
D E F

table 2 has the mapping of the column as shown below

ColNo Name Descr Conditon
0 ABC BC Yes
1 DEF DE No
2 GHI GH Yes

Now i have to populate values in table 3 from table 2 and table 1

The logic is as follows

Suppose first i read col1 from table1. I look and take the value 0 (dummy value) and i check the in table 2 if for 0 the condition is Yes or No. If its No I ignore the col1 and move to col2. If not i start populating values of col1 first and then move to next col2.

In the above table shown above, since condition for 0 is Yes i read values for col1 (which will be A,AA,D) and from table 2 i read value for 0 (ABC,BC) and populate it in table3. Once all data is populated for col1, i move to col2. Since col2 condition is No in Table2, i ignore col2 and then i finally read col3 (the condition is Yes for col3 in Table2) and my table3 should look as follows

ColNo Typ Name Descr
0 A ABC BC
0 AA ABC BC
0 D ABC BC
2 C GHI GH
2 F GHI GH

This can be accomplished either using function or pl/sql procedure

[anubina]

hi

I have 3 tables - table1,table2,table3

table1 has 3 columns named col1,col2 and col3 and has 1 dummy record with values 0,1,2 respectively. The other records are my master records

col1 col2 col3
0 1 2
A B C
AA BB C
D E F

table 2 has the mapping of the column as shown below

ColNo Name Descr Conditon
0 ABC BC Yes
1 DEF DE No
2 GHI GH Yes

Now i have to populate values in table 3 from table 2 and table 1

The logic is as follows

Suppose first i read col1 from table1. I look and take the value 0 (dummy value) and i check the in table 2 if for 0 the condition is Yes or No. If its No I ignore the col1 and move to col2. If not i start populating values of col1 first and then move to next col2.

In the above table shown above, since condition for 0 is Yes i read values for col1 (which will be A,AA,D) and from table 2 i read value for 0 (ABC,BC) and populate it in table3. Once all data is populated for col1, i move to col2. Since col2 condition is No in Table2, i ignore col2 and then i finally read col3 (the condition is Yes for col3 in Table2) and my table3 should look as follows

ColNo Typ Name Descr
0 A ABC BC
0 AA ABC BC
0 D ABC BC
2 C GHI GH
2 F GHI GH

This can be accomplished either using function or pl/sql procedure

SQL Solution:

WITH
 proyeccion AS (
SELECT *
  FROM table2 tb2
 WHERE condition = 'YES'
)
,seleccion AS (
SELECT *
  FROM table1 tb1
 WHERE ROWNUM <= 1
)
,cartesiano AS (
SELECT ROWNUM AS registro,
       tb1.*
  FROM table1 tb1
)
SELECT DISTINCT pry.colno,
                (CASE pry.colno
                 WHEN sel.col1 THEN car.col1
                 WHEN sel.col2 THEN car.col2
                 WHEN sel.col3 THEN car.col3
                               ELSE NULL END) AS typ,
                pry.name,
                pry.description
  FROM cartesiano car,
       proyeccion pry,
       seleccion  sel
 WHERE car.registro > 1
 ORDER BY pry.colno,typ

REMEMBER: The first row of table1 always has the header of relation of the cartesian projection. Recommended an aditional field for identify this row, because the order by is not guarantee the correct selection in the factorial query.

Sorry, but my level english is very poor