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

[CAMPBELL, BRIAN D (BRIAN)]

The problem as I understand it is that the snippet of code below could be executed, 
where $pro_pr_branch variable could have "undefined" which would signify a NULL 
database "value".
I which case an "IS NULL" query is desired.

I saw solutions using NVL(), ISNULL(), etc. which appear to be database specific.
I didn't see any general ANSI solutions, so I thought I'd contribute one to the thread.
Here I offer a Perl Idiom that creates ANSI SQL and handles an arbitrary number of 
fields that might be null.

I have used this technique (actually in another language -- but same problem).
I've adapted it to Perl and the problem at hand. Here I've assumed that all but the 
first field might support NULL values.

-----Original Message-----
From: Christopher G Tantalo [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 29, 2004 1:53 PM

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,
<snipped>
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);
<snipped>

----------------------------------------------------------------------------

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 = ?

Brian Campbell.vcf
Description: Binary data