• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

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.
                               





            
            
            
            
             
                  
0
billkrieger
Asked:
billkrieger
  • 2
1 Solution
 
dqmqCommented:
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)
0
 
billkriegerAuthor Commented:
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.
0
 
dqmqCommented:
>Also, is it possible to put the where clauses inside of the open query to reduce the # of transactions returned.

You certainly can put where clauses inside the openquery and it quite possibly will improve performance a lot.  Do understand that it may also change the results of the query.  I trust you would test to make sure you are getting valid results.

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

Well it would be a group by query instead of a distinct query.  Here's the rub.  The group by query (that I added) only returns 4 columns: the total and the three columns it is summarized over.  But you need 4 other columns, right?  Add those four columns to the select list AND to the group by list.  Now the query will run.  If you still get the correct answer, then you are OK.

If not, it means the each row of the distinct query represents a different subset of detail rows than you are totaling in the group by query.  In other words,  summing the total column does not produce the same grand total as summing the detail rows.  If that's what you intend, then it requires two openqueries.  

dQm
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now