my $sth = $dbh->prepare('select * from mytable
where ( my_col= ?
or my_col is null and ? is null)');
my $scalar = undef; #to get rows where null columns
# or
# my $scalar = 'something meaningful here'; #to get non-nulls
$sth->execute($scalar, $scalar); # to get null columns
Above code is untested, but something very similar should work.
____________________________
Jeff Seger
Fairchild Semiconductor
[EMAIL PROTECTED]
____________________________
"Christopher G Tantalo" <[EMAIL PROTECTED]>
05/05/2004 08:11 AM
Please respond to ctantalo
To: "Bretz Ellen" <[EMAIL PROTECTED]>
cc: [EMAIL PROTECTED]
Subject: Re: null values
Bretz, Ellen wrote:
>
>
>
>
>I have a prepare statement for a select using DBI In ORACLE.
>
>
>
>One of the attributes can be null or can contain data. How do I set up
>my scalar for the
>
>execute?
>
>
>
>I tried $scalar = 'NULL';
>
>if it was null but didn't work.
>
>
>
>Thanks
>
I just ran into this problem last week, and got a great solution. I am
re-posting the code that a fellow newsgrouper gave to me for my solution
my $sql = "select dns_id
from dmg.do_not_solicit
where dns_prospect_id = ?\n";
my @columns = qw(
dns_area_code
dns_phone
dns_zip_code
dns_pr_branch
dns_pr_client_nbr
dns_pr_client_status
);
my @fields = (
$dnc_areacode,
$dnc_phone,
$dnc_zipcode,
$pro_pr_branch,
$pro_pr_client_nbr,
$pro_pr_client_status
);
for (my $i = 0; $i <= $#columns; $i++)
{
$sql .= " AND " . $columns[$i] .
(defined($fields[$i])? " = ?" : " IS NULL") . "\n";
}
my $sth = $dbh->prepare($sql);
$sth->bind_param(1,$pro_prospect_id);
my $j = 2;
for (my $i = 0; $i <= $#fields; $i++)
{
$sth->bind_param($j++,$fields[$i]) if (defined(@fields[$i])
}
-------------
If the 3rd and 4th values are undef in @fields, this produces the SQL
statement:
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 IS NULL
AND dns_pr_client_nbr IS NULL
AND dns_pr_client_status = ?
--
-------------------------------
Just Your Friendly Neighborhood
_SPIDEY_
-----------------------------------------
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.
