CODEWITHSUNDEEP
sqloracle-databasejoinkognitiokognitio-wx2
Srini V
Srini V

Reputation: 11375

CASE statement when using LEFT JOIN

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

Upvotes: 1

Views: 1875

Answers (3)

valex
valex

Reputation: 24144

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

Upvotes: 4

user2270653
user2270653

Reputation: 247

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

Just seen its Oracle please try above

Upvotes: 0

Linger
Linger

Reputation: 15058

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;

Upvotes: 2

Related Questions