CODEWITHSUNDEEP
sqloracle-database
dc4teg
dc4teg

Reputation: 109

Return the first non-NULL value in the list, or NULL if there are no non-NULL values

I want to be able to do this, return the first non-NULL value in the list, or NULL if there are no non-NULL values. I am using Oracle SQL against oracle database.

SELECT
    a.customer,
    a.item,
    b.item,
FROM
    Orders a
        LEFT JOIN
        item_desc c
        ON
    a.item= c.tag1 OR
    a.item= c.tag2 OR
    a.item= c.tag3 OR
    a.item= c.tag4 OR
    a.item= c.tag5
        LEFT JOIN
        Orders b
        ON
        c.item = b.item AND
    a.customer = b.customer
WHERE
    a.itemLIKE 'CP%'
GROUP BY
    a.customer,
    a.item,
    b.item;

The purpose of my query is to make sure that the customer has the main item and not just the tag along item purchased but there could be a tag along item that belongs to multiple items youll see below how Jon and Mike have a match for item X and Null value because you see item 422 is a tag along for item X and B. The results look like this:

Customer    Item    Item
Jon         422     X
Jon         424     NULL
Mike        424     X
Mike        422     Null
Jay         422     Null
Dan         422     B
Dan         422     Null

The purpose of my query is to make sure that the customer has the required tag along to an item purchased but there could be a tag along item that belongs to multiple items.

I want the results to be like this: Since Jon and Mike had a match the null value disappears from the result set but since Jay didn't have a match the Null value stayed.

Customer    Item    Item
Jon         422     X
Mike        424     X
Jay         422     Null
Dan         422     B

Upvotes: 0

Views: 1508

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I suspect that you can get what you want using aggregation:

SELECT a.customer, a.item, max(b.item)
FROM Orders a LEFT JOIN
     item_desc c
     ON a.item in (c.tag1, c.tag2, c.tag3, c.tag4, c.tag5) LEFT JOIN
     Orders b
     ON c.item = b.item AND
        a.customer = b.customer
WHEREa.item LIKE 'CP%'
GROUP BY a.customer, a.item;

This returns the non-NULL value, if there is one.

EDIT:

If you want the above query to eliminate null values for a customer, then you can tweak it as:

SELECT customer, item, bitem
FROM (SELECT a.customer, a.item, max(b.item) as bitem,
             row_number() over (partition by a.customer order by (case when a.item is not null then 1 else 2 end) ) as seqnum
      FROM Orders a LEFT JOIN
           item_desc c
           ON a.item in (c.tag1, c.tag2, c.tag3, c.tag4, c.tag5) LEFT JOIN
           Orders b
           ON c.item = b.item AND
              a.customer = b.customer
      WHERE a.item LIKE 'CP%'
      GROUP BY a.customer, a.item
     ) t
WHERE bitem is not null or seqnum = 1;

Upvotes: 1

Related Questions