Combine results from 2 foxpro DBF files using union
-
On this query, I get 2 sets of results for each part number, which is probably what I asked for. I'm trying to get 1 set of results for each part number, I just need to figure out the proper method. I'll try some critical thinking here, it can't be the group by clause, so perhaps I'm suppose to wrap the union in a select statement like my count in the post below? But then I have the distinct, and I don't want to mess that up. Once I have this done, I think I'm done writing for these old account mate dos programs.
Dim queryString As String = _
" SELECT " & _
" DISTINCT h.FITEMNO " & _
", SUM(h.FSHIPQTY) " & _
", AVG(h.FCOST) " & _
", AVG(h.FPRICE) " & _
", SUM(h.FSHIPQTY * h.FPRICE) " & _
", (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = h.FITEMNO) AS FREALDESC " & _
", SUM(h.FSHIPQTY * h.FPRICE - h.FSHIPQTY * h.FCOST)" & _
" FROM ARTRS01H.dbf h " & _
" WHERE " & _
" h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate " & _
" GROUP BY h.FITEMNO "queryString +=
" UNION ALL "queryString +=
" SELECT " & _
" DISTINCT v.FITEMNO " & _
", SUM(v.FSHIPQTY) " & _
", AVG(v.FCOST) " & _
", AVG(v.FPRICE) " & _
", SUM(v.FSHIPQTY * v.FPRICE) " & _
", (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = v.FITEMNO) AS FREALDESC " & _
", SUM(v.FSHIPQTY * v.FPRICE - v.FSHIPQTY * v.FCOST)" & _
" FROM ARTRS01.dbf v " & _
" WHERE " & _
" v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate " & _
" GROUP BY v.FITEMNO " -
On this query, I get 2 sets of results for each part number, which is probably what I asked for. I'm trying to get 1 set of results for each part number, I just need to figure out the proper method. I'll try some critical thinking here, it can't be the group by clause, so perhaps I'm suppose to wrap the union in a select statement like my count in the post below? But then I have the distinct, and I don't want to mess that up. Once I have this done, I think I'm done writing for these old account mate dos programs.
Dim queryString As String = _
" SELECT " & _
" DISTINCT h.FITEMNO " & _
", SUM(h.FSHIPQTY) " & _
", AVG(h.FCOST) " & _
", AVG(h.FPRICE) " & _
", SUM(h.FSHIPQTY * h.FPRICE) " & _
", (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = h.FITEMNO) AS FREALDESC " & _
", SUM(h.FSHIPQTY * h.FPRICE - h.FSHIPQTY * h.FCOST)" & _
" FROM ARTRS01H.dbf h " & _
" WHERE " & _
" h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate " & _
" GROUP BY h.FITEMNO "queryString +=
" UNION ALL "queryString +=
" SELECT " & _
" DISTINCT v.FITEMNO " & _
", SUM(v.FSHIPQTY) " & _
", AVG(v.FCOST) " & _
", AVG(v.FPRICE) " & _
", SUM(v.FSHIPQTY * v.FPRICE) " & _
", (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = v.FITEMNO) AS FREALDESC " & _
", SUM(v.FSHIPQTY * v.FPRICE - v.FSHIPQTY * v.FCOST)" & _
" FROM ARTRS01.dbf v " & _
" WHERE " & _
" v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate " & _
" GROUP BY v.FITEMNO "I think got it. The numbers look correct on a couple of test items. I'm shocked that it works, doesn't really make sense to me how the inner select lines up with the main select.
queryString +=
" SELECT " & _
" DISTINCT FITEMNO " & _
", SUM(FSHIPQTY) " & _
", AVG(FCOST) " & _
", AVG(FPRICE) " & _
", SUM(FSHIPQTY * FPRICE) " & _
", (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = h.FITEMNO) AS FREALDESC " & _
", SUM(FSHIPQTY * FPRICE - FSHIPQTY * FCOST)" & _
" FROM " & _
"(" & _
" SELECT " & _
" h.FITEMNO " & _
" , h.FSHIPQTY " & _
" , h.FCOST " & _
" , h.FPRICE " & _
" FROM ARTRS01H.dbf h " & _
" WHERE " & _
" h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate " & _
" UNION ALL " & _
" SELECT " & _
" v.FITEMNO " & _
" , v.FSHIPQTY " & _
" , v.FCOST " & _
" , v.FPRICE " & _
" FROM ARTRS01.dbf v " & _
" WHERE " & _
" v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate " & _
")" & _
" GROUP BY FITEMNO "