This is not a DBI issue. Look at the DECODE() or NVL() functions. ----------------- Ron Reidy Senior DBA Array BioPharma, Inc.
-----Original Message----- From: Christopher G Tantalo [mailto:[EMAIL PROTECTED] Sent: Thursday, April 29, 2004 2:53 PM To: [EMAIL PROTECTED] Subject: Selecting a record froma table where a column might be null 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.
