asked on

update amount field in a table with sum of amount fields from a linked server.

I created the Following table and inserted the following DISTINCT records (There are multiple line items for each invoice but I only wanted one record in the table I created) from a linked JDE database in SQL Server, which works fine:

CREATE TABLE [sysop].TMP_APAudit (
      BatchNo varchar (25),
      BatchDate datetime,
      InvNo varchar (25),
      InvDate datetime,
      DocType varchar (2),
      InvAmt varchar (14),
      BranchPlant varchar (20),
      SupplierNo varchar (25),
      )

INSERT INTO TMP_APAudit
SELECT a.RPICU, a.RPDICJ, a.RPVINV, a.RPDIVJ, a.RPDCT, '', a.RPMCU,a.RPAN8 FROM
OPENQUERY(MMM_JDEVIEW, 'Select DISTINCT RPICU, RPDICJ, RPVINV, RPDIVJ, RPDCT,
RPMCU,RPAN8 FROM TESTDTA.Docimag4 ') a
LEFT OUTER JOIN ae_dt3 b ON RTRIM(LTRIM(a.RPICU)) = RTRIM(LTRIM(b.field5))
AND RTRIM(LTRIM(a.RPVINV)) = RTRIM(LTRIM(b.field9))
AND RTRIM(LTRIM(a.RPAn8)) = RTRIM(LTRIM(b.field2))
WHERE b.field5 is NULL AND a.RPDCT IN(SELECT DocTypeID FROM APAudit_DocTypes)
AND RPDICJ >= (SELECT Startdate FROM APAudit_StartDate)

How do I update the InvAmt field in the table I created with the sum of the RPAC field in the docimag4 table where the RPICU = BatchNo and RPVINV = InvNo And RPAn8 = SupplierNo

Thanks in Advance for you help.

dqmq

Seems like you might be able to do it with one OPENQUERY, but I don't know your data well enough to advance that solution. Try this:

INSERT INTO TMP_APAudit
SELECT a.RPICU, a.RPDICJ, a.RPVINV, a.RPDIVJ, a.RPDCT, b.total, a.RPMCU,a.RPAN8 FROM
OPENQUERY(MMM_JDEVIEW, 'Select DISTINCT RPICU, RPDICJ, RPVINV, RPDIVJ, RPDCT,
RPMCU,RPAN8 FROM TESTDTA.Docimag4 ') a
LEFT OUTER JOIN ae_dt3 b ON RTRIM(LTRIM(a.RPICU)) = RTRIM(LTRIM(b.field5))
AND RTRIM(LTRIM(a.RPVINV)) = RTRIM(LTRIM(b.field9))
AND RTRIM(LTRIM(a.RPAn8)) = RTRIM(LTRIM(b.field2))
--this is what I added
LEFT OUTER JOIN
OPENQUERY(MMM_JDEVIEW, 'Select RPICU, RPVINV, RPAN8, SUM(RPAC) total FROM TESTDTA.Docimag4 group by RPICU, RPVINV, RPAN8') b
on a.RPICU=b.RPICU and a.RPVINV=b.RPVINV and a.RPAN8=b.RPAN8
--
WHERE b.field5 is NULL AND a.RPDCT IN(SELECT DocTypeID FROM APAudit_DocTypes)
AND RPDICJ >= (SELECT Startdate FROM APAudit_StartDate)

billkrieger

ASKER

The statement is running now, but takes a long time. It now has doubled with the 2nd open query.

How would you do it with one open query. I played around with it but, kept getting an error on the group by.

Also, is it possible to put the where clauses inside of the open query to reduce the # of transactions returned.

ASKER CERTIFIED SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial