MVSFORUMS.com

[Sarangadhar]

I have a situation as below:
table1 - col1, col2, col3, col4-col10
table2 - col1, col2, col3, col4-col10
Table1 will have recent data, and table 2 will have historical data.
I need to get all unique combinations of col1 and col2 from both the tables together, and corresponding col4-col10 values. TABLE1 will have some col1 col2 combinations which are not present in table2 and vice versa.

If a combination present in both the tables, then I need the information (col4-col10) from tab1, as this is recent info. Otherwise, I need the information present that table where that combination exist.

One fact about data is -> col4-col10 values are same for the same combination of col1 and col2 even though there are more than one rows present for each col1 and col2 combination.

There will be millions of rows in the table, but the unique number of col1 and col2 combinations will be less than 2000.

I can try a CASE statement in select clause to see whether the combination is present in table1 from FULL OUTER JOIN between tables. Pick the information from table one if table1 row is present, other wise pick from table 2. But since there are nearly 10 columns, I need 10 case statements which look ugly. Is there any better way?

Please let me know if you need any more info.

Eg.:

[Sarangadhar]

above one got solved.

but I wanted to know which one is efficient of below when used in a SELECT caluse:

[vivek1983]

Sarangadhar,

I am using COALESCE function which is an optimized one for such a scenario.

SELECT COALESCE(COL1,COL2) as member

For information check the below link:

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.sqlref/fifnull.htm
_________________
Vivek G
--------------------------------------
A dream is just a dream. A goal is a dream with a plan and a deadline. (Harvey Mackay)

[vkphani]

You can use VALUE function too as mentioned below:
SELECT VALUE(COL1,COL2) as member

[Sarangadhar]

Thanks all..

I am using COALESCE function
_________________
Thanks