SQLTeam.com | Weblogs | Forums

Add column to a Union Query


#1

I have the following union view.
SELECT TrnYear, TrnMonth, Journal, DistrValue, ExpenseGlCode, Reference
FROM dbo.T1
UNION ALL
SELECT CbTrnYear, CbTrnMonth, Journal, TrnValue, GlCode, Notation
FROM dbo.T2
UNION ALL
SELECT GlYear, GlPeriod, Journal, EntryValue, GlCode, Reference
FROM dbo.T3
How do I add new column to table T1 and T2 and T3 called Source
Source would have following records AP for Table T1, CSHB for table T2 and GL for T3

So My Union will read
TrnYear, TrnMonth, Journal, DistrValue, ExpenseGlCode, Reference, Source
2015 10 1 50.15 1011000 TEST GL
2015 10 1 40.50 1011000 TEST1 CSHB
2015 10 1 10.00 1011000 TEST2 AP


#2

[quote="Patyk, post:1, topic:3724"]
SELECT TrnYear, TrnMonth, Journal, DistrValue, ExpenseGlCode, Reference, 'AP' as Source
FROM dbo.T1
UNION ALL
SELECT CbTrnYear, CbTrnMonth, Journal, TrnValue, GlCode, Notation, 'CSHB'
FROM dbo.T2
UNION ALL
SELECT GlYear, GlPeriod, Journal, EntryValue, GlCode, Reference, 'GL'
FROM dbo.T3
[/quote]You would need to make all three items (AP, CSHB and GL) the same data type...or have I missed the boat completely?