Thank you! I had the same idea this night - and it works!
Joerg Arand MD assistant medical director Neonatology Children's Hospital University Tuebingen Calwer Str. 7 D-72076 Tuebingen Tel.+49-(0)7071-2982211 Tel.+49-(0)7071-2980895 (office) E-mail:Joerg.Arand(AT)med.uni-tuebingen.de >>> Dave Crozier <[email protected]> 15.11.2011 10:33 >>> Can't you make the query simpler by actually joining the nddiagnosen table in the main body as follows: SELECT DISTINCT ; ndFall0.fall_id, ; ndFall0.pat_id ; FROM ndFall0 ; JOIN ndpat0 ON ndPat0.pat_id=ndFall0.pat_id ; JOIN ndfall1 ON ndfall1.fall_id=Ndfall0.fall_id ; join nddiagnosen dg on dg.fall_id=ndFall0.fall_id ; INTO CURSOR (tmp_dat) READWRITE ; WHERE dg.loesch=0 ; and dg.base_id IN (NdFall1.opdgn1,NdFall1.opdgn2,NdFall1.opdgn3,NdFall1.opdgn4,NdFall1.opdgn5) ; AND (dg.dgnnr1='Q41' OR dg.dgnnr2='Q41') Dave C -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Joerg Arand Sent: 14 November 2011 21:12 To: [email protected] Subject: SQL-Qestion Error 1801 I want to extract patients with an specific Diagnosis code as operation diagnosis (in this case Q41) Diagnoses of the patient are in table nddiagnosen (base_id is the key-field) In table ndfall2 are stored the base_id's of diagnoses for Surgery (maximal 5 ) In the SQL-Query SELECT DISTINCT ndFall0.fall_id,ndFall0.pat_id ; FROM ndFall0 ; JOIN ndpat0 ON ndPat0.pat_id=ndFall0.pat_id ; JOIN ndfall1 ON ndfall1.fall_id=Ndfall0.fall_id ; INTO CURSOR (tmp_dat) READWRITE ; WHERE ((EXISTS (SELECT * FROM nddiagnosen dg WHERE dg.fall_id=ndFall0.fall_id and dg.loesch=0 and ; dg.base_id IN (NdFall1.opdgn1,NdFall1.opdgn2,NdFall1.opdgn3,NdFall1.opdgn4,NdFall1.opdgn5) AND (dg.dgnnr1='Q41' OR dg.dgnnr2='Q41')))) I get the orror 1801 (SQL: Error correlating fields.) The problem is in the part dg.base_id IN (NdFall1.opdgn1,NdFall1.opdgn2,NdFall1.opdgn3,NdFall1.opdgn4,NdFall1.opdgn5) any sugestions?? Joerg Arand MD assistant medical director Neonatology Children's Hospital University Tuebingen Calwer Str. 7 D-72076 Tuebingen Tel.+49-(0)7071-2982211 Tel.+49-(0)7071-2980895 (office) E-mail:Joerg.Arand(AT)med.uni-tuebingen.de --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
