is there a way to evaluate Null with a string field via = or a NOT IN list?

Toad for Oracle
1

I am trying to figure out if there is a way to check a string field via a NOT IN list and also allow NULL. I want to allow anything except for a value of ‘A’ or ‘C’ - (Nulls are OK).

For example, the field, pgm_cde might have the following values:
‘A’, ‘C’, ‘L’, ‘Q’, ‘T’ or NULL

If pgm_cde contains a value other than NULL, then the following WHERE works:
[ AND pgm_cde NOT IN (‘A’, ‘C’) ]

If pgm_cde is NULL, then the row is not returned.

I have tried using the NVL() function to change the NULLS to a character but the evaluation (NOT IN) still fails to return anything for these.

I appreciate any suggestions on how to accomplish this.

2

Just try nvl(pgm_cde,‘Z’) not in (‘A’,‘C’,‘L’,‘Q’,‘T’,‘Z’)

3

I would do it like this:

Where x=y

AND ( pgm_cde NOT IN (‘A’, ‘C’)

Or pgm_cde is null )

Phyllis

4

IN is simply a shorthand for a list of = or clauses. And NULL can never be used
with anything but IS NULL or IS NOT NULL. So you’re trying to swim
upstream J

5

Here’s a good blog on TW talking about Nulls and Not IN or Not Exist

http://toadworld.com/BLOGS/tabid/67/EntryID/548/Default.aspx

6

Agreed.

If column is indexed – just remember that while the first clause might use
the index – the second will not (unless it’s a bitmap index) –
so full table scan may very well occur

7

Afternoon Statey603,

I am trying to figure out if there is a way to check a
string field via a NOT IN list and also allow NULL. I want
to allow anything except for a value of 'A' or 'C' - (Nulls
are OK).

There is:

SELECT pgm_cde ...
WHERE pgm_cde is null or (pgm_cde not in ('A','C'))

will do exactly what you want.

If pgm_cde contains a value other than NULL, then the
following WHERE works:
[ AND pgm_cde NOT IN ('A', 'C') ]
That's because NULL is an absence of a value as opposed to a value.
Hence you cannot tell what it's value is because the value is absent and
unknowable!

If pgm_cde is NULL, then the row is not returned.
Yes. This test is saying "are there any rows where pgm_cde have no
value"?

I have tried using the NVL() function to change the NULLS to
a character but the evaluation (NOT IN) still fails to
return anything for these.
This should work, but you wish to select NULLs so you need to change
them to a non-A and non-C value, as follows:

SELECT pgm_cde ...
WHERE nvl(pgm_cde, 'X') NOT IN ('A','C');

The problem with this approach is that in the event that you have an
index on pgm_cde, the NVL() function call will disable that/those
indices. In addition, if the pgm_cde is the only column in the index,
then NULL entries will not appear in the index, so you will most likely
end up with a FTS anyway. (When including NULLs in the output.)

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

8

WHERE pgm_cde is null or (pgm_cde not in (‘A’,‘C’))

isn’t this because the ‘not in’ to the right of the OR is trying to evaluate the NULL as a string/character and ev aluation of NULL using anyhting but IS NULL or IS NOT NULL will fail?

am I missing something?

can NVL help?

When I try the following - if the pgm_cde value is NULL, nothing is returned.
Message was edited by: statey603

Message was edited by: statey603

9

That should work.

Maybe you have a space instead of a null. Or maybe something else in your where
clause is filtering it out.

If it’s a space, this should find it.

WHERE pgm_cde is null or ( pgm_cde not in ( ‘A’ , ‘C’ )) or ( pgm_cde = ’ ’ )

10

It is because NULL is also in an evil dimension.

In the expressions ( a=null, a<>null, a in
(‘x’,’y’) ) you always get a false when a is null.

There are only three ways of testing for null:

11

Hi Statey603

When I try the following - if the pgm_cde value is NULL,
nothing is returned.

WHERE pgm_cde is null or (pgm_cde not in ('A','C'))

That's exactly what I've been running, which works for me. Hmm.

Try this please:

select nvl(pgm_cde, 'NULL'), count(*)
from
group by pgm_cde
order by pgm_cde;

And a DESC of the table would also help. Can you also mention what
versions of Oracle database, Oracle client and Toad you are using
please.

isn't this because the not in to the right of the OR is
trying to evaluate the NULL as a string/character
It is strange, but what is happening in the evaluation is this:

Q1. Is the value NULL?
YES: Select this row.
NO: Q2. Is the value NOT 'A' AND NOT 'C'
YES: Reject this row
NO: Include this row.

