This doesn't look like Oracle.
In Oracle:
create table foo (i int, c char(10) null);
SQL> desc foo
Name Null? Type
----------------------------------------- -------- -----------
I NUMBER(38)
C CHAR(10)
SQL> insert into foo values (1, null);
1 row created.
SQL> insert into foo values (2, '2');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from foo;
I C
---------- ----------
1
2 2
SQL> select * from foo where c is null;
I C
---------- ----------
1
SQL> select * from foo where c = null;
no rows selected
SQL> select * from foo where c = '';
no rows selected
SQL> select * from foo where nvl(c,'x') = 'x';
I C
---------- ----------
1
SQL> select * from foo where decode(c,null,'x',c) = 'x';
I C
---------- ----------
1
SQL> select * from foo where decode(c,null,'x',c) is not null;
I C
---------- ----------
1
2 2
-----------------
Ron Reidy
Senior DBA
Array BioPharma, Inc.
303.386.1480
-----Original Message-----
From: Chuck Fox [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 29, 2004 3:21 PM
To: Reidy, Ron
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Selecting a record froma table where a column might be null
Hmmm,
So column != column when the column contains a null. How very unusual. I would have
assumed that null = null, but there I go assuming things again.
So why does this work ?
create table foo( i int, c char(10) null )
go
insert foo values( 1, null )
insert foo values( 2, "2" )
go
select * from foo where c = null
go
declare @c char(10)
select @c = null
select * from foo where c = isnull( @c, c )
go
[EMAIL PROTECTED] wrote:
This does not work in Oracle. A NULL value is the absence of value and cannot be
compared to anything (including NULL).
-----------------
Ron Reidy
Senior DBA
Array BioPharma, Inc.
-----Original Message-----
From: Chuck Fox [ mailto:[EMAIL PROTECTED]
Sent: Thursday, April 29, 2004 3:01 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: Selecting a record froma table where a column might be null
You can try reformatting your query like this:
select dns_id
from dmg.do_not_solicit
where dns_prospect_id = isnull( ?,
dns_prospect_id )
and dns_area_code = isnull( ? ,
dns_area_code )
and dns_phone = isnull( ?, dns_phone )
and dns_zip_code = isnull( ? ,
dns_zip_code )
and dns_pr_branch = isnull( ? ,
dns_pr_branch )
and dns_pr_client_nbr = isnull( ? ,
dns_pr_client_nbr )
and dns_pr_client_status = isnull( ?,
dns_pr_client_status ) ");
This will cause a null parameter to be replaced with column=column which
is always true and should select the row.
HTH,
Your Friendly Neighborhood DBA,
Chuck
[EMAIL PROTECTED] wrote:
I am trying to retrieve a record from a table, and one of the
parameters in the where clause happens to be null, and the select I am
doing below in my script, the id does not come back, although I can
use TOAD and view the record in the table. It just happens that the
branch parameter is NULL. Is there anyway to do this select with a
parameter that is NULL in a dynamic fashion?
And I have tried the quote dbi function, but that doesnt seem to work
either.
Any help would be greatly appreciated.
Chris
#!/usr/local/bin/perl
#
use warnings;
use strict;
use DBI;
$database_name = "nssdev8";
eval { $dbh = DBI->connect("dbi:Oracle:" . $database_name,"","",{
RaiseError => 1,
PrintError => 0,
AutoCommit => 1
});};
if($@)
{
exit; # exit the program, since error from not connecting to db
}
#### gathering of other variables cut to save space, since it is many
other selects
####
####
my $sth = $dbh->prepare("select dns_id
from dmg.do_not_solicit
where dns_prospect_id = ?
and dns_area_code = ?
and dns_phone = ?
and dns_zip_code = ?
and dns_pr_branch = ?
and dns_pr_client_nbr = ?
and dns_pr_client_status = ?");
$sth->bind_param(1,$pro_prospect_id);
$sth->bind_param(2,$dnc_areacode);
$sth->bind_param(3,$dnc_phone);
$sth->bind_param(4,$dnc_zipcode);
$sth->bind_param(5,$pro_pr_branch);
$sth->bind_param(6,$pro_pr_client_nbr);
$sth->bind_param(7,$pro_pr_client_status);
# execute sth3 above
$sth->execute();
$sth->bind_columns(\$dns_id);
$sth->fetch;
print "dnsid $dns_id\n";
------------------
this is end here
-------------------------------------
this is a sample output of a record that fails
---------------------------------------------
new record here
pros id 477974
areacode 716
phone 4733866
zipcode 14618
branch
client nbr 00000
client status P
Use of uninitialized value in concatenation (.) or string at ./dnc.pl
line 205.
dnsid
-----------------------------------------
The information contained in this message may be privileged,
confidential, and protected from disclosure. If the reader of this
message is not the intended recipient, or any employee or agent
responsible for delivering this message to the intended recipient, you
are hereby notified that any dissemination, distribution, or copying
of this communication is strictly prohibited. If you have received
this communication in error, please notify us immediately by replying
to the message and deleting it from your computer.
Thank you. Paychex, Inc.
This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.
This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.
