All Forums Database
Priti_Kanal 8 posts Joined 05/16
18 May 2016
Column value is refered as Column_Name in another table

Hi,
I am working on below requirement :

  • In source table there is field with XYZ.
  • There is another source B table which is joined with A table on ID column.
  • Now value present in XYZ column is column name in B table which is C1.
  • When XYZ column from A table has C1 value then I want to pick the value of C1 field in B table.

Refer below example for it
A table
ID | XYZ
1 | C1
2 | C2
 
B Table
ID | C1 | C2
1 | $$ | ##
2 | @@ |**
 
Target table
TGT_ID | Text
1 | $$
2 | **
 
Please help me, how this can be done.
Thanks in advance.! :)
 

dnoeth 4628 posts Joined 11/04
18 May 2016

Strange data model.
This is a simple CASE:

SELECT
   CASE
      WHEN A.XYZ = 'C1' THEN B.C1
      WHEN A.XYZ = 'C2' THEN B.C2
   END

 

Dieter

Priti_Kanal 8 posts Joined 05/16
18 May 2016

Hi dnoeth,
There are C1 to C99 columns.
I want to avoid writing CASE statement for 99 times.
Can we perform it dynamic way? or using WITH RECURSIVE.
Thanks for your reply.

dnoeth 4628 posts Joined 11/04
18 May 2016

There's no reason to avoid it, you'll get best performance with a huge CASE, this is a one-time effort, you can create most of it using a Select on dbc.ColumnsV like this:

SELECT 'WHEN A.XYZ = ''' || columnname || ''' THEN B.'|| columnname
FROM dbc.ColumnsV 
WHERE TableName = 'MyTable'

 

Dieter

Priti_Kanal 8 posts Joined 05/16
18 May 2016

Thanks for your reply..!!
I thought we can do it dynamic way to fetch required field name.
But as you said, there will not be any performance issues with huge CASE statements, I can go with this option.
Just one doubt,
I have to use such huge CASE statements 3 times (to fetch data from C,D tables as well) in single SQL.
And this SQL will be used in Informatica SQ transformation,so will it give me same performance?
 

yuvaevergreen 93 posts Joined 07/09
19 May 2016

Case statement would be better, if you are confident about the number of columns. Far better than recursive.

Priti_Kanal 8 posts Joined 05/16
20 May 2016

Hi All,
I am getting spool space issue. Is there any other way to optimize the SQL for it?
Thank you in advance.!

dnoeth 4628 posts Joined 11/04
20 May 2016

When you run out of spool it's probably not because of the huge CASE.
I assume your actual query is more complicated, did you check Explain?

Dieter

You must sign in to leave a comment.