Aleks wrote:
> I have an sql statement, as soon as I add the part :
>
> ----
> h.FirstNm + ' ' + h.LastNm as spousename, h.POBCountry as spousecob
> ----
>
> It gives me the following error:
>
> ---
> Server: Msg 156, Level 15, State 1, Line 16
> Incorrect syntax near the keyword 'as'.
> ---
>
>
> The full query is:
>
> ---
> SELECT t.Caseid, cases.id, cases.caseid, a.firstnm, a.lastnm,
> b.processcatalog, f.MaidenNm as
> EmployerNm,k.filingtype,m.firmname,o.PreferenceType,
> cases.archived,cases.maincase,cases.I140Filingdate, cases.Createdon,
> cases.Lastworked
> ,cases.expdate,cases.approvaldate,a.I94,a.I94dateD,a.I797,a.I797date,a.Nistatus,a.expireson,a.NIVmaxstatus,a.POBCountry,a.email,l.JobTitle,
> h.FirstNm + ' ' + h.LastNm as spousename, h.POBCountry as spousecob
> FROM (SELECT DISTINCT Caseid, userid FROM Atts) t
> INNER JOIN Users ON (t.userid = Users.userid) INNER JOIN Cases ON (t.caseid
> = Cases.id)
> LEFT JOIN Users as a ON (a.userid = Cases.alienid)
> left join users as f on cases.empid = f.userid
> INNER JOIN FilingType as k ON cases.Processtype = k.FilingTypeId
> left join FirmAddresses as m on cases.firmaddressid = m.id
> INNER JOIN Processcatalog as b ON (b.processcatalogid = cases.process)
> left join PreferenceType as o on o.PrefTypeid = cases.PrefType
> left join employment as L on a.userid=L.userid and l.curemp = 1
> left join Partiescont as x on a.userid = x.PartcontId and relation =
> 'spouse'
> left join users as h on x.userid = h.userid
> WHERE t.userid IN (MMColParam) GROUP BY t.Caseid, cases.id, cases.caseid,
> a.firstnm, a.lastnm, b.processcatalog , f.MaidenNm
> ,k.filingtype,m.firmname,o.PreferenceType,cases.archived,cases.maincase,cases.I140Filingdate,
> cases.Createdon, cases.Lastworked
> ,cases.expdate,cases.approvaldate,a.I94,a.I94dateD,a.I797,a.I797date,a.Nistatus,a.expireson,a.NIVmaxstatus,a.POBCountry,a.email,l.JobTitle,
> h.FirstNm + ' ' + h.LastNm as spousename, h.POBCountry as spousecob
> HAVING COUNT(*) = MMColParam2
> ---
>
> Without the part above the query works just fine.
> Any ideas ? I think the 'as' syntax is correct.
>
> Aleks