Mike Mirabelli Mike Mirabelli - 3 months ago 9
SQL Question

Summing two seperate queries into one value using UNION ALL clause

I have the following query (as part of a larger query). I am trying to get the sum results from 2 different data sets within a subquery but I am having trouble trying to encapsulate the two into 1 value. What I have is this:

(Select SUM('Invoiced MTD') from

((Select SUM(CASE WHEN SOH.LASDLVNUM_0 <> '' AND SOH.LASINVNUM_0 <> '' AND MONTH(SOH.SHIDAT_0) = MONTH(GETDATE()) THEN
(SOP.NETPRI_0 * SOQ.QTY_0 * SOH.CHGRAT_0) ELSE 0 END) as 'Invoiced MTD'
From x3v6.CICPROD.SORDER SOH
LEFT OUTER JOIN x3v6.CICPROD.BPCUSTOMER BPC on SOH.BPCORD_0 = BPC.BPCNUM_0
LEFT OUTER JOIN x3v6.CICPROD.SORDERQ SOQ on SOH.SOHNUM_0 = SOQ.SOHNUM_0
LEFT OUTER JOIN x3v6.CICPROD.SORDERP SOP on SOQ.SOHNUM_0 = SOP.SOHNUM_0 and SOQ.SOPLIN_0 = SOP.SOPLIN_0 and SOQ.SOQSEQ_0 = SOP.SOPSEQ_0
LEFT OUTER JOIN x3v6.CICPROD.ITMMASTER ITM on SOP.ITMREF_0 = ITM.ITMREF_0 ))

UNION ALL

((Select SUM(CASE WHEN SIH.INVTYP_0 = 2 and MONTH(SIH.ACCDAT_0) = MONTH(GETDATE()) THEN SID.AMTNOTLIN_0 * (-1) ELSE 0 END) as 'Invoiced MTD'
From x3v6.CICPROD.SINVOICE SIH
Left Outer Join x3v6.CICPROD.SINVOICED SID on SIH.NUM_0 = SID.NUM_0))

as 'T2',


But I am getting an error where the UNION ALL clauses is, and I can't figure it out. Basically I want to combine Sales credit memos with the sales order dollar totals from a seperate table.

Can anyone assist me with this?

Answer

What about this ?

Select SUM([Invoiced MTD]) from
(  
  Select SUM(CASE WHEN SOH.LASDLVNUM_0 <> '' AND SOH.LASINVNUM_0 <> '' AND MONTH(SOH.SHIDAT_0) = MONTH(GETDATE()) 
                   THEN (SOP.NETPRI_0 * SOQ.QTY_0 * SOH.CHGRAT_0) ELSE 0 END) as 'Invoiced MTD' 
   From x3v6.CICPROD.SORDER SOH
   LEFT OUTER JOIN x3v6.CICPROD.BPCUSTOMER BPC on SOH.BPCORD_0 = BPC.BPCNUM_0
   LEFT OUTER JOIN x3v6.CICPROD.SORDERQ SOQ on SOH.SOHNUM_0 = SOQ.SOHNUM_0
   LEFT OUTER JOIN x3v6.CICPROD.SORDERP SOP on SOQ.SOHNUM_0 = SOP.SOHNUM_0 and SOQ.SOPLIN_0 = SOP.SOPLIN_0 and SOQ.SOQSEQ_0 = SOP.SOPSEQ_0
   LEFT OUTER JOIN x3v6.CICPROD.ITMMASTER ITM on SOP.ITMREF_0 = ITM.ITMREF_0 

UNION ALL
   Select SUM(CASE WHEN SIH.INVTYP_0 = 2 and MONTH(SIH.ACCDAT_0) = MONTH(GETDATE()) 
                THEN SID.AMTNOTLIN_0 * (-1) ELSE 0 END) as 'Invoiced MTD'
   From x3v6.CICPROD.SINVOICE SIH 
   Left Outer Join x3v6.CICPROD.SINVOICED SID on SIH.NUM_0 = SID.NUM_0
)T