[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • Last Modified:

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

0
Betamax
Asked:
Betamax
  • 4
  • 2
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now