Column value is refered as Column_Name in another table

Database
Enthusiast

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.! :)

7 REPLIES
Junior Contributor

Re: Column value is refered as Column_Name in another table

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
Enthusiast

Re: Column value is refered as Column_Name in another table

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.

Junior Contributor

Re: Column value is refered as Column_Name in another table

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'
Enthusiast

Re: Column value is refered as Column_Name in another table

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?

Enthusiast

Re: Column value is refered as Column_Name in another table

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

Enthusiast

Re: Column value is refered as Column_Name in another table

Hi All,

I am getting spool space issue. Is there any other way to optimize the SQL for it?

Thank you in advance.!

Junior Contributor

Re: Column value is refered as Column_Name in another table

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?