I am working on below requirement :
Refer below example for it
ID | XYZ
1 | C1
2 | C2
ID | C1 | C2
1 | $$ | ##
2 | @@ |**
TGT_ID | Text
1 | $$
2 | **
Please help me, how this can be done.
Thanks in advance.! :)
Strange data model.
This is a simple CASE:
WHEN A.XYZ = 'C1' THEN B.C1
WHEN A.XYZ = 'C2' THEN B.C2
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.
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
WHERE TableName = 'MyTable'
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?
Case statement would be better, if you are confident about the number of columns. Far better than recursive.
I am getting spool space issue. Is there any other way to optimize the SQL for it?
Thank you in advance.!
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?