MS Sql 2008 Studio - generates alias when saving a view.

I have an existing view that's not complicated but complex.  I'm adding a select to it and when I save the view Studio adds an alias to the end of it and that causes a syntax error.  

SELECT     TOP (100) PERCENT dbo.Users.ID AS UserId, dbo.Users_Info.Home_Area_Code, dbo.Users_Info.Work_Area_Code, dbo.Users_Info.State AS HomeState, 
                      (CASE WHEN (dbo.Users_Info.Address1 = '' OR
                      dbo.Users_Info.Address1 IS NULL) THEN 0 ELSE 1 END) AS has_home_address, (CASE WHEN dbo.users.ID NOT IN
                          (SELECT     UserID
                            FROM          Users_Client_Companies) THEN 1 ELSE 0 END) AS Eliminate_TSI_Clients, (CASE WHEN dbo.users.ID IN
                          (SELECT     individual_number
                            FROM          qUsersPresented_ES) THEN 1 ELSE 0 END) AS Presented_ES, (CASE WHEN dbo.users.ID IN
                          (SELECT     individual_number
                            FROM          qUsersPlaced_ES
                            WHERE      isnull(date_of_placement, '') > '') THEN 1 ELSE 0 END) AS Placed_ES, (CASE WHEN dbo.users.ID IN
                          (SELECT     individual_number
                            FROM          qUsersPresented_CS) THEN 1 ELSE 0 END) AS Presented_CS, (CASE WHEN dbo.users.ID IN
                          (SELECT     individual_number
                            FROM          qUsersPlaced_CS
                            WHERE      isnull(date_of_placement, '') > '') THEN 1 ELSE 0 END) AS Placed_CS, (CASE WHEN dbo.users.ID IN
                          (SELECT     individual_number
                            FROM          QEliteAdCandidatesPanel
                            WHERE      isnull(date_of_placement, '') > '') THEN 1 ELSE 0 END) AS Placed_EJA, (CASE WHEN dbo.users.ID IN
                          (SELECT     individual_number
                            FROM          QEliteAdCandidatesPanel) THEN 1 ELSE 0 END) AS Presented_EJA, (CASE WHEN dbo.users.id IN
                          (SELECT     ID
                            FROM          qUsers_TTMember) THEN 1 ELSE 0 END) AS TT_Member, (CASE WHEN dbo.users.id IN
                          (SELECT     ID
                            FROM          qUsers_HasHomeEmail) THEN 1 ELSE 0 END) AS Has_Home_Email, (CASE WHEN dbo.users.id IN
                          (SELECT     ID
                            FROM          qUsers_HasWorkEmail) THEN 1 ELSE 0 END) AS Has_Work_Email, dbo.Companies.Sales_Level, dbo.Users_Salary.ISIPCT AS Individual_Letter_Per, 
                      dbo.Users_Salary.ISCPCT AS Corporate_Letter_Per, (CASE WHEN (dbo.Users_Info.Basic_JobAd = 1 OR
                      dbo.Users_Info.Custom_JobAd = 1 OR
                      dbo.Users_Info.Elite_JobAd = 1 OR
                      dbo.Users_Info.EJA_Candidate = 1) THEN 1 ELSE 0 END) AS job_board, (CASE WHEN dbo.Users_Info.Current_Company_Number IN
                          (SELECT     company_id
                            FROM          Companies_Adv) THEN 1 ELSE 0 END) AS Comp_Adv, (CASE WHEN dbo.Users_Info.Current_Company_Number IN
                          (SELECT     company_id
                            FROM          Companies_BJA) THEN 1 ELSE 0 END) AS Comp_BJA, (CASE WHEN dbo.Users_Info.Current_Company_Number IN
                          (SELECT     company_id
                            FROM          Companies_CJA) THEN 1 ELSE 0 END) AS Comp_CJA, (CASE WHEN dbo.Users_Info.Current_Company_Number IN
                          (SELECT     company_id
                            FROM          Companies_CS) THEN 1 ELSE 0 END) AS Comp_CS, (CASE WHEN dbo.Users_Info.Current_Company_Number IN
                          (SELECT     company_id
                            FROM          Companies_EJA) THEN 1 ELSE 0 END) AS Comp_EJA, (CASE WHEN dbo.Users_Info.Current_Company_Number IN
                          (SELECT     company_id
                            FROM          Companies_ES) THEN 1 ELSE 0 END) AS Comp_ES, (CASE WHEN dbo.Users_Info.Current_Company_Number IN
                          (SELECT     company_id
                            FROM          Companies_Intern) THEN 1 ELSE 0 END) AS Comp_Intern, (CASE WHEN dbo.Users_Info.Current_Company_Number IN
                          (SELECT     company_id
                            FROM          Companies_OrgAssess) THEN 1 ELSE 0 END) AS Comp_OrgAssess, (CASE WHEN dbo.Users_Info.Current_Company_Number IN
                          (SELECT     company_id
                            FROM          Companies_SB) THEN 1 ELSE 0 END) AS Comp_SB,
                          (SELECT     MAX(EJA_Date_Purchased) AS EJA_Date_Purchased
                            FROM          (SELECT     date_purchased AS EJA_Date_Purchased
                                                    FROM          dbo.QEliteContacts
                                                    WHERE      (contact_id = dbo.Users.ID)) AS t) AS Date_Purchased_EJA, dbo.Users_Salary.ISTEC1 AS Technical_Federal_Per, 
                      dbo.Users_Salary.ISTEC2 AS Technical_StateLocal_Per, dbo.Users_Salary.ISTEC3 AS Technical_InternationalForeign_Per, 

