Old school dbase II / clipper database, multiple left joins
-
I've never worked on these old dbase/clipper database files for Account Mate DOS, and I'm having trouble with multiple joins. Says I'm missing a arithmetic operator in the statement. I can't find a reference to study to get an idea of how it should be written. I really don't want to make 2 calls, because it really slows down the load time. This is the error message Syntax error (missing operator) in query expression '(c.FCUSTNO = a.FCUSTNO) LEFT JOIN ARCEM01.dbf e ON (c.FCUSTNO = e.FCUSTNO)'.
"SELECT c.FINVNO, c.FCUSTNO, c.FCOMPANY, c.FSALESPN, c.FSHIPVIA, c.FORDDATE, c.FSHIPDATE, c.FNTAXAMT, a.FADDR1 " & _
"FROM ARINV01.dbf c " & _
"LEFT JOIN ARCUS01.dbf a ON (c.FCUSTNO = a.FCUSTNO) " & _
"LEFT JOIN ARCEM01.dbf e ON (c.FCUSTNO = e.FCUSTNO) " & _
"WHERE c.FORDDATE=@startDate" -
I've never worked on these old dbase/clipper database files for Account Mate DOS, and I'm having trouble with multiple joins. Says I'm missing a arithmetic operator in the statement. I can't find a reference to study to get an idea of how it should be written. I really don't want to make 2 calls, because it really slows down the load time. This is the error message Syntax error (missing operator) in query expression '(c.FCUSTNO = a.FCUSTNO) LEFT JOIN ARCEM01.dbf e ON (c.FCUSTNO = e.FCUSTNO)'.
"SELECT c.FINVNO, c.FCUSTNO, c.FCOMPANY, c.FSALESPN, c.FSHIPVIA, c.FORDDATE, c.FSHIPDATE, c.FNTAXAMT, a.FADDR1 " & _
"FROM ARINV01.dbf c " & _
"LEFT JOIN ARCUS01.dbf a ON (c.FCUSTNO = a.FCUSTNO) " & _
"LEFT JOIN ARCEM01.dbf e ON (c.FCUSTNO = e.FCUSTNO) " & _
"WHERE c.FORDDATE=@startDate"If I remember correctly (which might not be the case) dbase only allows one outer join. But this doesn't seem to be a real problem as you're not using ARCEM01.dbf for anything. The solution otherwise would be to use subqueries in the select. Like this:
SELECT c.FINVNO
,c.FCUSTNO
,c.FCOMPANY
,c.FSALESPN
,c.FSHIPVIA
,c.FORDDATE
,c.FSHIPDATE
,c.FNTAXAMT
,(SELECT a.FADDR1 FROM ARCUS01.dbf a WHERE c.FCUSTNO = a.FCUSTNO) as FADDR1
FROM ARINV01.dbf c
WHERE c.FORDDATE=@startDateWrong is evil and must be defeated. - Jeff Ello[^]
-
If I remember correctly (which might not be the case) dbase only allows one outer join. But this doesn't seem to be a real problem as you're not using ARCEM01.dbf for anything. The solution otherwise would be to use subqueries in the select. Like this:
SELECT c.FINVNO
,c.FCUSTNO
,c.FCOMPANY
,c.FSALESPN
,c.FSHIPVIA
,c.FORDDATE
,c.FSHIPDATE
,c.FNTAXAMT
,(SELECT a.FADDR1 FROM ARCUS01.dbf a WHERE c.FCUSTNO = a.FCUSTNO) as FADDR1
FROM ARINV01.dbf c
WHERE c.FORDDATE=@startDateWrong is evil and must be defeated. - Jeff Ello[^]
-
If I remember correctly (which might not be the case) dbase only allows one outer join. But this doesn't seem to be a real problem as you're not using ARCEM01.dbf for anything. The solution otherwise would be to use subqueries in the select. Like this:
SELECT c.FINVNO
,c.FCUSTNO
,c.FCOMPANY
,c.FSALESPN
,c.FSHIPVIA
,c.FORDDATE
,c.FSHIPDATE
,c.FNTAXAMT
,(SELECT a.FADDR1 FROM ARCUS01.dbf a WHERE c.FCUSTNO = a.FCUSTNO) as FADDR1
FROM ARINV01.dbf c
WHERE c.FORDDATE=@startDateWrong is evil and must be defeated. - Jeff Ello[^]
That Works! Thanks Jorgen
SELECT c.FINVNO
,c.FCUSTNO
,c.FCOMPANY
,c.FSALESPN
,c.FSHIPVIA
,c.FORDDATE
,c.FSHIPDATE
,c.FNTAXAMT
,(SELECT a.FADDR1 FROM ARCUS01.dbf a WHERE c.FCUSTNO = a.FCUSTNO) AS FADDR1
,(SELECT e.FEMAIL1 FROM ARCEM01.dbf e WHERE c.FCUSTNO = e.FCUSTNO) AS FEMAIL1
FROM ARINV01.dbf c
WHERE c.FORDDATE=@startDate