468,527 Members | 2,109 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,527 developers. It's quick & easy.

Incorrect syntax near the keyword 'AS'.

Hi Everybody,
I'm beginer in SQL and was wonder if anybody could take a look at this and help me. I got this error, and not sure how to fix it.
This is the error:
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AS'.

Any help so much appreciated
Expand|Select|Wrap|Line Numbers
  1. with [RDR SUB]
  2. as(
  3.  
  4. SELECT     [close_week_num], [product_desc], [team], 
  5. [CAST(RIGHT([tech_code], 5) AS int) as loginID], [tech_name], 
  6. [SUM(total_dps) AS Calls], [SUM(nbr_of_visits) + SUM(caused_rep_call) - SUM(total_dps) 
  7.                       AS Rpts], [parent_channel_desc]
  8. where      (close_week_num >= 200814)
  9. FROM  dbo.UNION_RDR_DAILY_DETAIL
  10.  
  11. Union
  12. select close_week_num, product_desc, team,loginID, tech_name, Calls, Rpts, parent_channel_desc, dbo.view_vision_users.EmployeID
  13. FROM  dbo.UNION_RDR_DAILY_DETAIL LEFT OUTER JOIN
  14.                       dbo.view_vision_users ON dbo.[RDR SUB].loginID = dbo.view_vision_users.logid
  15. GROUP BY close_week_num, loginID, tech_name, product_desc, team, parent_channel_desc
Sep 24 '07 #1
3 15060
pbmods
5,821 Expert 4TB
Heya, Skillman. Welcome to TSDN!

Please use CODE tags when posting source code:

[CODE=sql]
SQL code goes here.
[/CODE]
Sep 24 '07 #2
pbmods
5,821 Expert 4TB
Moving to the MS SQL forum....
Sep 24 '07 #3
azimmer
200 Expert 100+
Hi Everybody,
I'm beginer in SQL and was wonder if anybody could take a look at this and help me. I got this error, and not sure how to fix it.
This is the error:
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AS'.

Any help so much appreciated
Expand|Select|Wrap|Line Numbers
  1. with [RDR SUB]
  2. as(
  3.  
  4. SELECT     [close_week_num], [product_desc], [team], 
  5. [CAST(RIGHT([tech_code], 5) AS int) as loginID], [tech_name], 
  6. [SUM(total_dps) AS Calls], [SUM(nbr_of_visits) + SUM(caused_rep_call) - SUM(total_dps) 
  7.                       AS Rpts], [parent_channel_desc]
  8. where      (close_week_num >= 200814)
  9. FROM  dbo.UNION_RDR_DAILY_DETAIL
  10.  
  11. Union
  12. select close_week_num, product_desc, team,loginID, tech_name, Calls, Rpts, parent_channel_desc, dbo.view_vision_users.EmployeID
  13. FROM  dbo.UNION_RDR_DAILY_DETAIL LEFT OUTER JOIN
  14.                       dbo.view_vision_users ON dbo.[RDR SUB].loginID = dbo.view_vision_users.logid
  15. GROUP BY close_week_num, loginID, tech_name, product_desc, team, parent_channel_desc
A few things:
  • Each SELECT in a UNION has to have the same number and type of columns; you have an extra EmployeID in the second SELECT. If you do need it and don't have in the first SELECT, just throw in a "NULL as EmployeID" at the end.
  • The WITH ... AS has an opening but lacks a closing bracket
Hope it helps
Sep 24 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Jeff Magouirk | last post: by
11 posts views Thread by Mark Findlay | last post: by
2 posts views Thread by ielmrani via SQLMonster.com | last post: by
1 post views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.