Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172

    Unanswered: Totals messed up with multiple queries

    Hi there,

    I'm working on a report that gets the data from two different queries. Here's what I get when I click on "Show SQL Query"

    Code:
     SELECT SOP10200.ITEMNMBR, SOP10100.CUSTNAME, SOP10100.STATE, SOP10100.COUNTRY, SOP10200.CMPNTSEQ, SOP10100.SOPNUMBE, SOP10100.DOCDATE, SOP10200.QUANTITY, SOP10200.OXTNDPRC, RM00102.ADDRESS1, RM00102.ADDRESS2, RM00102.ADDRESS3, RM00102.CITY, RM00102.STATE, RM00102.ZIP, RM00102.COUNTRY, SOP10100.CURNCYID, IV00101.ITMGEDSC, SOP10106.USERDEF2, RM00101.CUSTCLAS, ML3_Milgram.Douane, RM00102.PHONE1
     FROM   (((((m0851System.dbo.SOP10100 SOP10100 INNER JOIN m0851System.dbo.RM00101 RM00101 ON SOP10100.CUSTNMBR=RM00101.CUSTNMBR) LEFT OUTER JOIN m0851System.dbo.SOP10200 SOP10200 ON (SOP10100.SOPTYPE=SOP10200.SOPTYPE) AND (SOP10100.SOPNUMBE=SOP10200.SOPNUMBE)) LEFT OUTER JOIN m0851System.dbo.RM00102 RM00102 ON (SOP10100.CUSTNMBR=RM00102.CUSTNMBR) AND (SOP10100.PRBTADCD=RM00102.ADRSCODE)) LEFT OUTER JOIN m0851System.dbo.SOP10106 SOP10106 ON (SOP10100.SOPTYPE=SOP10106.SOPTYPE) AND (SOP10100.SOPNUMBE=SOP10106.SOPNUMBE)) LEFT OUTER JOIN m0851System.dbo.IV00101 IV00101 ON SOP10200.ITEMNMBR=IV00101.ITEMNMBR) INNER JOIN m0851System.dbo.ML3_Milgram ML3_Milgram ON IV00101.ITEMNMBR=ML3_Milgram.ITEMNMBR
     WHERE  (SOP10100.SOPNUMBE='IRMS00628' OR SOP10100.SOPNUMBE='IRMS00629' OR SOP10100.SOPNUMBE='IRMS00630')
     ORDER BY SOP10100.SOPNUMBE
    
    
     SELECT uv_Milgram_Header.ORTDISAM, uv_Milgram_Header.USERDEF1, uv_Milgram_Header.ORDOCAMT, uv_Milgram_Header.SOPNUMBE, uv_Milgram_Header.ORFRTAMT
     FROM   m0851System.dbo.uv_Milgram_Header uv_Milgram_Header
     WHERE  (uv_Milgram_Header.SOPNUMBE='IRMS00628' OR uv_Milgram_Header.SOPNUMBE='IRMS00629' OR uv_Milgram_Header.SOPNUMBE='IRMS00630')
    So as you can see I have two different SELECT statements. The first SELECT stands for the data lines (body)
    The second one stands for the total (page header/footer).

    For this example let's say I chose only one SOPNUMBE.

    If I create a formula field for this database field : v_Milgram_Header.ORDOCAMT
    that goes like this : SUM({v_Milgram_Header.ORDOCAMT})
    And let's say that I have 3 rows returned from the 1st query. It's gonna give me the equivalent of this : {v_Milgram_Header.ORDOCAMT}*3

    But when I roll my two queries in T-SQL the second one returns only one row.

    So ... WHAT'S HAPPENING HERE !?!?!

    HELP! I NEED SOMEBODY, HELP! .... :P

    Regards,

    R-TH
    Last edited by ortho; 02-27-08 at 10:25.
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  2. #2
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    My problem is fixed now... I used a subreport and it worked...
    Less is more.
    How long is now?
    http://www.lesouterrain.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •