• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

Need help with using the UNION to union two SELECT statements

I have the two SELECT statements that work great when executed alone. I need to union them so I have the 2 results combined and returned as 1. When I use UNION for the 2 SELECTS, I get the follow error:

Msg 207, Level 16, State 3, Line 1
Invalid column name 'tBirthDate'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'tBirthDate'.

Here is the complete statements

Select cType as Type,
cDesc as Description,
CONVERT(varchar(2),
MONTH(dtEvent)) + ' - ' + CONVERT(varchar(2), DAY(dtEvent)) as Date,
month(dtEvent) as Month
from dbo.Events
union
SELECT 'Birthday' as Type,
ISNULL(cNickName, cFirstName) + ' ' + ISNULL(cLastName, '') + ' ' + ISNULL(cSuffix, '') AS Description,
CONVERT(varchar(2),
MONTH(tBirthDate)) + ' - ' + CONVERT(varchar(2), DAY(tBirthDate)) AS Date,
month(tBirthDate) as Month
FROM         dbo.PEOPLE
WHERE     (MONTH(tBirthDate) = month(getdate()) OR
MONTH(tBirthDate) = (month(getdate())+1)) AND (bHideBirthdate = 0) AND (nStatusUID = 75)
ORDER BY MONTH(tBirthDate), DAY(tBirthDate)

Can you help me with this getting it working?
0
gtrapp
Asked:
gtrapp
2 Solutions
 
chapmandewCommented:
Does the tBirthDate column exist in the People table?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this will work:
Select cType as Type, 
cDesc as Description, 
REPLACE(CONVERT(varchar(5), dtEvent, 1 ), '/', '-') as [Date], 
month(dtEvent) as Month 
from dbo.Events
union all
SELECT 'Birthday' as Type, 
ISNULL(cNickName, cFirstName) + ' ' + ISNULL(cLastName, '') + ' ' + ISNULL(cSuffix, '') AS Description, 
REPLACE(CONVERT(varchar(5), tBirthDate, 1 ), '/', '-') as [Date], 
month(tBirthDate) as Month
FROM         dbo.PEOPLE
WHERE     (MONTH(tBirthDate) = month(getdate()) OR
MONTH(tBirthDate) = (month(getdate())+1)) AND (bHideBirthdate = 0) AND (nStatusUID = 75)
ORDER BY  3

Open in new window

0
 
gtrappAuthor Commented:
Yes.

I am thinking that it could be data type of the column. Having two different data types will not make the UNION work.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now