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).
