Query

Hi

I am trying to execute the below query in Query analyzer, the same query is executed in Sybase with the corresponding columns.

select (a.OnlineAccessId) + CASE
WHEN (a.onlineaccessid2) <> '00000000000000' THEN (a.onlineaccessid2)
else ''
End + CASE
WHEN (a.onlineaccessid3) <> '00000000000000' THEN (a.onlineaccessid3)
else ''
End as onlineaccessids, a.id as AcctInfoId, BankNo, RegionNo, OfficeNo, AcctNo,AcctShortNm ,SecLending, q.id as UsersId
from AcctInfo AS a INNER JOIN "104030" AS p ON a.AdminOfficer = p.CrmCode
inner join Users as q ON p.CrmDesc = q.OnlineaccessId union select
OnlineAccessIds = (a.OnlineAccessId) + CASE
WHEN (a.onlineaccessid2) <> '00000000000000' THEN (a.onlineaccessid2)
else ''
End + CASE
WHEN (a.onlineaccessid3) <> '00000000000000' THEN (a.onlineaccessid3)
else ''
End,q.id, a.Id, BankNo, RegionNo,
OfficeNo, AcctNo,AcctShortNm, SecLending
from AcctInfo as a
inner join users as q on
(q.onlineaccessid = substring(onlineaccessids, 1, 3))

The error message is

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'onlineaccessids'..

It is able to recognize the field "onlineaccessids" and giving me the results in Sybase database and giving the error in SQL Server.
Try to help me out.

Thanx in advanceFirst, double check that both the AcctInfo and users tables have an attribute "onlineaccessids".

Secondly, add an alias to:

(q.onlineaccessid = substring(a/q.onlineaccessids, 1, 3))