The OP's problem was specific to Oracle and as such, he should use the DB facilities that available. While on the surface, the below solution looks elegant, from an Oracle performance perspective, it is horrific. By building the SQL statement dynamically in this manner, statement reuse is almost assuredly eliminated. It is better (IMHO) to use the DB facilities available (in this case Oracle's NVL() or DECODE() functions) than to try to write something that will be portable across all RDBMSs.
----------------- Ron Reidy Senior DBA Array BioPharma, Inc. -----Original Message----- From: CAMPBELL, BRIAN D (BRIAN) [mailto:[EMAIL PROTECTED] Sent: Friday, April 30, 2004 2:03 PM To: '[EMAIL PROTECTED]'; [EMAIL PROTECTED] Subject: RE: Selecting a record froma table where a column might be null 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 = ? This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
