How to bring extra columns in a UNION ALL view? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to bring extra columns in a UNION ALL view?

Author

Topic

leodesol
Starting Member

9 Posts

Posted - 2006-07-19 : 12:37:27

I have a view that is using UNION ALL to combine common fields of two tables, this is my statement:

SELECT ID, STATUS, ACTIVE_STATUS, NS_PARENT_CHANGE_NUM, NS_REP, NS_CHANGE_NUM, NS_CHANGE_IDENTIFER
FROM dbo.CT_FRAME_T

UNION ALL

SELECT ID, STATUS, ACTIVE_STATUS, NS_PARENT_CHANGE_NUM, NS_REP, NS_CHANGE_NUM, NS_CHANGE_IDENTIFER
FROM dbo.CT_ATM_T

This works fine, but I would also like some fields that do not match to appear in the view. It is OK if the value is null for the rows of data from the other table that doesn't have the columns.

The other columns are called DLCI from CT_FRAME_T and then VPI, VCI from CT_ATM_T.

My view would then return ID, STATUS, ACTIVE_STATUS, NS_PARENT_CHANGE_NUM, NS_REP, NS_CHANGE_NUM, NS_CHANGE_IDENTIFER, DLCI (where applicable), VPI and VCI (where applicable). Is this possible?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-19 : 12:46:40

SELECT ID, STATUS, ACTIVE_STATUS, NS_PARENT_CHANGE_NUM, NS_REP, NS_CHANGE_NUM, NS_CHANGE_IDENTIFER, DLCI, Null as VPI, Null as VCI
FROM dbo.CT_FRAME_T

UNION ALL

SELECT ID, STATUS, ACTIVE_STATUS, NS_PARENT_CHANGE_NUM, NS_REP, NS_CHANGE_NUM, NS_CHANGE_IDENTIFER, Null as DLCI, VPI, VCI
FROM dbo.CT_ATM_T

Srinika

leodesol
Starting Member

9 Posts

Posted - 2006-07-19 : 12:52:58

Beautiful! thanks! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources