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:
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)
# 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!
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!
Using advanced WHERE SQL statement?