Hi everyone,
I'm having real problems using the UNION clause as I keep getting the error 'Msg 205, Level 16, State 1, Line 1. All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.'
I seem to have an equal number of expressions, so I can't see what I'm doing wrong. Any help would be much appreciated.
Thank you
Jon
SELECT [SERIALNUMBER],[FIRSTNAME],[SURNAME],[COUNTRY],
(CASE WHEN Row=100000 THEN SPACE(8) ELSE convert(varchar(8),[PAYMENTDATE],3) END) [PAYMENTDATE],
[PAYMENTTYPE], [AMOUNT],[GIFTAID],[TOTAL],[DESTINATION],[MAILINGSEGMENT], [CLASSOF]
FROM
(
select bd.Serialnumber [SERIALNUMBER], co.firstname [FIRSTNAME],co.keyname [SURNAME],co.country [COUNTRY], ed.CLASSOF[CLASSOF], com.NOTES[MAILINGSEGMENT], bd.DATEOFPAYMENT [PAYMENTDATE],
bd.Paymentamount [Amount],
dc.DESTINATIONNOTES [DESTINATION],
CASE WHEN (gd.GADstatus) = 'Active' AND (bd.taxclaimable) = 'Yes' THEN bd.paymentamount * (0.25)
- bd.paymentamount ELSE '0' END AS GIFTAID, v.GIFTAID + bd.PAYMENTAMOUNT[TOTAL],
bd.PaymentType [PAYMENTTYPE],row_number() over (order by bd.DATEOFPAYMENT desc) Row
from batchdetail bd
left join contact co
on bd.SERIALNUMBER=co.SERIALNUMBER
left join
DESTINATIONCODE dc
on bd.DESTINATIONCODE=dc.DESTINATIONCODE
left join GIFTAID_CURRENTDECLARATION gd
on bd.SERIALNUMBER=gd.serialnumber
CROSS APPLY(VALUES (CASE WHEN gd.GADstatus = 'Active' AND bd.taxclaimable = 'Yes' THEN bd.paymentamount * (0.25)
ELSE '0' END)) v(GIFTAID)
left join dbo.EDUCATION AS ED on bd.SERIALNUMBER = ed.SERIALNUMBER
left join COMMUNICATION com ON co.SERIALNUMBER = com.SERIALNUMBER
where bd.ADMITNAME in(select ADMITNAME from OBJECT where STAGEID=30010)
and bd.REVERSAL<>1 and bd.REVERSAL<>-1
and bd.DATEOFPAYMENT >= '01-01-2020'
and bd.incometype in('DONATION','LEGACY')
and ed.INSTITUTIONTYPE='Key'
and com.SUBJECT = 'mailing'
and dc.destinationtype LIKE 'Black%'
and bd.sourcecode IN ('APP00147', 'APP00149', 'APP00150', 'APP00151', 'APP00152', 'APP00153', 'APP00154', 'APP00155', 'APP00156', 'APP00157', 'APP00158', 'APP00159', 'APP00160', 'APP00161', 'APP00162', 'APP00163',
'APP00164', 'APP00165', 'APP00167', 'APP00168', 'APP00169', 'APP00170', 'APP00172') and bd.PAYMENTTYPE NOT IN ('Direct Debit','Standing Order')
UNION
select 'TOTALS' serialnumber,' ' firstname,' ' keyname,' ' country,' ' classof,' ' giftaid, ' ' mailingsegment,' '[Payment Date], SUM(bd.Paymentamount) Amount,
'' total,' ' destinationnotes,' , 'paymenttype,' ' ,100000 Row
from batchdetail bd
left join contact co
on bd.SERIALNUMBER=co.SERIALNUMBER
left join DESTINATIONCODE dc
on bd.DESTINATIONCODE=dc.DESTINATIONCODE
left join GIFTAID_CURRENTDECLARATION gd
on bd.SERIALNUMBER=gd.serialnumber
CROSS APPLY(VALUES (CASE WHEN gd.GADstatus = 'Active' AND bd.taxclaimable = 'Yes' THEN bd.paymentamount * (0.25)
ELSE '0' END)) v(GIFTAID)
left join dbo.EDUCATION AS ED on bd.SERIALNUMBER = ed.SERIALNUMBER
left join COMMUNICATION com ON co.SERIALNUMBER = com.SERIALNUMBER
where bd.ADMITNAME in(select ADMITNAME from OBJECT where STAGEID=30010)
and bd.REVERSAL<>1 and bd.REVERSAL<>-1
and bd.DATEOFPAYMENT >= '01-01-2020'
and bd.incometype in('DONATION','LEGACY')
and ed.INSTITUTIONTYPE='Key'
and com.SUBJECT = 'mailing'
and dc.destinationtype LIKE 'Black%'
and bd.sourcecode IN ('APP00147', 'APP00149', 'APP00150', 'APP00151', 'APP00152', 'APP00153', 'APP00154', 'APP00155', 'APP00156', 'APP00157', 'APP00158', 'APP00159', 'APP00160', 'APP00161', 'APP00162', 'APP00163',
'APP00164', 'APP00165', 'APP00167', 'APP00168', 'APP00169', 'APP00170', 'APP00172') and bd.PAYMENTTYPE NOT IN ('Direct Debit','Standing Order')
)dtot
ORDER BY dtot.row