CASE statement when using LEFT JOIN

Question

I need some help in fixing a data aberration. I create a view based on two tables with Left Join and the result has some duplicates (as given in the logic section)

Data Setup:

*******************
       TEST1
*******************
PRODUCT VALUE1  KEY
1       2       12
1       3       13
1       4       14
1       5       15

*******************
       TEST2
*******************
KEY ATTRIBUTE
12  DESC
13  (null)
14  DESC
15  (null)

What I tried so far

SELECT 
    B.KEY,
    B.ATTRIBUTE,
    A.PRODUCT
    A.VALUE1
FROM TEST2 B LEFT JOIN TEST1 A ON TEST2.KEY = TEST1.KEY;

What I get with above SQL is

KEY ATTRIBUTE   PRODUCT VALUE1
12  DESC        1       2
13  (null)      1       3
14  DESC        1       4
15  (null)      1       5

What I need to get

KEY ATTRIBUTE   PRODUCT VALUE1
12  DESC        1       2
13  DESC        1       3
14  DESC        1       4
15  DESC        1       5

Logic: Since all products with id 1 are same, I need to retain the attributes if it is NULL. So doing a distinct of PRODUCT and ATTRIBUTE will always have 1 row per product id. Test1 has more than 100 products and Test2 has corresponding descriptions.

Note: This is not a normalized design since it is data warehousing. So no complaints on design please

I would like to have a CASE statement in the attribute field.

CASE
    WHEN ATTRIBUTE IS NULL THEN {fix goes here}
    ELSE ATTRIBUTE 
END AS ATTRIBUTE

Some one needs to see fiddle, then go here

Why does the attribute column of test2 contain nulls? What would be the impact of simply updating them to be the appropriate value? — Bohemian, Jun 04 '14 at 13:26
Nulls are loaded from files (2 rows). Descriptions are already present in DB (2 rows). As a temporary fix, I am doing updates as you said, but I need a permanent fix in this view creation. — Srini V, Jun 04 '14 at 13:27

score 4 · Accepted Answer · edited Oct 20 '14 at 11:59

4

It's not clear but if you say that for each product can be only one attribute then try to use MAX() OVER

SELECT 
TEST1.Product,
TEST1.value1,
TEST2.KEY,
MAX(ATTRIBUTE) OVER (PARTITION BY test1.Product) ATTR
FROM TEST2 
  LEFT JOIN 
       TEST1 ON TEST2.KEY = TEST1.KEY

SQLFiddle demo

edited Oct 20 '14 at 11:59

Srini V

11,045
14
66
89

answered Jun 04 '14 at 13:26

valex

23,966
7
43
60

+1 for reminding me about MAX, I will check this out. LOL over me since I suggest this to many when it comes to numbers and I forgot trying it for char/varchar – Srini V Jun 04 '14 at 13:29
You nailed it. :) Thanks a lot – Srini V Jun 04 '14 at 13:47

score 2 · Answer 2 · answered Jun 04 '14 at 13:29

SQL Fiddle:

SELECT B.KEY,
  CASE WHEN B.ATTRIBUTE IS NULL THEN 
  (
    SELECT s2.ATTRIBUTE
    FROM test2 s2
    LEFT JOIN TEST1 s1 ON s1.KEY = s2.KEY
    WHERE s1.PRODUCT = A.PRODUCT
    AND s2.ATTRIBUTE IS NOT NULL
    AND ROWNUM = 1
  ) ELSE B.ATTRIBUTE END AS ATTRIBUTE, 
  A.PRODUCT, A.VALUE1
FROM TEST2 B 
LEFT JOIN TEST1 A ON A.KEY = B.KEY;

score 0 · Answer 3 · edited Jun 04 '14 at 14:15

0

SELECT 
NVL(attribute,'DESC')
FROM TEST2 LEFT JOIN TEST1 ON TEST2.KEY = TEST1.KEY;

Just seen its Oracle please try above

edited Jun 04 '14 at 14:15

Joel

4,732
9
39
54

answered Jun 04 '14 at 13:37

user2270653

207
3
16

Thanks for your response. But this is a bad suggestion you can make. Also in future if you want to do a NULL Check and replace then go for NVL or COALESCE – Srini V Jun 04 '14 at 13:41

CASE statement when using LEFT JOIN

3 Answers3