troubleshooting Question

SQL - Syntax Query Damn Bracket :) Take2

Avatar of Zack
ZackFlag for Australia asked on
Microsoft SQL ServerSQL
3 Comments1 Solution15 ViewsLast Modified:
Hi EE,

For some reason on the last line of this code, I have syntax issue:

;WITH B1 AS (
SELECT CS.TRANSRECID, CS.TRANSDATE, CS.OFFSETTRANSVOUCHER, CS.ACCOUNTNUM,
                CS.SETTLEAMOUNTCUR, CS.CREATEDDATETIME,
CS.OFFSETRECID, CS.RECID, CS.DATAAREAID, CS.CREATEDBY
FROM dbo.CUSTSETTLEMENT CS
WHERE CS.DATAAREAID IN (N'EF', N'CSG')
AND (CS.OFFSETTRANSVOUCHER LIKE N'R-%'
                      OR CS.OFFSETTRANSVOUCHER LIKE N'P%'
                      OR CS.OFFSETTRANSVOUCHER LIKE N'P%')
AND CS.CREATEDDATETIME BETWEEN '2018-01-01' AND '2020-01-01'
), B2 AS (
SELECT CSO.OFFSETTRANSVOUCHER, CSO.TRANSRECID, CSO.TRANSDATE,
CSO.ACCOUNTNUM, CSO.SETTLEAMOUNTCUR, CSO.CREATEDDATETIME,
CSO.CREATEDBY, CSO.DATAAREAID, CSO.OFFSETRECID
FROM dbo.CUSTSETTLEMENT CSO
INNER JOIN B1 ON CSO.DATAAREAID = B1.DATAAREAID
AND CSO.TRANSRECID = B1.OFFSETRECID
AND CSO.OFFSETRECID = B1.TRANSRECID
WHERE (CSO.OFFSETTRANSVOUCHER LIKE N'R%'
              OR CSO.OFFSETTRANSVOUCHER LIKE N'I%')
)


SELECT B2.OFFSETTRANSVOUCHER AS SettledBy, B1.OFFSETTRANSVOUCHER,
B2.TRANSRECID AS OFFSETRECID,
B1.TRANSRECID, B1.TRANSDATE, B1.ACCOUNTNUM, B1.SETTLEAMOUNTCUR,
B1.CREATEDDATETIME,  B1.DATAAREAID, B1.CREATEDBY
INTO #TEMP_COMM
FROM B1
INNER JOIN B2 ON B1.DATAAREAID = B2.DATAAREAID
AND B1.OFFSETRECID = B2.TRANSRECID
AND B1.TRANSRECID = B2.OFFSETRECID
GROUP BY B2.OFFSETTRANSVOUCHER, B1.OFFSETTRANSVOUCHER, B2.TRANSRECID,
B1.TRANSRECID, B1.TRANSDATE, B1.ACCOUNTNUM, B1.SETTLEAMOUNTCUR,
B1.CREATEDDATETIME, B1.DATAAREAID, B1.CREATEDBY
--select *from #TEMP_COMM


;WITH SMT AS (
SELECT B.TRANSRECID,B.OFFSETTRANSVOUCHER,B.ACCOUNTNUM,B.SETTLEAMOUNTCUR,
        B.DATAAREAID,CT2.INVOICEPROJECT,CT2.INVOICE,B.TRANSDATE,B.CREATEDDATETIME,B.SettledBy,
CT.CREATEDTRANSACTIONID, CT.PAYMREFERENCE
FROM #TEMP_COMM B
LEFT JOIN CUSTTRANS CT ON CT.DATAAREAID=B.DATAAREAID AND CT.RECID=B.TRANSRECID
LEFT JOIN CUSTTRANS CT2 ON CT2.DATAAREAID=B.DATAAREAID
           AND CT2.RECID=B.OFFSETRECID  ) as B3

Error: Msg 156, Level 15, State 1, Line 45
Incorrect syntax near the keyword 'as'.

Any assistance is welcome. Thank you. 

Thank you. 
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros