RE: null values

[Bretz, Ellen]

This works great but how do I switch something under @fields
from defined on one select to undefined the next time through.
Having the field null doesn't work.

-----Original Message-----
From: Christopher G Tantalo [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 05, 2004 8:11 AM
To: Bretz, Ellen
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.