Flexible SQL Select
%UNIPLEX
%TO ilist
%FROM gjones
%SYSTEM DHLNET
%SUBJECT Flexible SQL Select
%VERIFY y
%DATE 18/10/95 10:36
%REFERENCE 325319
Perhaps you could try something in terms of selecting the table
multiple times and aliasing it :-
select unique(a.card_no)\\011from pt_code a, pt_code b
\\011where a.card_no = b.card_no and
\\011\\011a.diag_cod = "303.500" and
\\011\\011b.diag_cod = "621.100"
Might be worth a try.
If you want something a bit more generic how about loading
the required diag_cod's into an array and then :-
declare a_cursor for
\\011select unique(card_no)
\\011\\011from pt_code
\\011\\011where diag_cod = search_array[1].diag_code
foreach a_cursor into l_card_no
\\011insert into temp_table values ( l_card_no )
end foreach
let i = 2
while length(search_array[i].diag_code)
\\011delete from temp_table
\\011\\011where card_no not in
\\011\\011\\011( select card_no from pt_code
\\011\\011\\011\\011where diag_cod = search_array[i].diag_code )
end while
You should end up with a list of card numbers required in the
temp table (I havn't tested it but it should work).
Hope this helps ...
Greg
>Cc: informix-list@rmy.emory.edu
>X-Informix-List-To: dhlinfmx@dhl.com
>X-Informix-List-Id: <list.7683>
>John,
>
> I, too, was thinking of your "having count..." approach. The problem
>though is that in the example table a patient might have the same
>diagnosis code more than once. So, your SQL would also find a patient
>which has 3 or more "303.500" codes but no "339.000" or "621.100"
>code. Can you think of a way to avoid this problem?
>
> --Craig (craig@chemistry.utah.edu)
>
>>
>> Or, as another way of doing the same job:
>>
>> SELECT card_no
>> FROM pt_code
>> WHERE diag_code IN ("303.500", "339.000", "621.100")
>> GROUP BY card_no
>> HAVING COUNT(*) = 3;>>
>
%UEND