ajax
ajax

Reputation: 13

Returning NULL and NOT NULL values in query

I am trying to show NULL values and NOT NULL values for c.GUIDELINE_TEXT. I've seen some examples but I'm not sure where to put it. When I use this code :

WHERE c.GUIDELINE_TEXT is NULL 

I don't get any values returned. I haven't tried COALESCE as I'm not quite sure what that is or where to put it in my code. I do know that GUIDELINE_TEXT does have null values. I know I'm probably making some newbie mistake so if someone can point me in the right direction that would be nice. Thank you.

SELECT
    a.ORDER_NO,
    a.RELEASE_NO,
    a.SEQUENCE_NO,
    b.C_OPERATION_SEQUENCE as OP_SEQ,
    b.OPERATION_NO,
    b.OP_ID,
    c.GUIDELINE_TEXT
FROM
    ifsapp.SHOP_ORD a,
    ifsapp.SHOP_ORDER_OPERATION b,
    ifsapp.SHOP_ORD_WORK_GUIDE c
WHERE
    a.ORDER_NO = b.ORDER_NO 
    AND b.ORDER_NO = c.ORDER_NO
    AND a.RELEASE_NO = b.RELEASE_NO
    AND b.RELEASE_NO = c.RELEASE_NO
    AND a.SEQUENCE_NO=b.SEQUENCE_NO
    AND b.SEQUENCE_NO = c.SEQUENCE_NO
    AND b.OPERATION_NO = c.OPERATION_NO
    AND a.ORDER_NO = ('&Ord')
    AND a.RELEASE_NO = ('&Rel')
    AND a.SEQUENCE_NO = ('&Seq')

EDIT: An example of data.

Example

An expected result would be that it pulls data also from blank columns of GUIDELINE_TEXT with the hopeful result that it will pull all order_no instead of just those that have text in GUIDELINE_TEXT column.

Upvotes: 0

Views: 190

Answers (1)

kevinskio
kevinskio

Reputation: 4551

Without seeing sample data this should show the presence of null values

SELECT
a.ORDER_NO,
a.RELEASE_NO,
a.SEQUENCE_NO,
b.C_OPERATION_SEQUENCE as OP_SEQ,
b.OPERATION_NO,
b.OP_ID,
NVL(c.GUIDELINE_TEXT,'Nothing to see, move along') AS GUIDELINE_TEXT

From the documentation "NVL lets you replace null (returned as a blank) with a string".

COALESCE returns the first non null value from a list which is described here. Coalesce can be tricky to use if you have multiple values which might or might not be null. If your set of possible values can be arranged in order of likelihood of a non null value or you know for sure that all the values in the expression will be the same then it is very helpful.

Upvotes: 1

Related Questions