InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 18:37:26 GMT20http://www.sqlservercentral.com/Forums/Topic457980-8-1.aspxThanks for all your helpWed, 20 Feb 2008 10:44:18 GMTpcq0125http://www.sqlservercentral.com/Forums/Topic457980-8-1.aspxI'm thinking I wouldn't put the sub-query there, especially since it's returning a single scalar value.you're also using 4 subqueries when you only need 1. Look at this rewrite (not seeing your data - I made some assumptions):[code]declare @ASSGNID int--calculate the scalarselect @ASSGNID=ASSGNID from MaintDB..Project (nolock) where pcode = '11224DER'select [ASSGNID] = @assgnID, --use the scalar[COUNTRY] = upper(c.FullName),[Branch NUMBER] = s.Branchno, [RECORD ID] = d.uid,[INITIALS] = d.initials,[DOB] = Customer.dbo.function_DateFormat(d.dob,'mdy','',0),[EXAMNUM] = d.Custid,vst.[EXAMDATE] ,[ENTRY] = r.ENTRYno,vst.[ENTRYDATE] ,vst.[EXAMFAILCAUSE] ,vst.[EXAMFAILDATE] ,vst.[EARLYTERMCAUSE],vst.[EARLYTERMDATE] ,[VISITRECID] = 'LQ1' + cast(v.VID as nvarchar),[VISITNAME] = rv.VisitCode,[VISITCOMPDATE] = Customer.dbo.function_DateFormat(v.Datevisited,'mdy','',0)fromCustomer.dbo.Customer_Geo d (nolock) inner joinMaintDB.dbo.Branch s (nolock) on d.Siteid = s.BranchID inner joinMaintDB.dbo.country c (nolock) on s.countryid = c.CountryID left joinCustomer.dbo.Customer_ROW r (nolock) on d.Custid = r.Custid left join--collapse all 4 subs into just one ( select CustID, Customer.dbo.function_DateFormat( max(case when ID_Visit = 1 then Datevisited else null end)) as EXAMDATE, Customer.dbo.function_DateFormat( max(case when ID_Visit = 3 then Datevisited else null end)) as ENTRYDATE, Customer.dbo.function_DateFormat( max(case when ID_Visit = 20 then Datevisited else null end)) as EXAMFAILDATE, Customer.dbo.function_DateFormat( max(case when ID_Visit = 21 then Datevisited else null end)) as EARLYTERMDATE, max(case when ID_Visit = 20 then CAUSEID else null end) as EXAMFAILCAUSE, max(case when ID_Visit = 21 then CAUSEID else null end) as EARLYTERMCAUSE from Customer.dbo.Customer_Visited (nolock) where ID_visit in (1,3,20,21) group by CustID ) vst on d.Custid = vst.Custid left joinCustomer.dbo.Customer_Visited v (nolock) on d.Custid = v.Custid left joinCustomer.dbo.Customer_Type rv (nolock) on v.ID_Visit = rv.ID_Visitorder by S.Branchno,d.Custid, v.VID [/code]The assignment syntax to create Aliases is also being deprecated. You probably want to start training yourself not to use it.Wed, 20 Feb 2008 08:18:45 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic457980-8-1.aspxHi Matt,I have some other factors involved in the query to use the alias and below is my original query and how would I resolve the problem with a subquery field? Please advise. Thanks.select [ASSGNID] = (select ASSGNID from MaintDB..Project (nolock)where pcode = '11224DER'),[COUNTRY] = upper(c.FullName),[Branch NUMBER] = s.Branchno, [RECORD ID] = d.uid,[INITIALS] = d.initials,[DOB] = Customer.dbo.function_DateFormat(d.dob,'mdy','',0),[EXAMNUM] = d.Custid,[EXAMDATE] = Customer.dbo.function_DateFormat(v1.Datevisited,'mdy','',0),[ENTRY] = r.ENTRYno,[ENTRYDATE] = Customer.dbo.function_DateFormat(v2.Datevisited,'mdy','',0),[EXAMFAILCAUSE] = v3.CAUSEID,[EXAMFAILDATE] = Customer.dbo.function_DateFormat(v3.Datevisited,'mdy','',0),[EARLYTERMCAUSE] = v4.CAUSEID,[EARLYTERMDATE] = Customer.dbo.function_DateFormat(v4.Datevisited,'mdy','',0),[VISITRECID] = 'LQ1' + cast(v.VID as nvarchar),[VISITNAME] = rv.VisitCode,[VISITCOMPDATE] = Customer.dbo.function_DateFormat(v.Datevisited,'mdy','',0)fromCustomer.dbo.Customer_Geo d (nolock) inner joinMaintDB.dbo.Branch s (nolock) on d.Siteid = s.BranchID inner joinMaintDB.dbo.country c (nolock) on s.countryid = c.CountryID left joinCustomer.dbo.Customer_ROW r (nolock) on d.Custid = r.Custid left join(select * from Customer.dbo.Customer_Visited (nolock) where ID_Visit = 1) as v1 on d.Custid = v1.Custid left join(select * from Customer.dbo.Customer_Visited (nolock) where ID_Visit = 3) as v2 on d.Custid = v2.Custid left join(select * from Customer.dbo.Customer_Visited (nolock) where ID_Visit = 20) as v3 on d.Custid = v3.Custid left join(select * from Customer.dbo.Customer_Visited (nolock) where ID_Visit = 21) as v4 on d.Custid = v4.Custid left joinCustomer.dbo.Customer_Visited v (nolock) on d.Custid = v.Custid left joinCustomer.dbo.Customer_Type rv (nolock) on v.ID_Visit = rv.ID_Visitorder by S.Branchno,d.Custid, v.VIDWed, 20 Feb 2008 07:56:28 GMTpcq0125http://www.sqlservercentral.com/Forums/Topic457980-8-1.aspxLook at the order of the execution of the statement.1. Evaluate FROM clause2. Evaluate WHERE clause3. Evaluate GROUP BY clause4. Evaluate HAVING clause5. Evaluate SELECT clauseYour SELECT clause with the Employee_ID alias goes after the GROUP BY clause that's why it is erroring out on you because there is no way the GROUP BY clause know about you alias. In this case you have to use the real name of the column.Wed, 20 Feb 2008 07:45:16 GMTghostriderhttp://www.sqlservercentral.com/Forums/Topic457980-8-1.aspx[quote][b]pcq0125 (2/20/2008)[/b][hr]Hi,I am on SQL 2000 and I an having problem with the square parenthesis error on the GROUP BY,. Can sombody help? Thanks.select [Employee_ID] = EmpID from dbo.Employee group by [Employee_ID]Server: Msg 207, Level 16, State 3, Line 2Invalid column name 'Employee_ID'.[/quote]Hello,It should be written asselect EmpID As Employee_ID from dbo.Employee group by EmpIDThere is no need for the square parenthesis and it should be used only when there is a space in between the alias being used for a column.Hope this is clear.ThanksWed, 20 Feb 2008 07:44:02 GMTlucky-80472http://www.sqlservercentral.com/Forums/Topic457980-8-1.aspxDon't use the alias in the GROUP BY.You probably want:[code]select EmpID as [Employee_ID] from dbo.Employee group by EmpID [/code]Or perhaps just simply use the DISTINCT, since you don't seem to be aggregating:[code]select distinct EmpID as [Employee_ID] from dbo.Employee[/code]Wed, 20 Feb 2008 07:42:54 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic457980-8-1.aspxHi,I am on SQL 2000 and I an having problem with the square parenthesis error on the GROUP BY,. Can sombody help? Thanks.select [Employee_ID] = EmpID from dbo.Employee group by [Employee_ID]Server: Msg 207, Level 16, State 3, Line 2Invalid column name 'Employee_ID'.Wed, 20 Feb 2008 07:33:56 GMTpcq0125