Reputation: 13
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.
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
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