Now even if Oracle is short-circuiting the tests (if the first one is
true, don't bother with the second because the whole OR will be true)
only one of the two tests will be true because a NULL implies NO VALUE
and anything but 'A' or 'C' implies a value.

The order you specify the tests in this case, make no difference, you
could check for NULLs last or NOT IN ('A','C') last and still get the
same results.

Looking at an explain plan for the statements on a test table, I see
this:

SELECT STATEMENT CHOOSE
2 COUNT
1 TABLE ACCESS FULL SYSTEM.NORM Filter Predicates:
"NORM"."A" IS NULL OR "NORM"."A"<>'A' AND "NORM"."A"<>'C'

I'm a little confused as to why you are not getting any results though.
Do you have other clauses in the WHERE that are filtering out before you
get to the NULL or NOT IN check.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

12

The pgm_cde field is in the ‘CasePgms’ table that is joined to the ‘Cases’ table via outer join because there might not be a Case Program.

WHERE
Cases.CaseID = CasePgms.CaseID(+)
AND…

The result is that any Cases with no CasePgm data have NULL CasePgm data fields (like pgm_cde)

13

At this point, my results reflect what ERWIN stated

It is because NULL is also in an evil dimension.

In the expressions ( a=null, anull, a in
(‘x’,’y’) ) you always get a false when a is null.

I am wondering if anyone knows a way around this.

I tried changing NULLs to something else via NVL(pgm_cde, ‘X’) in my SELECT but this does not seem to help during the WHERE clause evaluation of the pgm_cde values.

IO appreciate all of the help and comments. by the way.

14

when dinosaurs roamed the earth i took a course in boolean logic whose
evaluations for predicate statements may provide an understanding for this
question:
TRUE OR TRUE = TRUE
TRUE OR FALSE = TRUE
FALSE OR TRUE = TRUE
FALSE OR FALSE = FALSE

Conclusion:
When 2 or more expressions are evaluated with an intervening OR Operator The
more likely to succeed condition should always be first
When 2 or more expressions are evlauated with an intervening OR Operator The
Least likely to succeed condition should be dead last

TRUE AND TRUE = TRUE
TRUE AND FALSE=FALSE
FALSE AND TRUE=FALSE
FALSE AND FALSE=FALSE

Conclusion:
When 2 or more expressions are evaluated with an intervening AND Operator. The
more likely to fail condition should always be FIRST
When 2 or more expressions are evaluated with an intervening AND Operator. The
least likely to fail condition should always be LAST


hth
Martin Gainty

…‘Standard’ Caveats apply…

15

I will need to check with our dba to see what info I can provide.
sorry

I really appreciate the assistance.

16

You have to do it in the WHERE clause.

For instance

SELECT a, b, c, d

FROM tab1

WHERE NVL(b,’X’) in (’a’,’b’);

Or of course you could do this

SELECT *

FROM ( SELECT a, b, nvl(b,’X’) bb, c, d

FROM tab1 )

WHERE BB in (’a’,’b’);

But that will be inefficient

17

Or, if you are trying to make the condition evaluate as true if there are null
values (which I think is what you originally stated), then you would do

Where nvl(b, ’a’) in (‘a’, ‘b’);

I noticed that you remove the parenthesis around the original statement I
suggested to you. If you have anything else in your where clause, you have to
have parenthesis around that block or it will not work as you expect. . .

18

I got it working !!!

The weird thing is I am not completely sure what I was doing wrong.
I had a collegue try it with
[ WHERE pgm_cde is null or (pgm_cde not in (‘A’,‘C’)) ]
on another PC and it worked.
I thought I had the same SQL on my PC but cannot be quite sure since I ended up restarting my Toad session after which point it worked on my PC.

I am going to chalk this one up to Long Weekend Mindset. My bad…I just read this and will try to be better prepared come 4th of July. How to Recover from Back to Work Blues Accept the importance of being present at work again. Turning up with a holiday mindset can cause problems at work, from lack of concentration on the tasks at hand to forgetting appointments and the names of new clients. None of this is destined to look good in the eyes of fellow colleagues and bosses. Accept that you’ve had a great break and that you’re marvellously refreshed; now it’s time to get on with what you’re good at in a work environment.

SELECT
B.AUTH_NBR, B.SRVC_TYP_CDE, B.BGN_DT, B.END_DT,
C.CASE_ID, C.PLAN_OPEN_DT, C.PLAN_CLOSED_DT,
D.CASE_ID AS PGM_CASE_ID, D.PGM_CDE
FROM
CLIENT_SERVICE_TBL B,
CLIENT_CASE_TBL C,
CLIENT_CASE_PROGRAM_TBL D
WHERE
B.CL_ID = 7076
AND (B.CL_ID = C.CL_INDIVID_ID(+))
AND (B.BGN_DT BETWEEN C.PLAN_OPEN_DT(+) AND C.PLAN_CLOSED_DT(+))
AND C.CASE_ID = D.CASE_ID(+)
AND B.SRVC_TYP_CDE IN (120,122,140,141)
AND (D.PGM_CDE IS NULL OR D.PGM_CDE not in (‘A’, ‘C’))

I am not sure which of you to give credit to so I will say that you all do for the suggestions and comments!

Thanks everone !!