Open in new window


The line near the bottom that says "(select  max(eja_date_purchased)......."  is the culprit.  The view QEliteContacts is;

SELECT     bd.date_purchased, bd.ID, csc.contact_id
FROM         dbo.Board_Details AS bd LEFT OUTER JOIN
                      dbo.Client_Services_Contacts AS csc ON csc.service_id = bd.ID
WHERE     (bd.ad_type LIKE '%Elite%')

Open in new window



The code that gets generated dynamically to run this query is;

<cfquery datasource="#request.dsn#" name="BuildCallList" >
				#preservesinglequotes(request.qryString)#)
				order by 
				<cfif attributes.CLtype eq 1>
					cl.userid
				<cfelseif attributes.CLtype eq 2>
					cl.id
				<cfelse>
					cl.id
				</cfif>
			 </cfquery>

Open in new window


And the query string is;

select distinct(cl.userid) from View_CallListFields as cl where 0 = 0 AND ( 1=1 and ( ( cl.Contact_Type_Acronym = 'ha' ) ) and ( cl.Date_Purchased_EJA >= '2013/01/01' AND cl.Date_Purchased_EJA <= '2013/10/09' ) ) order by cl.userid


I can run the view QEliteContacts by itself without error.

I can pare the whole thing down to;

SELECT     TOP (100) PERCENT dbo.Users.ID AS UserId,  
          (SELECT     MAX(EJA_Date_Purchased) AS EJA_Date_Purchased
              FROM  (SELECT date_purchased AS EJA_Date_Purchased
                       FROM dbo.QEliteContacts
                       WHERE (contact_id = dbo.Users.ID)) AS t) AS Date_Purchased_EJA,
					   dbo.Client_Services_Contact_Types.Contact_Type_Acronym
			FROM      dbo.Users INNER JOIN
                      dbo.Users_Info ON dbo.Users.ID = dbo.Users_Info.ID  LEFT OUTER JOIN
                      dbo.Client_Services_Contacts ON dbo.Client_Services_Contacts.contact_id = dbo.Users_Info.ID LEFT OUTER JOIN
                      dbo.Client_Services_Contact_Types ON dbo.Client_Services_Contact_Types.id = dbo.Client_Services_Contacts.ContactTypeID
WHERE     (dbo.Users_Info.Status_Flag = 'A') AND (1 = 1) AND (dbo.Client_Services_Contact_Types.Contact_Type_Acronym = 'ha') AND (Date_Purchased_EJA >= '2013/01/01') 
                      AND (Date_Purchased_EJA <= '2013/10/09')
ORDER BY UserID

Open in new window


 But when I run the view I get "invalid column name date_purchased_EJA".
lantervAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lawrence BarnesCommented:
You are referring to the Date_Purchased_EJA in the where statement but that field name is being created in the select statement...so you cannot call it within the where statement.  Below I've adjusted the where statement to use the correlated subquery you used in your select.

SELECT TOP (100) 
    PERCENT dbo.Users.ID AS UserId
    , (	SELECT MAX(EJA_Date_Purchased) AS EJA_Date_Purchased
        FROM  (	SELECT date_purchased AS EJA_Date_Purchased
                FROM dbo.QEliteContacts
                WHERE (contact_id = dbo.Users.ID)) AS t) AS Date_Purchased_EJA
    , dbo.Client_Services_Contact_Types.Contact_Type_Acronym
FROM dbo.Users 
INNER JOIN dbo.Users_Info ON dbo.Users.ID = dbo.Users_Info.ID  
LEFT OUTER JOIN dbo.Client_Services_Contacts ON dbo.Client_Services_Contacts.contact_id = dbo.Users_Info.ID 
LEFT OUTER JOIN dbo.Client_Services_Contact_Types ON dbo.Client_Services_Contact_Types.id = dbo.Client_Services_Contacts.ContactTypeID
WHERE (dbo.Users_Info.Status_Flag = 'A') 
    AND (1 = 1) 
    AND (dbo.Client_Services_Contact_Types.Contact_Type_Acronym = 'ha') 
/*	AND (Date_Purchased_EJA >= '2013/01/01')
    AND (Date_Purchased_EJA <= '2013/10/09')*/
AND (	SELECT MAX(EJA_Date_Purchased) AS EJA_Date_Purchased
        FROM  (	SELECT date_purchased AS EJA_Date_Purchased
                FROM dbo.QEliteContacts
                WHERE (contact_id = dbo.Users.ID)) AS t) BETWEEN '2013/01/01' AND '2013/10/09'
ORDER BY UserID

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lantervAuthor Commented:
Okay, I see the problem.   But.......  I'm building the where clause on the fly.  I'll have to see if I can work it out.  However, you did answer the question.  Many thanks.
0
Lawrence BarnesCommented:
When you are buiding it on the fly you need to substitute this:
(SELECT MAX(EJA_Date_Purchased) AS EJA_Date_Purchased FROM  (SELECT date_purchased AS EJA_Date_Purchased FROM dbo.QEliteContacts WHERE (contact_id = dbo.Users.ID)) AS t)

Wherever you wrote:
Date_Purchased_EJA

 There is more optimization that could happen but I would have to have the table definitions to work through it.
0
lantervAuthor Commented:
I'm posting a new question related to this one.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.