SQL Query returning Null for Value present

Question

I have a sql table which has columns like ITEM_ID, FROM_QUANTITY, TO_QUANTITY, LIST_PRICE. Now every item should have a FROM_QUANTITY from 1 to 12 and a null in FROM_QUANTITY.

Now in order to check which QUANTITY is missing from ITEM in table , I have written a SQL query which will print None if the FROM_QUANTITY is not there.

The query is working as expected. Just that each item also has a null value in quantity and has some price associated with it. But my query is Showing null as list price for it.

Here is my query

with pl as (
      select DISTINCT ITEM_ID, FROM_QUANTITY,TO_QUANTITY, coalesce(to_char(list_price), 'NONE') as list_price
      from PRICELIST_LINE
      where item_id IN ('XYZ') and
            PRICELIST_HDR_KEY in (select Pricelist_Hdr_Key
                              from PRICELIST_HDR
                              where PRICELIST_NAME IN ('ABC') and
                                     SELLER_ORGANIZATION_CODE IN ('100')
                             ) and SYSDATE < END_DATE_ACTIVE
      )
select i.item_id, u.from_quantity, pl.to_quantity,pl.list_price
from (select distinct item_id from pl) i cross join
     (select '1' as from_quantity from dual union all
      select '2' as from_quantity from dual union all
      select '3' as from_quantity from dual union all
      select '4' as from_quantity from dual union all
      select '5' as from_quantity from dual union all
      select '6' as from_quantity from dual union all
      select '7' as from_quantity from dual union all
      select '8' as from_quantity from dual union all
      select '9' as from_quantity from dual union all
      select '10' as from_quantity from dual union all
      select '11' as from_quantity from dual union all
      select '12' as from_quantity from dual union all
      select '' as from_quantity from dual
     ) u left join
     pl
     on pl.item_id = i.item_id and pl.from_quantity = u.from_quantity;

Now the Output for this is like:

ITEM_ID FROM_QUANTITY TO_QUANTITY LIST_PRICE
------- ------------- ----------- ----------
ABC                 1           2        100
ABC

Expected :

ITEM_ID FROM_QUANTITY TO_QUANTITY LIST_PRICE
------- ------------- ----------- ----------
ABC                 1           2        100
ABC                                      200

You are joining on from_quantity so when that is null you will have null for the rows. If I understand correctly, you might want to convert this into a UNION. It may be a bit verbose though. Or maybe a CASE statement to handle the nulls? — Jacob H
– Jacob H, Commented Jun 27, 2019 at 13:23
No I meant writing out 12 queries for the UNION instead of doing a cross join. That was just a suggestion though. I think your actual problem is still that you are joining on from_quantity. — Jacob H
– Jacob H, Commented Jun 27, 2019 at 13:27
Yes. One thing which might help here is that the null in FROM_QUANTITY always has a price of 999999999. Can we check for that separately if t exist or not — Duck_dragon
– Duck_dragon, Commented Jun 27, 2019 at 13:29
@JacobH What do you think is the best option to check for a null price — Duck_dragon
– Duck_dragon, Commented Jun 27, 2019 at 13:39

Boneist · Accepted Answer · 2019-06-27 14:49:50Z

1

Using a partition outer join in Oracle should help, e.g.:

WITH pl AS (SELECT DISTINCT item_id,
                            from_quantity,
                            to_quantity,
                            coalesce(to_char(list_price), 'NONE') AS list_price
            FROM   pricelist_line
            WHERE  item_id IN ('XYZ')
            AND    pricelist_hdr_key IN (SELECT pricelist_hdr_key
                                         FROM   pricelist_hdr
                                         WHERE  pricelist_name IN ('ABC')
                                         AND    seller_organization_code IN ('100'))
            AND    SYSDATE < end_date_active),
    qtys AS (SELECT LEVEL from_quantity
             FROM   dual
             CONNECT BY LEVEL <= 12
             UNION ALL
             SELECT NULL from_quantity
             FROM   dual)
SELECT pl.item_id,
       q.from_quantity,
       pl.to_quantity,
       pl.list_price
FROM   qtys q
       LEFT OUTER JOIN PL PARTITION BY (pl.item_id) ON pl.from_quantity = q.from_quantity
                                                       OR (pl.from_quantity IS NULL AND q.from_quantity IS NULL);

N.B. untested.

edited Jun 27, 2019 at 14:49

answered Jun 27, 2019 at 14:33

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Duck_dragon Over a year ago

This works exactly as expected. How did you figure out that a partition join would help

Boneist Over a year ago

because you're wanting to have a dummy set of rows to compare against each unique id to see what's missing. In short: experience. The link I posted in my comment above is very helpful; it's my go-to for doing partition outer joins!

Duck_dragon Over a year ago

Just a addon query. What if ITEM XYZ doesn't exist in Table but I still want to print NULL for all quantities. I don't think that is possible and Yes my ITEM_ID column is a string but that is something which is not in my control.

Boneist Over a year ago

Well, no. But how would you know it's ITEM XYZ you want to report against, and not, say, ITEM ABC? Presumably you have some way of knowing what items you want to report against - it's a matter of outer joining the pl subquery to that list-of-items, and then doing the partition outer join.

Duck_dragon Over a year ago

You are correct. It is actually difficult to know which ITEM it is. I think I should handle that operation using python script. That would be easy. Thanks for help

Collectives™ on Stack Overflow

SQL Query returning Null for Value present

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related