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!
Subject Author Views Date
Thread Using advanced WHERE SQL statement? LanceWilson2 3150 Feb 14, 2005, 12:29 AM
Thread Re: [LanceWilson2] Using advanced WHERE SQL statement?
604 3007 Feb 14, 2005, 1:48 PM
Thread Re: [TheStone] Using advanced WHERE SQL statement?
LanceWilson2 2994 Feb 20, 2005, 9:16 PM
Post Re: [LanceWilson2] Using advanced WHERE SQL statement?
LanceWilson2 2975 Mar 2, 2005, 10:19 AM