Mike Mirabelli - 1 year ago 59

SQL Question

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 Source

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
```