Chuck,
Try to reformat your select to something like this:
$strSQL="select dns_id from dmg.do_not_solicit ";
$strSQL.="where dns_prospect_id = '".$pro_prospect_id."'";
$strSQL.=" and dns_area_code = '".$dnc_areacode."'";
$strSQL.=" and dns_phone = '".$dnc_phone."'";
$strSQL.=" and dns_zip_code = '".$dnc_zipcode."'";
$strSQL.=" and dns_pr_branch = '".$pro_pr_branch."'";
$strSQL.=" and dns_pr_client_nbr = '".$pro_pr_client_nbr."'";
$strSQL.=" and dns_pr_client_status = '".$pro_pr_client_status."'";
$sth=$dbh->prepare($strSQL);
$sth->execute();
This should enclose the variable in single qutoes ' '.
Patty
Chuck Fox
<[EMAIL PROTECTED]
m> To
[EMAIL PROTECTED]
04/29/2004 04:01 cc
PM [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.
>
