Hi, In perl dbi, it passes arguments everything as single quoted. So you use the decode function in your where class. example dns_pr_branch = decode(?,'null',NULL). string null in decode you can change appropriatly to the value you are passing.
hope it helps Shiv --- Christopher G Tantalo <[EMAIL PROTECTED]> wrote: > 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. > __________________________________ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover
