Is this possible, using an old foxpro database files and oledb for DBase
-
I have this report for sales rep margin in Account Mate for DOS, that uses FoxPro for database table file, .dbx The item tables or DB files does not have a column for sales rep and order dates, so I can't go to the table directly to get my data. So without knowing or even where to start to write this in pure SQL, I wrote 2 functions 1. Get Invoices that match the sales rep and start and stop dates, and store it in a List(of invoices) 2. loop a function that targets items by invoice number, and get the cost, price, qty, etc. When you have 500 invoices, it takes forever to run, slowing down fixing my other problems, which may be fixed now. Do you think it's possible to merge these statements into a single statement. Plus if so, a nudge in the right direction. I hate this old Fox Pro stuff, it was before my time.
SELECT
h.FINVNO
, h.FSHIPDATE
FROM ARINV01H.dbf h
WHERE
h.FSALESPN = @FSALESPN
AND
h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate
UNION ALL
SELECT
v.FINVNO
, v.FSHIPDATE
FROM ARINV01.dbf v
WHERE
v.FSALESPN = @FSALESPN
AND
v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDateSELECT
DISTINCT FITEMNO
, SUM(FSHIPQTY)
, SUM(FCOST * FSHIPQTY)
, SUM(FPRICE * FSHIPQTY)
, (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = h.FITEMNO) AS FREALDESC
FROM
(
SELECT
h.FITEMNO
, h.FSHIPQTY
, h.FCOST
, h.FPRICE
FROM ARTRS01H.dbf h
WHERE
h.FINVNO = @FINVNO
UNION
SELECT
v.FITEMNO
, v.FSHIPQTY
, v.FCOST
, v.FPRICE
FROM ARTRS01.dbf v
WHERE
v.FINVNO = @FINVNO
)
GROUP BY FITEMNO " -
I have this report for sales rep margin in Account Mate for DOS, that uses FoxPro for database table file, .dbx The item tables or DB files does not have a column for sales rep and order dates, so I can't go to the table directly to get my data. So without knowing or even where to start to write this in pure SQL, I wrote 2 functions 1. Get Invoices that match the sales rep and start and stop dates, and store it in a List(of invoices) 2. loop a function that targets items by invoice number, and get the cost, price, qty, etc. When you have 500 invoices, it takes forever to run, slowing down fixing my other problems, which may be fixed now. Do you think it's possible to merge these statements into a single statement. Plus if so, a nudge in the right direction. I hate this old Fox Pro stuff, it was before my time.
SELECT
h.FINVNO
, h.FSHIPDATE
FROM ARINV01H.dbf h
WHERE
h.FSALESPN = @FSALESPN
AND
h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate
UNION ALL
SELECT
v.FINVNO
, v.FSHIPDATE
FROM ARINV01.dbf v
WHERE
v.FSALESPN = @FSALESPN
AND
v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDateSELECT
DISTINCT FITEMNO
, SUM(FSHIPQTY)
, SUM(FCOST * FSHIPQTY)
, SUM(FPRICE * FSHIPQTY)
, (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = h.FITEMNO) AS FREALDESC
FROM
(
SELECT
h.FITEMNO
, h.FSHIPQTY
, h.FCOST
, h.FPRICE
FROM ARTRS01H.dbf h
WHERE
h.FINVNO = @FINVNO
UNION
SELECT
v.FITEMNO
, v.FSHIPQTY
, v.FCOST
, v.FPRICE
FROM ARTRS01.dbf v
WHERE
v.FINVNO = @FINVNO
)
GROUP BY FITEMNO "Sorry I can't help with the query but I'd assume they are not still using Account Mate? So why not suck all the data into a sql server database and use that for reporting.
Never underestimate the power of human stupidity RAH
-
Sorry I can't help with the query but I'd assume they are not still using Account Mate? So why not suck all the data into a sql server database and use that for reporting.
Never underestimate the power of human stupidity RAH
The AccountMate for DOS works. The sales rep for them in Dallas sort of dicked them around with the price to upgrade, and raised the price 15K after he paid a visit, so they decided to just stick it to them. So I made an overlay program for them that performs most of the daily functions for the front office. Hey its income for me, they pay fast.