RE: comparing null values
the NULL can be looked as field never ever used.
a field that is looked as never ever used cannot match something or it's complement.
this filed only matches NULL.
btw: investigate into CODD's laws if you can spend some time.
apologies for any typos overlooked
kr
m.r.reger
[EMAIL PROTECTED] 01/30/03 22:15 PM
While I agree that it could return the rows, I also understand the system
side.
Null is nothing so it can not be compared to anything else, even another
null. It is something like:
I don't know whether it is like '%STU%' or not because it is nothing - I
don't know what nothing is
Witold
-Original Message-
Jackson
Sent: 30 January 2003 3:27 PM
To: Multiple recipients of list ORACLE-L
Why is this?? If I know that value X has no value, I certainly know that
value X is not like '%STU%' ?
From: Whittle Jerome Contr NCI [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
CC: [EMAIL PROTECTED]
Subject: RE: comparing null values
Date: Thu, 30 Jan 2003 13:15:33 -0600
Gary,
Null has no value. It can't be like or not like anything. If you want to
see the null you'll need to add OR IS NULL to your Where clause.
Jerry Whittle
ASIFICS DBA
NCI Information Systems Inc.
[EMAIL PROTECTED]
618-622-4145
-Original Message-
From: Gary Jackson [SMTP:[EMAIL PROTECTED]]
Can anyone explain why it is that I seem unable to use 'like' and 'not
like'
on columns containing null values. (I am unable to find information
regarding this on MetaLink.)
For example:
SQL select * from tester2;
COL1 COL2 WHATEVER
11STUFF
22STUFF
33
44
SQL select * from tester2 where whatever not like '%STU%';
no rows selected
My question is why does this not return the 3 4 columns?
_
Protect your PC - get McAfee.com VirusScan Online
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Gary Jackson
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Witold Iwaniec
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Markus Reger
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re:RE: comparing null values
Sony and Gary, Null is a special condition of data. A data point cannot equal or not equal null, therefore 'like' and 'not like' also does not work as neither is true. Therefore SQL recognizes 'null' and 'not null' for the purpose of evaluating nulls. Consider the null state as being the complete non existence of the data point only. That being said, then a null neither exists nor does not exist. It's just NULL. An instructor put the logic of Nulls this way: In logic things are either TRUE, FALSE, or NULL. Dick Goulet Reply Separator Author: Sony kristanto [EMAIL PROTECTED] Date: 1/30/2003 6:22 PM Hi Gary, I wonder why you didn't use : select * from tester2 where whatever is null; Null values is extremely empty. Rgrds, Sony -Original Message- From: Gary Jackson [SMTP:[EMAIL PROTECTED]] Sent: Friday, January 31, 2003 12:42 AM To: Multiple recipients of list ORACLE-L Subject: comparing null values Can anyone explain why it is that I seem unable to use 'like' and 'not like' on columns containing null values. (I am unable to find information regarding this on MetaLink.) For example: SQL select * from tester2; COL1 COL2 WHATEVER 11STUFF 22STUFF 33 44 SQL select * from tester2 where whatever not like '%STU%'; no rows selected My question is why does this not return the 3 4 columns? _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gary Jackson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sony kristanto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
comparing null values
Can anyone explain why it is that I seem unable to use 'like' and 'not like' on columns containing null values. (I am unable to find information regarding this on MetaLink.) For example: SQL select * from tester2; COL1 COL2 WHATEVER 11STUFF 22STUFF 33 44 SQL select * from tester2 where whatever not like '%STU%'; no rows selected My question is why does this not return the 3 4 columns? _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gary Jackson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: comparing null values
It shouldn't, because comparison to NULL returns NULL, not TRUE or FALLS If you want, you need: select * from tester2 where whatever not like '%STU%' or whatever IS NULL; Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 30, 2003 12:41 PM Can anyone explain why it is that I seem unable to use 'like' and 'not like' on columns containing null values. (I am unable to find information regarding this on MetaLink.) For example: SQL select * from tester2; COL1 COL2 WHATEVER 11STUFF 22STUFF 33 44 SQL select * from tester2 where whatever not like '%STU%'; no rows selected My question is why does this not return the 3 4 columns? _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gary Jackson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: comparing null values
Title: RE: comparing null values Gary, Null has no value. It can't be like or not like anything. If you want to see the null you'll need to add OR IS NULL to your Where clause. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Gary Jackson [SMTP:[EMAIL PROTECTED]] Can anyone explain why it is that I seem unable to use 'like' and 'not like' on columns containing null values. (I am unable to find information regarding this on MetaLink.) For example: SQL select * from tester2; COL1 COL2 WHATEVER 1 1 STUFF 2 2 STUFF 3 3 4 4 SQL select * from tester2 where whatever not like '%STU%'; no rows selected My question is why does this not return the 3 4 columns?
RE: comparing null values
Title: RE: comparing null values Gary I think this goes back to the classic definition of NULL. NULL means unknown value. Therefore you can't say anything definite about the value. So it is not equal to anything or like anything, period. It is IS NULL from a SQL perspective. HTH -Original Message- From: Gary Jackson [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 30, 2003 12:42 PM To: Multiple recipients of list ORACLE-L Subject: comparing null values Can anyone explain why it is that I seem unable to use 'like' and 'not like' on columns containing null values. (I am unable to find information regarding this on MetaLink.) For example: SQL select * from tester2; COL1 COL2 WHATEVER 1 1 STUFF 2 2 STUFF 3 3 4 4 SQL select * from tester2 where whatever not like '%STU%'; no rows selected My question is why does this not return the 3 4 columns? _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gary Jackson INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: comparing null values
Fred, because a null value is not not like anything in the world. by definition, a null is not comparable to anything. it is null. you cannot check to see if something is comparable to null. you can, however, change your query to say: SQL select * from tester2 where nvl(whatever,'null') not like '%STU%'; and it will work just fine. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, January 30, 2003 12:42 PM To: Multiple recipients of list ORACLE-L Can anyone explain why it is that I seem unable to use 'like' and 'not like' on columns containing null values. (I am unable to find information regarding this on MetaLink.) For example: SQL select * from tester2; COL1 COL2 WHATEVER 11STUFF 22STUFF 33 44 SQL select * from tester2 where whatever not like '%STU%'; no rows selected My question is why does this not return the 3 4 columns? _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gary Jackson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: comparing null values
I believe that's expected behavior, arising from the 'null means we don't know *what* the value is--it could be anything' nature of nulls. For all the db knows, those nulls represent unknown values that *are* indeed like '%STU%'. You should get the same result from select * from tester2 where whatever != 'STUFF'. HTH, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, January 30, 2003 9:42 AM To: Multiple recipients of list ORACLE-L Can anyone explain why it is that I seem unable to use 'like' and 'not like' on columns containing null values. (I am unable to find information regarding this on MetaLink.) For example: SQL select * from tester2; COL1 COL2 WHATEVER 11STUFF 22STUFF 33 44 SQL select * from tester2 where whatever not like '%STU%'; no rows selected My question is why does this not return the 3 4 columns? _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gary Jackson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: comparing null values
Why is this?? If I know that value X has no value, I certainly know that value X is not like '%STU%' ? From: Whittle Jerome Contr NCI [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: RE: comparing null values Date: Thu, 30 Jan 2003 13:15:33 -0600 Gary, Null has no value. It can't be like or not like anything. If you want to see the null you'll need to add OR IS NULL to your Where clause. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Gary Jackson [SMTP:[EMAIL PROTECTED]] Can anyone explain why it is that I seem unable to use 'like' and 'not like' on columns containing null values. (I am unable to find information regarding this on MetaLink.) For example: SQL select * from tester2; COL1 COL2 WHATEVER 11STUFF 22STUFF 33 44 SQL select * from tester2 where whatever not like '%STU%'; no rows selected My question is why does this not return the 3 4 columns? _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gary Jackson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: comparing null values
Use:where whatever not like '%STU% or whatever is null or use: where nvl(whatever ,'[NULL]') not like '%STU% Regards, Waleed -Original Message- Sent: Thursday, January 30, 2003 12:42 PM To: Multiple recipients of list ORACLE-L Can anyone explain why it is that I seem unable to use 'like' and 'not like' on columns containing null values. (I am unable to find information regarding this on MetaLink.) For example: SQL select * from tester2; COL1 COL2 WHATEVER 11STUFF 22STUFF 33 44 SQL select * from tester2 where whatever not like '%STU%'; no rows selected My question is why does this not return the 3 4 columns? _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gary Jackson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: comparing null values
While I agree that it could return the rows, I also understand the system
side.
Null is nothing so it can not be compared to anything else, even another
null. It is something like:
I don't know whether it is like '%STU%' or not because it is nothing - I
don't know what nothing is
Witold
-Original Message-
Jackson
Sent: 30 January 2003 3:27 PM
To: Multiple recipients of list ORACLE-L
Why is this?? If I know that value X has no value, I certainly know that
value X is not like '%STU%' ?
From: Whittle Jerome Contr NCI [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
CC: [EMAIL PROTECTED]
Subject: RE: comparing null values
Date: Thu, 30 Jan 2003 13:15:33 -0600
Gary,
Null has no value. It can't be like or not like anything. If you want to
see the null you'll need to add OR IS NULL to your Where clause.
Jerry Whittle
ASIFICS DBA
NCI Information Systems Inc.
[EMAIL PROTECTED]
618-622-4145
-Original Message-
From: Gary Jackson [SMTP:[EMAIL PROTECTED]]
Can anyone explain why it is that I seem unable to use 'like' and 'not
like'
on columns containing null values. (I am unable to find information
regarding this on MetaLink.)
For example:
SQL select * from tester2;
COL1 COL2 WHATEVER
11STUFF
22STUFF
33
44
SQL select * from tester2 where whatever not like '%STU%';
no rows selected
My question is why does this not return the 3 4 columns?
_
Protect your PC - get McAfee.com VirusScan Online
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Gary Jackson
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Witold Iwaniec
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: comparing null values
Gary,
it's a definition thing. a null value belongs to the null set. relational
theory says that the null set may not be compared to anything else. it is
simply not there.
this is different from 1 is not equal to 2.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Thursday, January 30, 2003 2:27 PM
To: Multiple recipients of list ORACLE-L
Why is this?? If I know that value X has no value, I certainly know that
value X is not like '%STU%' ?
From: Whittle Jerome Contr NCI [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
CC: [EMAIL PROTECTED]
Subject: RE: comparing null values
Date: Thu, 30 Jan 2003 13:15:33 -0600
Gary,
Null has no value. It can't be like or not like anything. If you want to
see the null you'll need to add OR IS NULL to your Where clause.
Jerry Whittle
ASIFICS DBA
NCI Information Systems Inc.
[EMAIL PROTECTED]
618-622-4145
-Original Message-
From: Gary Jackson [SMTP:[EMAIL PROTECTED]]
Can anyone explain why it is that I seem unable to use 'like' and 'not
like'
on columns containing null values. (I am unable to find information
regarding this on MetaLink.)
For example:
SQL select * from tester2;
COL1 COL2 WHATEVER
11STUFF
22STUFF
33
44
SQL select * from tester2 where whatever not like '%STU%';
no rows selected
My question is why does this not return the 3 4 columns?
_
Protect your PC - get McAfee.com VirusScan Online
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Gary Jackson
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: comparing null values
Hi Gary, I wonder why you didn't use : select * from tester2 where whatever is null; Null values is extremely empty. Rgrds, Sony -Original Message- From: Gary Jackson [SMTP:[EMAIL PROTECTED]] Sent: Friday, January 31, 2003 12:42 AM To: Multiple recipients of list ORACLE-L Subject: comparing null values Can anyone explain why it is that I seem unable to use 'like' and 'not like' on columns containing null values. (I am unable to find information regarding this on MetaLink.) For example: SQL select * from tester2; COL1 COL2 WHATEVER 11STUFF 22STUFF 33 44 SQL select * from tester2 where whatever not like '%STU%'; no rows selected My question is why does this not return the 3 4 columns? _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gary Jackson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sony kristanto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: NOT IN with NULL-values
Title: RE: NOT IN with NULL-values Yes I had a null value in am91. rgds amar -Original Message- From: Kuijten, F. (Frank) [mailto:[EMAIL PROTECTED]] Sent: Monday, October 29, 2001 6:35 PM To: Multiple recipients of list ORACLE-L Subject: RE: NOT IN with NULL-values Amar, Thanks. One question : Do you have a NULL value in the 'am91' table ? Because that's where my problem is. Without the NULL value, all queries are giving the expected results. Greetings, Frank -Original Message- Sent: maandag 29 oktober 2001 14:50 To: Multiple recipients of list ORACLE-L I tried your example at my end. The both the NOT IN stmts do give me the result. NOT EXITS is functioning properly as there are records existing in sub-query. check the last stmt below for this. I see no bugs or issues. check this: -select * from am90; ---same as fk_ldm1 ID __ 1 2 3 4 -select * from am91; ---same as fk_ldm2 ID __ 1 2 4 -select * from am90 2 minus 3 select * from am91; ID __ 3 -select * from am90 2 where id not in (select * from am91); ID __ 3 -select * from am90 2 where not exists (select * from am91); no rows selected -select * from am90 2 where id not in (select * from am91 where id is not null); ID __ 3 -select * from am90 a 2 where not exists (select * from am91 where id = a.id); ID __ 3 rgds amar -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Monday, October 29, 2001 12:45 PM To: Multiple recipients of list ORACLE-L Hello, I'm running the SQL-statements below : SQL create table fk_ldm1 (id number(10) not null); Table created. SQL insert into fk_ldm1 values(1); 1 row created. SQL insert into fk_ldm1 values(2); 1 row created. SQL insert into fk_ldm1 values(3); 1 row created. SQL insert into fk_ldm1 values(4); 1 row created. SQL create table fk_ldm2 (id number(10)); Table created. SQL insert into fk_ldm2 values(1); 1 row created. SQL insert into fk_ldm2 values(2); 1 row created. SQL insert into fk_ldm2 values(4); 1 row created. SQL insert into fk_ldm2 values(NULL); 1 row created. As you can see, value '3' is not in 'fk_ldm2' and value 'NULL' is not in 'fk_ldm1'. Now for the following selects : SQL select * from fk_ldm1 2 minus 3 select * from fk_ldm2; ID - 3 SQL select * from fk_ldm1 2 where id not in (select * from fk_ldm2); no rows selected SQL select * from fk_ldm1 2 where not exists (select * from fk_ldm2); no rows selected SQL select * from fk_ldm1 2 where id not in (select * from fk_ldm2 where id is not null); ID - 3 I had expected to see value '3' appear in all selects. It has something to do with the NULL value in 'fk_ldm2', but I've got no explanation for this. Why won't value '3' appear as soon as a NULL value is used ??? (It's not a bug as this behaviour is the same in 7.1.5, 8.0.6 and 8.1.6) Greetings, Frank _ Frank Kuijten DBA/Developer Organon Teknika bv P.O. Box 84 5280 AB Boxtel Republic of The Netherlands +31 411 654265 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Kuijten, F. (Frank) INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kuijten, F. (Frank) INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: NOT IN with NULL-values
Title: RE: NOT IN with NULL-values Sorry Frank, I tried that without null. I cross checked and found results for not in similar to yours, as explained by other list members. null is not equal to another value, or null itself. rgds amar -Original Message- From: Kuijten, F. (Frank) [mailto:[EMAIL PROTECTED]] Sent: Monday, October 29, 2001 6:35 PM To: Multiple recipients of list ORACLE-L Subject: RE: NOT IN with NULL-values Amar, Thanks. One question : Do you have a NULL value in the 'am91' table ? Because that's where my problem is. Without the NULL value, all queries are giving the expected results. Greetings, Frank -Original Message- Sent: maandag 29 oktober 2001 14:50 To: Multiple recipients of list ORACLE-L I tried your example at my end. The both the NOT IN stmts do give me the result. NOT EXITS is functioning properly as there are records existing in sub-query. check the last stmt below for this. I see no bugs or issues. check this: -select * from am90; ---same as fk_ldm1 ID __ 1 2 3 4 -select * from am91; ---same as fk_ldm2 ID __ 1 2 4 -select * from am90 2 minus 3 select * from am91; ID __ 3 -select * from am90 2 where id not in (select * from am91); ID __ 3 -select * from am90 2 where not exists (select * from am91); no rows selected -select * from am90 2 where id not in (select * from am91 where id is not null); ID __ 3 -select * from am90 a 2 where not exists (select * from am91 where id = a.id); ID __ 3 rgds amar -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Monday, October 29, 2001 12:45 PM To: Multiple recipients of list ORACLE-L Hello, I'm running the SQL-statements below : SQL create table fk_ldm1 (id number(10) not null); Table created. SQL insert into fk_ldm1 values(1); 1 row created. SQL insert into fk_ldm1 values(2); 1 row created. SQL insert into fk_ldm1 values(3); 1 row created. SQL insert into fk_ldm1 values(4); 1 row created. SQL create table fk_ldm2 (id number(10)); Table created. SQL insert into fk_ldm2 values(1); 1 row created. SQL insert into fk_ldm2 values(2); 1 row created. SQL insert into fk_ldm2 values(4); 1 row created. SQL insert into fk_ldm2 values(NULL); 1 row created. As you can see, value '3' is not in 'fk_ldm2' and value 'NULL' is not in 'fk_ldm1'. Now for the following selects : SQL select * from fk_ldm1 2 minus 3 select * from fk_ldm2; ID - 3 SQL select * from fk_ldm1 2 where id not in (select * from fk_ldm2); no rows selected SQL select * from fk_ldm1 2 where not exists (select * from fk_ldm2); no rows selected SQL select * from fk_ldm1 2 where id not in (select * from fk_ldm2 where id is not null); ID - 3 I had expected to see value '3' appear in all selects. It has something to do with the NULL value in 'fk_ldm2', but I've got no explanation for this. Why won't value '3' appear as soon as a NULL value is used ??? (It's not a bug as this behaviour is the same in 7.1.5, 8.0.6 and 8.1.6) Greetings, Frank _ Frank Kuijten DBA/Developer Organon Teknika bv P.O. Box 84 5280 AB Boxtel Republic of The Netherlands +31 411 654265 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Kuijten, F. (Frank) INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kuijten, F. (Frank) INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
NOT IN with NULL-values
Hello, I'm running the SQL-statements below : SQL create table fk_ldm1 (id number(10) not null); Table created. SQL insert into fk_ldm1 values(1); 1 row created. SQL insert into fk_ldm1 values(2); 1 row created. SQL insert into fk_ldm1 values(3); 1 row created. SQL insert into fk_ldm1 values(4); 1 row created. SQL create table fk_ldm2 (id number(10)); Table created. SQL insert into fk_ldm2 values(1); 1 row created. SQL insert into fk_ldm2 values(2); 1 row created. SQL insert into fk_ldm2 values(4); 1 row created. SQL insert into fk_ldm2 values(NULL); 1 row created. As you can see, value '3' is not in 'fk_ldm2' and value 'NULL' is not in 'fk_ldm1'. Now for the following selects : SQL select * from fk_ldm1 2 minus 3 select * from fk_ldm2; ID - 3 SQL select * from fk_ldm1 2 where id not in (select * from fk_ldm2); no rows selected SQL select * from fk_ldm1 2 where not exists (select * from fk_ldm2); no rows selected SQL select * from fk_ldm1 2 where id not in (select * from fk_ldm2 where id is not null); ID - 3 I had expected to see value '3' appear in all selects. It has something to do with the NULL value in 'fk_ldm2', but I've got no explanation for this. Why won't value '3' appear as soon as a NULL value is used ??? (It's not a bug as this behaviour is the same in 7.1.5, 8.0.6 and 8.1.6) Greetings, Frank _ Frank Kuijten DBA/Developer Organon Teknika bv P.O. Box 84 5280 AB Boxtel Republic of The Netherlands +31 411 654265 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kuijten, F. (Frank) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: NOT IN with NULL-values
Title: RE: NOT IN with NULL-values I tried your example at my end. The both the NOT IN stmts do give me the result. NOT EXITS is functioning properly as there are records existing in sub-query. check the last stmt below for this. I see no bugs or issues. check this: -select * from am90; ---same as fk_ldm1 ID __ 1 2 3 4 -select * from am91; ---same as fk_ldm2 ID __ 1 2 4 -select * from am90 2 minus 3 select * from am91; ID __ 3 -select * from am90 2 where id not in (select * from am91); ID __ 3 -select * from am90 2 where not exists (select * from am91); no rows selected -select * from am90 2 where id not in (select * from am91 where id is not null); ID __ 3 -select * from am90 a 2 where not exists (select * from am91 where id = a.id); ID __ 3 rgds amar -Original Message- From: Kuijten, F. (Frank) [mailto:[EMAIL PROTECTED]] Sent: Monday, October 29, 2001 12:45 PM To: Multiple recipients of list ORACLE-L Subject: NOT IN with NULL-values Hello, I'm running the SQL-statements below : SQL create table fk_ldm1 (id number(10) not null); Table created. SQL insert into fk_ldm1 values(1); 1 row created. SQL insert into fk_ldm1 values(2); 1 row created. SQL insert into fk_ldm1 values(3); 1 row created. SQL insert into fk_ldm1 values(4); 1 row created. SQL create table fk_ldm2 (id number(10)); Table created. SQL insert into fk_ldm2 values(1); 1 row created. SQL insert into fk_ldm2 values(2); 1 row created. SQL insert into fk_ldm2 values(4); 1 row created. SQL insert into fk_ldm2 values(NULL); 1 row created. As you can see, value '3' is not in 'fk_ldm2' and value 'NULL' is not in 'fk_ldm1'. Now for the following selects : SQL select * from fk_ldm1 2 minus 3 select * from fk_ldm2; ID - 3 SQL select * from fk_ldm1 2 where id not in (select * from fk_ldm2); no rows selected SQL select * from fk_ldm1 2 where not exists (select * from fk_ldm2); no rows selected SQL select * from fk_ldm1 2 where id not in (select * from fk_ldm2 where id is not null); ID - 3 I had expected to see value '3' appear in all selects. It has something to do with the NULL value in 'fk_ldm2', but I've got no explanation for this. Why won't value '3' appear as soon as a NULL value is used ??? (It's not a bug as this behaviour is the same in 7.1.5, 8.0.6 and 8.1.6) Greetings, Frank _ Frank Kuijten DBA/Developer Organon Teknika bv P.O. Box 84 5280 AB Boxtel Republic of The Netherlands +31 411 654265 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kuijten, F. (Frank) INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: NOT IN with NULL-values
Amar, Thanks. One question : Do you have a NULL value in the 'am91' table ? Because that's where my problem is. Without the NULL value, all queries are giving the expected results. Greetings, Frank -Original Message- Sent: maandag 29 oktober 2001 14:50 To: Multiple recipients of list ORACLE-L I tried your example at my end. The both the NOT IN stmts do give me the result. NOT EXITS is functioning properly as there are records existing in sub-query. check the last stmt below for this. I see no bugs or issues. check this: -select * from am90; ---same as fk_ldm1 ID __ 1 2 3 4 -select * from am91; ---same as fk_ldm2 ID __ 1 2 4 -select * from am90 2 minus 3 select * from am91; ID __ 3 -select * from am90 2 where id not in (select * from am91); ID __ 3 -select * from am90 2 where not exists (select * from am91); no rows selected -select * from am90 2 where id not in (select * from am91 where id is not null); ID __ 3 -select * from am90 a 2 where not exists (select * from am91 where id = a.id); ID __ 3 rgds amar -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Monday, October 29, 2001 12:45 PM To: Multiple recipients of list ORACLE-L Hello, I'm running the SQL-statements below : SQL create table fk_ldm1 (id number(10) not null); Table created. SQL insert into fk_ldm1 values(1); 1 row created. SQL insert into fk_ldm1 values(2); 1 row created. SQL insert into fk_ldm1 values(3); 1 row created. SQL insert into fk_ldm1 values(4); 1 row created. SQL create table fk_ldm2 (id number(10)); Table created. SQL insert into fk_ldm2 values(1); 1 row created. SQL insert into fk_ldm2 values(2); 1 row created. SQL insert into fk_ldm2 values(4); 1 row created. SQL insert into fk_ldm2 values(NULL); 1 row created. As you can see, value '3' is not in 'fk_ldm2' and value 'NULL' is not in 'fk_ldm1'. Now for the following selects : SQL select * from fk_ldm1 2 minus 3 select * from fk_ldm2; ID - 3 SQL select * from fk_ldm1 2 where id not in (select * from fk_ldm2); no rows selected SQL select * from fk_ldm1 2 where not exists (select * from fk_ldm2); no rows selected SQL select * from fk_ldm1 2 where id not in (select * from fk_ldm2 where id is not null); ID - 3 I had expected to see value '3' appear in all selects. It has something to do with the NULL value in 'fk_ldm2', but I've got no explanation for this. Why won't value '3' appear as soon as a NULL value is used ??? (It's not a bug as this behaviour is the same in 7.1.5, 8.0.6 and 8.1.6) Greetings, Frank _ Frank Kuijten DBA/Developer Organon Teknika bv P.O. Box 84 5280 AB Boxtel Republic of The Netherlands +31 411 654265 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Kuijten, F. (Frank) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kuijten, F. (Frank) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: NOT IN with NULL-values
Frank, It sound like you are confusing the value and concepts of what 'null' means. For example, in your fk_ldm2 table, the record that contains a null value in the first column: If you try and select the 'null' value and compare it with something, it will not work because a 'null' does not equal anything, including another null. In your three selects: (1)SQL select * from fk_ldm1 2 where id not in (select * from fk_ldm2); no rows selected (2)SQL select * from fk_ldm1 2 where not exists (select * from fk_ldm2); no rows selected (3)SQL select * from fk_ldm1 2 where id not in (select * from fk_ldm2 where id is not null); ID - 3 #1 does not work because the where clause will not return a value of true - the 'id' (value 3) cannot be compared to the 'null' value and return a 'true' value. Because the 'null' exists, it throws the whole query away - as evidenced by #3 where you discard null values. #2 does not work because you did not co-relate the two queries together. Change it as suggested to: select * from fk_ldm1 where not exists (select * from fk_ldm2 where fk_ldm1.id = fk_ldm2.id); Notice that this now looks only for values that exist in the fk_ldm1 table, thus it will not search and select the null record in fk_ldm2 table causing the same null comparison problem as above. Remember, a Null does not equal anything, and when compared with something, produces undesirable results (mostly confusing). You need to always provide for null processing either by using the NVL function or some other way to make sure things work properly. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, October 29, 2001 9:35 AM To: Multiple recipients of list ORACLE-L Amar, Thanks. One question : Do you have a NULL value in the 'am91' table ? Because that's where my problem is. Without the NULL value, all queries are giving the expected results. Greetings, Frank -Original Message- Sent: maandag 29 oktober 2001 14:50 To: Multiple recipients of list ORACLE-L I tried your example at my end. The both the NOT IN stmts do give me the result. NOT EXITS is functioning properly as there are records existing in sub-query. check the last stmt below for this. I see no bugs or issues. check this: -select * from am90; ---same as fk_ldm1 ID __ 1 2 3 4 -select * from am91; ---same as fk_ldm2 ID __ 1 2 4 -select * from am90 2 minus 3 select * from am91; ID __ 3 -select * from am90 2 where id not in (select * from am91); ID __ 3 -select * from am90 2 where not exists (select * from am91); no rows selected -select * from am90 2 where id not in (select * from am91 where id is not null); ID __ 3 -select * from am90 a 2 where not exists (select * from am91 where id = a.id); ID __ 3 rgds amar -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Monday, October 29, 2001 12:45 PM To: Multiple recipients of list ORACLE-L Hello, I'm running the SQL-statements below : SQL create table fk_ldm1 (id number(10) not null); Table created. SQL insert into fk_ldm1 values(1); 1 row created. SQL insert into fk_ldm1 values(2); 1 row created. SQL insert into fk_ldm1 values(3); 1 row created. SQL insert into fk_ldm1 values(4); 1 row created. SQL create table fk_ldm2 (id number(10)); Table created. SQL insert into fk_ldm2 values(1); 1 row created. SQL insert into fk_ldm2 values(2); 1 row created. SQL insert into fk_ldm2 values(4); 1 row created. SQL insert into fk_ldm2 values(NULL); 1 row created. As you can see, value '3' is not in 'fk_ldm2' and value 'NULL' is not in 'fk_ldm1'. Now for the following selects : SQL select * from fk_ldm1 2 minus 3 select * from fk_ldm2; ID - 3 SQL select * from fk_ldm1 2 where id not in (select * from fk_ldm2); no rows selected SQL select * from fk_ldm1 2 where not exists (select * from fk_ldm2); no rows selected SQL select * from fk_ldm1 2 where id not in (select * from fk_ldm2 where id is not null); ID - 3 I had expected to see value '3' appear in all selects. It has something to do with the NULL value in 'fk_ldm2', but I've got no explanation for this. Why won't value '3' appear as soon as a NULL value is used ??? (It's not a bug as this behaviour is the same in 7.1.5, 8.0.6 and 8.1.6) Greetings, Frank _ Frank Kuijten DBA/Developer Organon Teknika bv P.O. Box 84 5280 AB Boxtel Republic of The Netherlands +31 411 654265 [EMAIL PROTECTED] -- Please see the official ORACLE-L
RE: NOT IN with NULL-values
Frank, A NOT IN evaluates to FALSE if *any* member of the set is NULL. http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/ a85397/operator.htm#1003933 Also scroll up just a tad on that link and look at the comments for NOT IN in table 3.4. This is a subtlety that many folks overlook until it bites them on the tail. With regards to your NOT EXISTS, you didn't include correlation between the two tables (which Amar pointed out with the example). Try this: SQL select * from fk_ldm1 a 2 where not exists (select * from fk_ldm2 b where a.id = b.id); ID -- 3 For each row in fk_ldm1 the sub-query is evaluated. And since you did not include correlation, the sub-query will always return a row(s) (there's no criteria on it), thus making the NOT EXISTS condition FALSE for each row in fk_ldm1. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Kuijten, F. (Frank) Sent: Monday, October 29, 2001 8:35 AM To: Multiple recipients of list ORACLE-L Subject: RE: NOT IN with NULL-values Amar, Thanks. One question : Do you have a NULL value in the 'am91' table ? Because that's where my problem is. Without the NULL value, all queries are giving the expected results. Greetings, Frank -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
slightly OT: visual c++ and null values
hello!
our developers here are requesting that I put defaults (' ' for
chars/varchars, 0 for numbers, etc) in place instead of nulls in all
columns that would supposedly allow null values...
they are using visual c++ and they say that they cannot make visual c++
retreive null values...
i am very hesitant in implementing this...
i have no knowledge whatsoever of visual c++ and don't know if this is
true...
i'd like to know if anyone had encountered this same problem before?
or maybe someone can tell me if there is any truth to what our
developers are saying...
oracle817 on solaris7
thanks =)
--
Maria Aurora VT de la Vega (OCP)
Database Specialist
Philippine Stock Exchange, Inc.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Maria Aurora VT de la Vega
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: slightly OT: visual c++ and null values
Maria:
The developers need to read the PRO*C manual. What the developers need to do
is to use indicator variable. These are varaiables that indicate whether the
variable is NULL (or if the value was truncated when selected into).
Here is a short example.
char v_first_name[18];
short v_first_name_ind;
SELECT first_name
INTO v_first_name:v_first_name_ind
FROM emp
WHERE emplid = :v_emplid:v_emplid_ind;
I forget the values for the indicator variables, but that is in the PRO*C
manual.
HTH
Kevin
-Original Message-
Sent: Friday, October 19, 2001 9:00 AM
To: Multiple recipients of list ORACLE-L
hello!
our developers here are requesting that I put defaults (' ' for
chars/varchars, 0 for numbers, etc) in place instead of nulls in all
columns that would supposedly allow null values...
they are using visual c++ and they say that they cannot make visual c++
retreive null values...
i am very hesitant in implementing this...
i have no knowledge whatsoever of visual c++ and don't know if this is
true...
i'd like to know if anyone had encountered this same problem before?
or maybe someone can tell me if there is any truth to what our
developers are saying...
oracle817 on solaris7
thanks =)
--
Maria Aurora VT de la Vega (OCP)
Database Specialist
Philippine Stock Exchange, Inc.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Maria Aurora VT de la Vega
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Toepke, Kevin M
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: slightly OT: visual c++ and null values
If you use ODBC (CTransaction + CRecordset in MFC) there is no problem to
handle null values.
It isn't good idea to put 0 (zero) instead of null in number column.
JP
On Fri 19. October 2001 15:00, you wrote:
hello!
our developers here are requesting that I put defaults (' ' for
chars/varchars, 0 for numbers, etc) in place instead of nulls in all
columns that would supposedly allow null values...
they are using visual c++ and they say that they cannot make visual c++
retreive null values...
i am very hesitant in implementing this...
i have no knowledge whatsoever of visual c++ and don't know if this is
true...
i'd like to know if anyone had encountered this same problem before?
or maybe someone can tell me if there is any truth to what our
developers are saying...
oracle817 on solaris7
thanks =)
--
Maria Aurora VT de la Vega (OCP)
Database Specialist
Philippine Stock Exchange, Inc.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jan Pruner
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: slightly OT: visual c++ and null values
Hi,
sorry for the strong language, but the statement that VC++ isn't able to
retrieve NULL values is plain bullshit.
| Regards, |
| Stefan Jahnke |
| BOV AG |
| @:D2 Vodafone, Abt.: FBOM |
| Tel.: 0211/533-4893|
Maria Aurora VT de la Vega schrieb:
hello!
our developers here are requesting that I put defaults (' ' for
chars/varchars, 0 for numbers, etc) in place instead of nulls in all
columns that would supposedly allow null values...
they are using visual c++ and they say that they cannot make visual c++
retreive null values...
i am very hesitant in implementing this...
i have no knowledge whatsoever of visual c++ and don't know if this is
true...
i'd like to know if anyone had encountered this same problem before?
or maybe someone can tell me if there is any truth to what our
developers are saying...
oracle817 on solaris7
thanks =)
--
Maria Aurora VT de la Vega (OCP)
Database Specialist
Philippine Stock Exchange, Inc.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Maria Aurora VT de la Vega
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
-
This Mail has been checked for Viruses
Attention: Encrypted mails can NOT be checked!
**
Diese Mail wurde auf Viren geprueft
Hinweis: Verschluesselte mails koennen NICHT auf Viren geprueft werden!
-
--
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stefan Jahnke
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: slightly OT: visual c++ and null values
Maria,
They need to use indicator variables. This is documented in the Pro*C
documentation. The application that I wrote is two years old but is still
operational against a 8.1.x database. I tried to find a small snippet of code
to include but since I haven't worked on the application in about two years
couldn't find anything quickly.
The gist of it is though that you define short integer variables in addition to
your variable to hold your column value. The column is then referenced in the
select/update statement as a concatenation of the two variables.
:column_name:indicatorthen if the indicator is 0 the column variable has
undefined value (NULL). For inserts/updates the user sets the indicator to -1
and Pro*C interpretes the value as a NULL.
I admit that my version of Visual C++ is old 5.0 and my version of Pro*C is
8.0.5 but this does work against a 8.1.x database (on Compaq) but I haven't seen
anything to indicate that they were getting away from indicator variables.
Shari Dishop
SAP ABAP Development - Project Systems Team
Northrop Grumman Information Technology
Baltimore, MD
hello!
our developers here are requesting that I put defaults (' ' for
chars/varchars, 0 for numbers, etc) in place instead of nulls in all
columns that would supposedly allow null values...
they are using visual c++ and they say that they cannot make visual c++
retreive null values...
i am very hesitant in implementing this...
i have no knowledge whatsoever of visual c++ and don't know if this is
true...
i'd like to know if anyone had encountered this same problem before?
or maybe someone can tell me if there is any truth to what our
developers are saying...
oracle817 on solaris7
thanks =)
--
Maria Aurora VT de la Vega (OCP)
Database Specialist
Philippine Stock Exchange, Inc.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Shari Dishop
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: slightly OT: visual c++ and null values
I forget the values for the indicator variables, but that is in the PRO*C
manual.
I think the values are 0 1 (false true). At least they are in Cobol.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Friday, October 19, 2001 9:50 AM
To: Multiple recipients of list ORACLE-L
Maria:
The developers need to read the PRO*C manual. What the developers need to do
is to use indicator variable. These are varaiables that indicate whether the
variable is NULL (or if the value was truncated when selected into).
Here is a short example.
char v_first_name[18];
short v_first_name_ind;
SELECT first_name
INTO v_first_name:v_first_name_ind
FROM emp
WHERE emplid = :v_emplid:v_emplid_ind;
I forget the values for the indicator variables, but that is in the PRO*C
manual.
HTH
Kevin
-Original Message-
Sent: Friday, October 19, 2001 9:00 AM
To: Multiple recipients of list ORACLE-L
hello!
our developers here are requesting that I put defaults (' ' for
chars/varchars, 0 for numbers, etc) in place instead of nulls in all
columns that would supposedly allow null values...
they are using visual c++ and they say that they cannot make visual c++
retreive null values...
i am very hesitant in implementing this...
i have no knowledge whatsoever of visual c++ and don't know if this is
true...
i'd like to know if anyone had encountered this same problem before?
or maybe someone can tell me if there is any truth to what our
developers are saying...
oracle817 on solaris7
thanks =)
--
Maria Aurora VT de la Vega (OCP)
Database Specialist
Philippine Stock Exchange, Inc.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Maria Aurora VT de la Vega
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Toepke, Kevin M
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: slightly OT: visual c++ and null values
Hi,
From the book:
EXEC SQL BEGIN DECLARE SECTION;
intemp_number;
float salary, commission;
short comm_ind; /* indicator variable */
EXEC SQL END DECLARE SECTION;
char temp[16];
float pay; /* not used in a SQL statement */
...
printf(Employee number? );
gets(temp);
emp_number = atof(temp);
EXEC SQL SELECT SAL, COMM
INTO :salary, :commission:ind_comm
FROM EMP
WHERE EMPNO = :emp_number;
if(ind_comm == -1)/* commission is null */
pay = salary;
else
pay = salary + commission;
--
Chris J. Guidry P.Eng.
ATCO Electric, Metering Services
Phone: (780) 420-4142
Fax: (780) 420-3854
Email: [EMAIL PROTECTED]
-Original Message-
From: Mercadante, Thomas F [SMTP:[EMAIL PROTECTED]]
Sent: Friday, October 19, 2001 09:25 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: slightly OT: visual c++ and null values
Stefan,
OK. can you help with a solution? sounds like you have experience...
:)
How about some coding examples?
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Friday, October 19, 2001 10:05 AM
To: Multiple recipients of list ORACLE-L
Hi,
sorry for the strong language, but the statement that VC++ isn't able to
retrieve NULL values is plain bullshit.
| Regards, |
| Stefan Jahnke |
| BOV AG |
| @:D2 Vodafone, Abt.: FBOM |
| Tel.: 0211/533-4893|
Maria Aurora VT de la Vega schrieb:
hello!
our developers here are requesting that I put defaults (' ' for
chars/varchars, 0 for numbers, etc) in place instead of nulls in all
columns that would supposedly allow null values...
they are using visual c++ and they say that they cannot make visual c++
retreive null values...
i am very hesitant in implementing this...
i have no knowledge whatsoever of visual c++ and don't know if this is
true...
i'd like to know if anyone had encountered this same problem before?
or maybe someone can tell me if there is any truth to what our
developers are saying...
oracle817 on solaris7
thanks =)
--
Maria Aurora VT de la Vega (OCP)
Database Specialist
Philippine Stock Exchange, Inc.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Maria Aurora VT de la Vega
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
-
This Mail has been checked for Viruses
Attention: Encrypted mails can NOT be checked!
**
Diese Mail wurde auf Viren geprueft
Hinweis: Verschluesselte mails koennen NICHT auf Viren geprueft werden!
-
--
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stefan Jahnke
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Guidry, Chris
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
RE: slightly OT: visual c++ and null values
Pro*C has indicator variables.
MFC's ODBC classes have IsNull functions.
Both methods add an extra step to the code to check for null, so it is a
pain to implement (and a major pain if all the columns are nullable), but it
is doable.
Brian Norrell
Manager, MPI Development
QuadraMed
511 E John Carpenter Frwy, Su 500
Irving, TX 75062
(972) 831-6600
-Original Message-
Sent: Friday, October 19, 2001 8:00 AM
To: Multiple recipients of list ORACLE-L
hello!
our developers here are requesting that I put defaults (' ' for
chars/varchars, 0 for numbers, etc) in place instead of nulls in all
columns that would supposedly allow null values...
they are using visual c++ and they say that they cannot make visual c++
retreive null values...
i am very hesitant in implementing this...
i have no knowledge whatsoever of visual c++ and don't know if this is
true...
i'd like to know if anyone had encountered this same problem before?
or maybe someone can tell me if there is any truth to what our
developers are saying...
oracle817 on solaris7
thanks =)
--
Maria Aurora VT de la Vega (OCP)
Database Specialist
Philippine Stock Exchange, Inc.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Maria Aurora VT de la Vega
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Norrell, Brian
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Null values
Hallo you DBA's: How can I modify in this script to make it handle also null values. This script only handles 0-values. Hope anyone can help me with this. vOWNERSHIP_NO:= rpad(to_char(nvl(recCursor.OWNERSHIP_NO,' 0')),2,' '); Thanks in advance Roland Sköldblom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
