Jet OLE, multiple joins with a union, missing an operator or brackets
-
So I'm back to that Foxpro database again in a VB app, and I thought I'd try and get even more efficient in loading data. I'm trying to join a billing and address dbf file into a union. I think it will work, but I get an error saying that I'm missing an operator, in which I think I have the brackets in the wrong format. The single join works, using the left join and right join on the union, it's when I added the 2nd join it failed. Not much on this out there when searching. Possible to take a look at and perhaps reflect back 18 years on this?
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & m_path & "; Extended Properties=dBASE IV"
Const queryString As String = _
"SELECT " & _
" h.FINVNO " & _
", h.FSONO " & _
", h.FCUSTNO " & _
", h.FCOMPANY " & _
", h.FSALESPN " & _
", h.FSHIPDATE " & _
", h.FPONO " & _
", h.FCSAMT " & _
", h.FTAXAMT1 " & _
", h.FBADDRCD " & _
", h.FSADDRCD " & _
", hbA.FCOMPANY " & _
", hbA.FADDR1 " & _
", hbA.FADDR2 " & _
", hbA.FCITY " & _
", hbA.FSTATE " & _
", hbA.FZIP " & _
" FROM ARINV01H.dbf h " & _
" LEFT JOIN ARADD01H.dbf hbA ON (h.FINVNO = hbA.FINVNO) " & _
" LEFT JOIN ARCUS01.dbf hsA ON (h.FCUSTNO = hsA.FCUSTNO) " & _
" WHERE " & _
" h.FSALESPN = @FSALESPN " & _
" AND " & _
" h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate " & _
"UNION ALL " & _
"SELECT " & _
" v.FINVNO " & _
", v.FSONO " & _
", v.FCUSTNO " & _
", v.FCOMPANY " & _
", v.FSALESPN " & _
", v.FSHIPDATE " & _
", v.FPONO " & _
", v.FCSAMT " & _
", v.FTAXAMT1 " & _
", v.FBADDRCD " & _
", v.FSADDRCD " & _
", vbA.FCOMPANY " & _
", vbA.FADDR1 " & _
", vbA.FADDR2 " & _
", vbA.FCITY " & _
", vbA.FSTATE " & _
", vbA.FZIP " & _
" FROM ARINV01.dbf v " & _
" RIGHT JOIN ARADD01.dbf vbA ON (v.FINVNO = vbA.FINVNO) " & _
" RIGHT JOIN ARCUS01.dbf vsA ON (v.FCUSTNO = vsA.FCUSTNO) " & _
" WHERE " & _
" v.FSALESPN = @FSALESPN " & _
" AND " & _
" v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate "21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
-
So I'm back to that Foxpro database again in a VB app, and I thought I'd try and get even more efficient in loading data. I'm trying to join a billing and address dbf file into a union. I think it will work, but I get an error saying that I'm missing an operator, in which I think I have the brackets in the wrong format. The single join works, using the left join and right join on the union, it's when I added the 2nd join it failed. Not much on this out there when searching. Possible to take a look at and perhaps reflect back 18 years on this?
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & m_path & "; Extended Properties=dBASE IV"
Const queryString As String = _
"SELECT " & _
" h.FINVNO " & _
", h.FSONO " & _
", h.FCUSTNO " & _
", h.FCOMPANY " & _
", h.FSALESPN " & _
", h.FSHIPDATE " & _
", h.FPONO " & _
", h.FCSAMT " & _
", h.FTAXAMT1 " & _
", h.FBADDRCD " & _
", h.FSADDRCD " & _
", hbA.FCOMPANY " & _
", hbA.FADDR1 " & _
", hbA.FADDR2 " & _
", hbA.FCITY " & _
", hbA.FSTATE " & _
", hbA.FZIP " & _
" FROM ARINV01H.dbf h " & _
" LEFT JOIN ARADD01H.dbf hbA ON (h.FINVNO = hbA.FINVNO) " & _
" LEFT JOIN ARCUS01.dbf hsA ON (h.FCUSTNO = hsA.FCUSTNO) " & _
" WHERE " & _
" h.FSALESPN = @FSALESPN " & _
" AND " & _
" h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate " & _
"UNION ALL " & _
"SELECT " & _
" v.FINVNO " & _
", v.FSONO " & _
", v.FCUSTNO " & _
", v.FCOMPANY " & _
", v.FSALESPN " & _
", v.FSHIPDATE " & _
", v.FPONO " & _
", v.FCSAMT " & _
", v.FTAXAMT1 " & _
", v.FBADDRCD " & _
", v.FSADDRCD " & _
", vbA.FCOMPANY " & _
", vbA.FADDR1 " & _
", vbA.FADDR2 " & _
", vbA.FCITY " & _
", vbA.FSTATE " & _
", vbA.FZIP " & _
" FROM ARINV01.dbf v " & _
" RIGHT JOIN ARADD01.dbf vbA ON (v.FINVNO = vbA.FINVNO) " & _
" RIGHT JOIN ARCUS01.dbf vsA ON (v.FCUSTNO = vsA.FCUSTNO) " & _
" WHERE " & _
" v.FSALESPN = @FSALESPN " & _
" AND " & _
" v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate "21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
jkirkerx wrote:
The single join works, using the left join and right join on the union, it's when I added the 2nd join it failed.
What does that mean? Did it throw an error? If yes, which, if no, what was the difference between the expected and the actual result?
jkirkerx wrote:
I'm back to that Foxpro database again in a VB app
There's the problem :thumbsup:
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
-
jkirkerx wrote:
The single join works, using the left join and right join on the union, it's when I added the 2nd join it failed.
What does that mean? Did it throw an error? If yes, which, if no, what was the difference between the expected and the actual result?
jkirkerx wrote:
I'm back to that Foxpro database again in a VB app
There's the problem :thumbsup:
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)