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

Christopher G Tantalo Fri, 30 Apr 2004 04:47:20 -0700

Umm, I do not want to insert a value into the column if it is NULL. I am retrieving the info from a previous statement, that happens to be null, and want to insert it, using the statement below with it being NULL. That is the issue at hand. Reidy, Ron wrote:

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.

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

Reply via email to