Hi 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)

from

Customer.dbo.Customer_Geo d (nolock) inner join

MaintDB.dbo.Branch s (nolock) on d.Siteid = s.BranchID inner join

MaintDB.dbo.country c (nolock) on s.countryid = c.CountryID left join

Customer.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 join

Customer.dbo.Customer_Visited v (nolock) on d.Custid = v.Custid left join

Customer.dbo.Customer_Type rv (nolock) on v.ID_Visit = rv.ID_Visit

order by S.Branchno,d.Custid, v.VID