I'm new to Informix, (mostly SQL Server), and have been tasked with converting some existing Informix queries to SQL Server. Here's one Informix query I'm trying to convert:
SELECT DISTINCT int.lab_no, int.v_id, int.c_code,
v_ref.lastname, vet.state, int.owner_id, int.in_date,
int.spec, int.subspec, hist.diag, acc_chg.test_no
FROM int, vet, v_ref, OUTER hist, acc_chg
WHERE acc_chg.test_no IN (125,199)
AND hist.add_date =
(SELECT MIN(add_date) FROM hist WHERE int.lab_no = hist.lab_no)
AND int.spec LIKE 'CRC'
AND int.v_id = v_ref.v_id
AND int.c_code = vet.c_code
AND int.lab_no = hist.lab_no
AND int.lab_no = acc_chg.lab_no
AND int.in_date >= '7/1/2006'
AND int.in_date <= '9/1/2006'
1) What exactly is the OUTER statement doing? Is v_ref the dominant table, and hist the the servant? Or are int, vet, and v_ref the dominant tables and hist the servant? And is acc_chg involved in the join? (it doesn't seem to be). I'm just trying to conceptualize what the FROM statement is doing.
2) The query works as it's written - almost. The one glitch is that if there are identical entries in every field except diag, I get repeat rows. I just want distinct lab_no's, and the info associated with them, which I thought the DISTINCT would do for me (but it doesn't). I think I can solve this with TOP in SQL Server, but I can't figure out how that might relate (if at all) to FIRST in Ifx.