MS Query Incorrect syntax near the keyword 'AS'

Question

I am trying to amend SQL in MS query and the error in the title is being returned when I submit this code. What is wrong with the AS in this case? I could do try to do this elsewhere but it would be simplest if I can get Query to work. Thanks *Or is there a way to improve the code so that AS isn't required?

SELECT 
ARM_GP_GROUPINGS.GrpCode, 
arm_cc_group_names.cc_name, 
CST_COSTCENTRE.CC_CODE, 
CST_COSTCENTRE.CC_NAME,
CST_COSTCENTRE.CC_LEVELPOINTER,
CST_COSTHEADER.CH_CODE, 
CST_COSTHEADER.CH_NAME, 
CST_COSTHEADER.CH_USER3, 
CST_COSTHEADER.CH_USER4, 
CST_DETAIL.CT_NETT, 
CST_DETAIL.CT_SORTTYPE, 
CST_DETAIL.CT_PERIODNUMBR, 
CST_DETAIL.CT_YEAR, 
SL_ACCOUNTS.CUCODE, 
SL_ACCOUNTS.CUNAME, 
CST_COSTHEADER.CH_CODE

FROM
MWLIVE.dbo.CST_DETAIL CST_DETAIL  
AS  Detail 
INNER 
JOIN MWLIVE.dbo.CST_COSTCENTRE CST_COSTCENTRE  
AS  Costcentre 
ON Costcentre.CC_CODE = Detail.CT_COSTCENTRE
INNER
JOIN MWLIVE.dbo.CST_COSTHEADER CST_COSTHEADER
AS Costheader
ON Costheader.CH_CODE = Costcentre.CC_COPYHEADER
LEFT OUTER
JOIN Costcentre
ON Costcentre.CC_LEVELPOINTER = MWLIVE.dbo.arm_cc_group_namesarm_cc_group_names.cc_levelpointer
LEFT OUTER
JOIN Costheader  
ON Costheader.CH_ACCOUNT = MWLIVE.dbo.SL_ACCOUNTS SL_ACCOUNTS.CUCODE
INNER
JOIN Costheader
ON Costheader.CH_CODE = MWLIVE.dbo.ARM_GP_GROUPINGS ARM_GP_GROUPINGS.ch_code

WHERE ((CST_DETAIL.CT_YEAR='C') AND (CST_DETAIL.CT_PERIODNUMBR=1))

score 0 · Answer 1 · answered Jun 27 '16 at 16:21

I have never seen a SQL statement with so many errors! Here are some of your issues:

SELECT ARM_GP_GROUPINGS.GrpCode    --ARM_GP_GROUPINGS does not exist as table or alias
  ,arm_cc_group_names.cc_name      --arm_cc_group_names does not exist as table or alias
  ,CST_COSTCENTRE.CC_CODE          --Should it be the alias Costcentre not CST_COSTCENTRE
  ,CST_COSTCENTRE.CC_NAME          --AS above
  ,CST_COSTCENTRE.CC_LEVELPOINTER  --AS above
  ,CST_COSTHEADER.CH_CODE          --Should it be the alias Costheader not CST_COSTHEADER
  ,CST_COSTHEADER.CH_NAME          --AS above
  ,CST_COSTHEADER.CH_USER3         --AS above
  ,CST_COSTHEADER.CH_USER4         --AS above
  ,CST_DETAIL.CT_NETT              --Should it be the alias Detail not CST_DETAIL
  ,CST_DETAIL.CT_SORTTYPE          --As above
  ,CST_DETAIL.CT_PERIODNUMBR       --As above
  ,CST_DETAIL.CT_YEAR              --As above 
  ,SL_ACCOUNTS.CUCODE
  ,SL_ACCOUNTS.CUNAME
  ,CST_COSTHEADER.CH_CODE          --Should it be the alias Costheader not CST_COSTHEADER
FROM MWLIVE.dbo.CST_DETAIL CST_DETAIL AS Detail --Double Alias
  INNER JOIN MWLIVE.dbo.CST_COSTCENTRE CST_COSTCENTRE AS Costcentre --Double Alias
    ON Costcentre.CC_CODE = Detail.CT_COSTCENTRE
  INNER JOIN MWLIVE.dbo.CST_COSTHEADER CST_COSTHEADER AS Costheader --Double Alias
    ON Costheader.CH_CODE = Costcentre.CC_COPYHEADER
  LEFT JOIN Costcentre --Your joining to a table already defined as an alias
    ON Costcentre.CC_LEVELPOINTER = MWLIVE.dbo.arm_cc_group_namesarm_cc_group_names.cc_levelpointer
  LEFT JOIN Costheader --Your joining to a table already defined as an alias
    ON Costheader.CH_ACCOUNT = MWLIVE.dbo.SL_ACCOUNTS SL_ACCOUNTS.CUCODE --What is this space?
  INNER JOIN Costheader --Your joining to a table already defined as an alias AGAIN!
    ON Costheader.CH_CODE = 
      MWLIVE.dbo.ARM_GP_GROUPINGS ARM_GP_GROUPINGS.ch_code --What is this space?
  WHERE (
    (CST_DETAIL.CT_YEAR = 'C')           --Should it be the alias Detail not CST_DETAIL
    AND (CST_DETAIL.CT_PERIODNUMBR = 1)  --Should it be the alias Detail not CST_DETAIL
    )

MS Query Incorrect syntax near the keyword 'AS'

1 Answers1