Re: Selecting a record froma table where a column might be null

Chuck Fox Thu, 29 Apr 2004 14:24:37 -0700

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.

Re: Selecting a record froma table where a column might be null

Reply via email to