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

Chuck Fox Thu, 29 Apr 2004 14:05:41 -0700

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.

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

Reply via email to