John Ellis
asked on
T-SQL: Embedding a CTE
Hello:
As shown below, I have two sets of T-SQL code. The first set works. But, I need to modify it to embed a CTE that I call "cte10". That's what the purpose of my second code block below is.
But, I'm getting syntax errors. I'm not very good at embedding CTE's. Perhaps, this cannot be done. I don't know.
Anyway, I'm trying to create a "syntax" that allows for cte10 to show that there should be only one record for the RM20201.APTODCNM field. That's why I'm trying to say that "Cnt" (or "count") is equal to only 1 of RM20201.APTODCNM.
I'm sure that it's just a matter of my placing a parentheses or something like that in the right spot.
Could someone help me, please?
Thank you!
John
As shown below, I have two sets of T-SQL code. The first set works. But, I need to modify it to embed a CTE that I call "cte10". That's what the purpose of my second code block below is.
But, I'm getting syntax errors. I'm not very good at embedding CTE's. Perhaps, this cannot be done. I don't know.
Anyway, I'm trying to create a "syntax" that allows for cte10 to show that there should be only one record for the RM20201.APTODCNM field. That's why I'm trying to say that "Cnt" (or "count") is equal to only 1 of RM20201.APTODCNM.
I'm sure that it's just a matter of my placing a parentheses or something like that in the right spot.
Could someone help me, please?
Thank you!
John
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE
AND RM20101.DOCNUMBR in (
select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 on RM20201.APFRDCNM = RM20101.DOCNUMBR AND RM20201.CUSTNMBR = RM20101.CUSTNMBR
WHERE RM20201.APPTOAMT = RM20101.ORTRXAMT AND RM20201.APFRDCDT < @AGE AND RM20201.APTODCDT < @AGE
AND RM20201.ApplyFromGLPostDate < @AGE
GROUP BY RM20201.APFRDCNM, RM20201.CUSTNMBR
)
and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> ''
THEN RM20201.APPTOAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149',
'0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981', '00144', '0100015049', '0100014968',
'0100018654', '0100019363', '0100019371',
'0100020932', '0100021047', '0100024819', '0100030188', '0100014661', '0100015270', '0100032895', '0100022052',
'0100033398', 'WW_FLIGHT12', 'WEST_MD_HC01', 'TRN6557', 'SVC989E'))
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME,
RM20201.APTODCDT, RM20101.DOCNUMBR,
RM20101.DOCDATE, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT
--RM20201.APTODCTY, RM20201.APFRDCDT, RM20201.APTODCNM,
HAVING
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE
AND RM20101.DOCNUMBR in (
select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 on RM20201.APFRDCNM = RM20101.DOCNUMBR AND RM20201.CUSTNMBR = RM20101.CUSTNMBR
WHERE RM20201.APPTOAMT = RM20101.ORTRXAMT AND RM20201.APFRDCDT < @AGE AND RM20201.APTODCDT < @AGE
AND RM20201.ApplyFromGLPostDate < @AGE
GROUP BY RM20201.APFRDCNM, RM20201.CUSTNMBR
)
and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> ''
THEN RM20201.APPTOAMT * -1 ELSE 0 END
<> 0select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE
AND RM20101.DOCNUMBR in (;with cte10 AS (
select RM20201.APFRDCNM,
COUNT(*) OVER (PARTITION BY RM20101.CUSTNMBR, RM20201.APTODCNM) AS Cnt
from RM20201
INNER JOIN RM20101 on RM20201.APFRDCNM = RM20101.DOCNUMBR AND RM20201.CUSTNMBR = RM20101.CUSTNMBR
WHERE RM20201.APPTOAMT = RM20101.ORTRXAMT AND RM20201.APFRDCDT < @AGE AND RM20201.APTODCDT < @AGE
AND RM20201.ApplyFromGLPostDate < @AGE),
select * from Cte10 where Cnt = 1
GROUP BY RM20201.APFRDCNM, RM20201.CUSTNMBR))
and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> ''
THEN RM20201.APPTOAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149',
'0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981', '00144', '0100015049', '0100014968',
'0100018654', '0100019363', '0100019371',
'0100020932', '0100021047', '0100024819', '0100030188', '0100014661', '0100015270', '0100032895', '0100022052',
'0100033398', 'WW_FLIGHT12', 'WEST_MD_HC01', 'TRN6557', 'SVC989E'))
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME,
RM20201.APTODCDT, RM20101.DOCNUMBR,
RM20101.DOCDATE, RM20101.RMDTYPAL, RM20201.DATE1, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APPTOAMT
--RM20201.APTODCTY, RM20201.APFRDCDT, RM20201.APTODCNM,
HAVING
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE < @AGE and RM20201.DATE1 > @AGE
AND RM20101.DOCNUMBR in (
select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 on RM20201.APFRDCNM = RM20101.DOCNUMBR AND RM20201.CUSTNMBR = RM20101.CUSTNMBR
WHERE RM20201.APPTOAMT = RM20101.ORTRXAMT AND RM20201.APFRDCDT < @AGE AND RM20201.APTODCDT < @AGE
AND RM20201.ApplyFromGLPostDate < @AGE
GROUP BY RM20201.APFRDCNM, RM20201.CUSTNMBR
)
and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> ''
THEN RM20201.APPTOAMT * -1 ELSE 0 END
<> 0ASKER
My apologies, Jim. You're right! :)
The following are the errors returned from SQL Management Studio:
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ';'.
Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 47
Incorrect syntax near the keyword 'and'.
Thank you, for your quick response!
John
The following are the errors returned from SQL Management Studio:
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ';'.
Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 47
Incorrect syntax near the keyword 'and'.
Thank you, for your quick response!
John
Okay. So double-click on the error messages, watch the cursor jump to the offending line, then tell us what line that is in your code block, as the SSMS code line will not be the same number as the EE code block line number.
Hi John,
Can you please provide me your latest running SQL and your new requirement you wanted to achieve ?
Thank you !
Hope it helps
Can you please provide me your latest running SQL and your new requirement you wanted to achieve ?
Thank you !
Hope it helps
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It would greatly help if you could tell us these syntax errors, and what line number in the code that throws the error / which one that is in the above code blocks. Mind readers we ain't, and this is a really long T-SQL statement.