Gossamer Forum
Home : Products : DBMan SQL : Discussion :

Using advanced WHERE SQL statement?

Quote Reply
Using advanced WHERE SQL statement?
Dear Forum Programmers,

Been working on my heavily modified DbMan SQL and wondering if DbMan will allow me to run the results of the following SQL syntax:

Code:

SELECT DISTINCT Carriers FROM dbo.Select_Entries WHERE (Carriers NOT IN
(SELECT DISTINCT Carrier FROM dbo.Insured WHERE (PatientID = '000000-DMBAPatient')
AND (Type = '1')))

First, don't get scared by the statement. There is a SELECT statement within the WHERE clause which I can do with a condition quite easily. My basic question is with everything I have seen you can only do a SELECT x WHERE x = x. How can I make the WHERE statement be straight text (eg. no equals in it like in the above SQL)?

Here is what I have come up with so far but the result is an empty set. (eg. nothing)

Code:

# Create the condition.
my $cond = GT::SQL::Condition->new('Carriers', 'NOT IN', \"(SELECT DISTINCT
Carrier FROM Insured WHERE (PatientID = $tags->{PatientID}) AND (Type = $tags->{Type}))");

# Perform the selection of the table
my $sth = $table->select ( { 'Carriers' => $cond }, 'Carriers');

# Create the pulldown form element.
my $output = '<select name="Carrier" id="Carrier" size="1" class="RequiredEntry">';
$output .= ' <option value="" SELECTED>-- Select One --</option>';
while (my $row = $sth->fetchrow_hashref) {
$row->{'Carriers'} =~ s/\s+$//;
if($row->{'Carriers'} ne ''){
$output .= '<option value="'.$row->{'Carriers'}.'">'.$row->{'Carriers'}.'</option>';
}
}
$output .= '</select>';
return $output;

I'm doing something wrong but not quite sure where that is. I suspect it is with the SELECT statement. I know it is a little advanced and I can give a basic table diagram if needed but I thought if maybe someone can see syntax errors or how I am calling the condition.

Thank you in advance!
Quote Reply
Re: [LanceWilson2] Using advanced WHERE SQL statement? In reply to
Hi,

GT::SQL does not support for sup-query, but you can do something like this instead:

my $list = $DB->table("Insured")->select('Carrier', { PatientID => 'xxxxxxxxxxx' })->fetchall_arrayref;
my @ids = map $_->[0], @$list;

my $results = $DB->table('Select')->select(GT::SQL::Condition->new(Carriers => 'NOT IN' => \@ids))->fetchall_hashref;
# For debugging
use Data::Dumper;
print "<pre>".Dumper($results)."</pre>";

Hope that helps.

TheStone.

B.
Quote Reply
Re: [TheStone] Using advanced WHERE SQL statement? In reply to
Ok... ok.. i see where you are going with this. It should work great. But I get an error when I create this statement.
Code:

Can't call method "fetchall_hashref" on an undefined value at (eval 20) line 21.

Which i know is just a general error because of an empty set or error further up the line. I did some back tracking and it all works except for this statement here:
Code:

GT::SQL::Condition->new('Carriers' => 'NOT IN' => \@ids )

This is the error that I get back from the SQL server:
Code:

GT::SQL::error = Failed to execute query: 'SELECT * FROM Select_Entries WHERE ( Carriers NOT IN ? ) '
Reason: [unixODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '@P1'. (SQL-42000)

It looks as if the script is passing the array item as SQL code to the server. Am I correct?? How do I get it to print out the exact values and not the array reference to the SQL server?

Maybe I'm on the wrong track here?

For reference, here is the code I'm using right now to get this output:
Code:

my $tags = shift;


# Ok.. create a condition to narrow the patient records.
my $cond = GT::SQL::Condition->new(
'PatientID', '=', $tags->{PatientID},
'Type', '=', $tags->{Type}
);

# Now select the above condition, returning only carrier names.
my $list = $DB->table("Insured")->select('Carrier', $cond)->fetchall_arrayref;
my @ids = map $_->[0], @$list;

# Now only select the records from "Select_Entries" that are NOT matching in Insured table (above).
my $results = $DB->table('Select_Entries')->select(GT::SQL::Condition->new('Carriers' => 'NOT IN' => \@ids))->fetchall_hashref;


# For debugging
use Data::Dumper;
print "<pre>".Dumper($results)."</pre>";
Quote Reply
Re: [LanceWilson2] Using advanced WHERE SQL statement? In reply to
Does anybody have a solution to this? I'm still looking for an answer of some type.