Moving from UNION to ... ?

[firmusgrp]

Hello all and thanks for the help.

At the moment I have a SQL UNION within a CTE as follows (simplified for the thread) ...

WITH InvTrans AS 
(
	SELECT 		po.unitQty AS qtyIn, po.POLineID
	FROM      	dbo.tblPOLine AS po LEFT OUTER JOIN
				dbo.tblPOLedger AS poL ON po.POLedgerID = poL.POLedgerID
	WHERE     	(po.locationID = 10)
UNION ALL
	SELECT     	SUM(tr.qty) AS qtyIn, tr.POLineID
	FROM   		dbo.tblTRLine AS tr LEFT OUTER JOIN
				dbo.tblTRLedger AS trL ON tr.TRLedgerID = trL.TRLedgerID
	WHERE     	(trL.locationID IN (3, 5))
	GROUP BY 	tr.POLineID
}
...

As is necessary for a UNION both SELECTs have the same number of columns.

If on one of the SELECT statements there were an optional field I'd like to include can someone please explain how I move from this format to one that would populate the optional field as well? For example if in the 2nd SELECT statement a field PARAM1 (optional) exists how I can create the table?

For clarity I'm looking for:

SELECT1:
FIELD1     FIELD2     FIELD3
a          b          c
d          e          f

SELECT2:
FIELD1     FIELD2     FIELD3     FIELD4(opt)
g          h          i
j          k          l          999

OUTPUT:
FIELD1     FIELD2     FIELD3     FIELD4
a          b          c
d          e          f
g          h          I
j          k          l          999

 

[imex]

Hi,

Try something like this:

with InvTrans as
(
    select Field1, Field2, Field3, null as Field4
    from TableA

    union all

    select Field1, Field2, Field3, Field4
    from TableB
)

-- ...

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[firmusgrp]

... always a bit funny (read: sad) when you are reminded of the basics you've forgotten from lack of use.

Thanks imex, I appreciate the refresher.