I posted this solution originally. Please be aware that this solution does require a "prepare" for each execute. So if you are running this logic in a loop many times, the run time will be more expensive than other methods that were posted. You can monitor the performance of your app and see if that is an issue for you.
The other methods that were posted allow you to construct a "fixed" (i.e. unchanging) SQL string, using extended features of Oracle (which I believe are all non-ANSI). That allows you to prepare once, and execute many times, which more efficient. I would advise you to use the technique below only if: - The number of executes is small or performance is not an important issue in your app. - Your non-Oracle database does not support any of the other methods, or portability to other databases is very important. In my case, I work with Informix databases, and none of the posted methods are supported in Informix. I checked the documentation, to see if there was anything similar, and came up empty. My docs are old, so if any one knows otherwise, I'd like to hear about it. Or maybe I should get new docs. :-) With all the disclaimers out of the way, below are the answers to your questions, and more. Marked with a ">>" -----Original Message----- From: Bretz, Ellen [mailto:[EMAIL PROTECTED] 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] 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 ); >> >> The above is designed to be executed once in your program. >> my @fields = ( $dnc_areacode, $dnc_phone, $dnc_zipcode, $pro_pr_branch, $pro_pr_client_nbr, $pro_pr_client_status ); >> >> The above statement is designed to be executed multiple times >> with each prepare & execute, in order to refresh the array >> with the latest values. However, it would be better to >> pair this array up the first, and execute it once. You can do that >> with references. See an alternate solution for this below... >> 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]) >> >> ................................... ^ Bug! Should be: if defined($fields[$i]); >> } =================================================== Alt solution, starting with fields, with loop added. By storing references, you can make changes to the variables, and the sql contruction Code will contiue to pick up the latest values in the variables. Warning. This is untested. Caveat Emptor. my @fields = ( \$dnc_areacode, \$dnc_phone, \$dnc_zipcode, \$pro_pr_branch, \$pro_pr_client_nbr, \$pro_pr_client_status ); while ($some_condition) { # make changes to variables here 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]}); } # execute, process results, etc. here } >> I realize that a more elegant solution might be to combine the two arrays, >> columns and fields, into a single array of hashes. I'll leave that as an >> "exercise for the reader".
