Incorrect syntax near the keyword 'AS'

I have the attached sql to find the Oldest call back (OldestCB) and insert it into a table called 'batching'. It erros with 'Incorrect syntax near the keyword 'AS' but cannot work out why. I know it'll be something simple so please someone help! :)
Thanks
Tim
'insert into batching (OldestCB) 
select top 1 cast(floor(cast(lastdate as float )) as datetime) from sqlserver.tms42.sysdba.telrcm' + right(@telcomtable,3) + ' where (customerstatus like ''%ngaged%'' or customerstatus like ''%pecific%'' or customerstatus like ''%eneral%'' or customerstatus like ''%orning%'' or customerstatus like ''%fternoon%'' or customerstatus like ''%vening%'' or customerstatus like ''%nswer%'' or customerstatus like ''%locked%'') and owner not like ''%OVER%'' AS OldestCB 
from sqlserver.tms42.sysdba.telrcm' + right(@telcomtable,3) + ' tlr inner join sqlserver.tms42.sysdba.telcom' + right(@telcomtable,3) + ' tlc 
on tlr.account = tlc.account
group by tlr.owner '

Open in new window

BetamaxAsked:
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.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Can you post the statement created out of this sql code..
0
Aneesh RetnakaranDatabase AdministratorCommented:
Something like this
 

'insert into batching (OldestCB)
select top 1 CAST(floor(cast(lastdate as float )) as DATETIME )
from sqlserver.tms42.sysdba.telrcm' + right(@telcomtable,3) + ' tlr inner join sqlserver.tms42.sysdba.telcom' + right(@telcomtable,3) + ' tlc
on tlr.account = tlc.account
where (customerstatus like ''%ngaged%'' or customerstatus like ''%pecific%'' or customerstatus like ''%eneral%'' or customerstatus like ''%orning%'' or customerstatus like ''%fternoon%'' or customerstatus like ''%vening%'' or customerstatus like ''%nswer%'' or customerstatus like ''%locked%'')
and owner not like ''%OVER%''
group by tlr.owner '
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
BetamaxAuthor Commented:
Many thanks aneeshattingal!

It ran for just over 3minutes 30 before giving this error.

Msg 209, Level 16, State 1, Line 5
Ambiguous column name 'customerstatus'.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Ambiguous column name 'customerstatus'.

Seems like both the tables joined in your dynamically formed tables contains the table name customerstatus and hence you are obtaining the error..
0
BetamaxAuthor Commented:
Thanks rrjegan17,

I've changed all column names to have 'tlr.'before them. Just running it now!
0
BetamaxAuthor Commented:
Error today is...

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'CampaignID', table 'PTFReporting.dbo.Batching'; column does not allow nulls. INSERT fails.
The statement has been terminated.
0
BetamaxAuthor Commented:
Solution required a little changing to work but it did get me on the right track. Many thanks!
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 2005

From novice to tech pro — start learning today.