Skip to content
Advertisement

SQL server error in regards to common table expression [closed]

I have this query:

if object_id('tempdb..#mappings') is not null drop table #mappings;
Create table #mappings
(
    GLEntity int,
    NoteOwner VARCHAR(MAX)
)

INSERT INTO #mappings
VALUES(351, 'Ajax E Master Trust I, a Delaware Trust, Wilmington Savings Fund Society, FSB, Trustee'),
(346, 'U.S. Bank National Association, as Indenture Trustee on behalf of and with respect to Ajax Mortgage Loan Trust 2016-1, Mortgage-Backed Notes, Series 2016-1'),
(352, 'U.S. Bank National Association, as Indenture Trustee on behalf of and with respect to Ajax Mortgage Loan Trust 2016-2, Mortgage-Backed Notes, Series 2016-2'),
(542, 'U.S. Bank National Association, as Indenture Trustee on behalf of and with respect to Ajax Mortgage Loan Trust 2014-A, Mortgage-Backed Notes, Series 2014-A'),
(543, 'U.S. Bank National Association, as Indenture Trustee on behalf of and with respect to Ajax Mortgage Loan Trust 2014-B, Mortgage-Backed Notes, Series 2014-B'),
(544, 'U.S. Bank National Association, as Indenture Trustee on behalf of and with respect to Ajax Mortgage Loan Trust 2015-A, Mortgage-Backed Notes, Series 2015-A'),
(545, 'U.S. Bank National Association, as Indenture Trustee on behalf of and with respect to Ajax Mortgage Loan Trust 2015-B, Mortgage-Backed Notes, Series 2015-B'),
(546, 'U.S. Bank National Association, as Indenture Trustee on behalf of and with respect to Ajax Mortgage Loan Trust 2015-C, Mortgage-Backed Notes, Series 2015-C'),
(547, 'U.S. Bank National Association, as Indenture Trustee on behalf of and with respect to Ajax Mortgage Loan Trust 2016-A, Mortgage-Backed Notes, Series 2016-A'),
(548, 'U.S. Bank National Association, as Indenture Trustee on behalf of and with respect to Ajax Mortgage Loan Trust 2016-B, Mortgage-Backed Notes, Series 2016-B'),
(549, 'U.S. Bank National Association, as Indenture Trustee on behalf of and with respect to Ajax Mortgage Loan Trust 2016-C, Mortgage-Backed Notes, Series 2016-C'),
(550, 'U.S. Bank National Association, as Indenture Trustee on behalf of and with respect to Ajax Mortgage Loan Trust 2017-A, Mortgage-Backed Notes, Series 2017-A'),
(551, 'U.S. Bank National Association, as Indenture Trustee on behalf of and with respect to Ajax Mortgage Loan Trust 2017-B, Mortgage-Backed Notes'),
(552, 'U.S. Bank National Association, as Indenture Trustee on behalf of and with respect to Ajax Mortgage Loan Trust 2017-C, Mortgage-Backed Notes'),
(553, 'U.S. Bank National Association, as Indenture Trustee on behalf of and with respect to Ajax Mortgage Loan Trust 2017-D, Mortgage-Backed Notes'),
(601, 'U.S. Bank National Association, as Indenture Trustee on behalf of and with respect to Ajax Mortgage Loan Trust 2018-A Mortgage-Backed Notes'),
(602, 'U.S. Bank National Association, as Indenture Trustee on behalf of and with respect to Ajax Mortgage Loan Trust 2018-B, Mortgage-Backed Notes'),
(557, 'Ajax Mortgage Loan Trust 2018-C, Mortgage-Backed Securities, Series 2018-C, by U.S. Bank National Association, as Indenture Trustee'),
(557, 'U.S. Bank National Association, as Indenture Trustee on behalf of and with respect to Ajax Mortgage Loan Trust 2018-C, Mortgage-Backed Securities, Series 2018-C'),
(603, 'U.S. Bank National Association, as Indenture Trustee on behalf of and with respect to Ajax Mortgage Loan Trust 2018-D, Mortgage-Backed Securities, Series 2018-D'),
(603, 'Ajax Mortgage Loan Trust 2018-D, Mortgage-Backed Securities, Series 2018-D, by U.S. Bank National Association, as Indenture Trustee'),
(604, 'Ajax Mortgage Loan Trust 2018-E, Mortgage-Backed Securities, Series 2018-E, by U.S. Bank National Association, as Indenture Trustee'),
(604, 'U.S. Bank National Association, as Indenture Trustee on behalf of and with respect to Ajax Mortgage Loan Trust 2018-E, Mortgage-Backed Securities, Series 2018-E'),
(605, 'Ajax Mortgage Loan Trust 2018-F, Mortgage-Backed Securities, Series 2018-F, by U.S. Bank National Association, as Indenture Trustee'),
(605, 'U.S. Bank National Association, as Indenture Trustee on behalf of and with respect to Ajax Mortgage Loan Trust 2018-F, Mortgage-Backed Securities, Series 2018-F'),
(606, 'U.S. Bank National Association, as Indenture Trustee on behalf of and with respect to Ajax Mortgage Loan Trust 2018-F, Mortgage-Backed Securities, Series 2018-F'),
(608, 'Ajax Mortgage Loan Trust 2019-B, Mortgage-Backed Securities, Series 2019-B, by U.S. Bank National Association, as Indenture Trustee'),
(607, 'Ajax Mortgage Loan Trust 2019-A, Mortgage-Backed Securities, Series 2019-A, by U.S. Bank National Association, as Indenture Trustee'),
(607, 'U.S. Bank National Association, as Indenture Trustee on behalf of and with respect to Ajax Mortgage Loan Trust 2019-A, Mortgage-Backed Securities, Series 2019-A'),
(609, 'Ajax Mortgage Loan Trust 2019-C, Mortgage-Backed Securities, Series 2019-C, by U.S. Bank National Association, as Indenture Trustee'),
(609, 'U.S. Bank National Association, as Indenture Trustee on behalf of and with respect to Ajax Mortgage Loan Trust 2019-C, Mortgage-Backed Securities, Series 2019-C'),
(561, 'Ajax Mortgage Loan Trust 2019-D, Mortgage-Backed Securities, Series 2019-D, by U.S. Bank National Association, as Indenture Trustee'),
(561, 'U.S. Bank National Association, as Indenture Trustee on behalf of and with respect to Ajax Mortgage Loan Trust 2019-D, Mortgage-Backed Securities, Series 2019-D')


DECLARE @reportingDate date = '5/31/2019',
        @ArchiveDate date = '6/30/2019';

WITH RptPop AS
(
    SELECT 
     la.Account [LOAN_NBR]
    ,la.PmtPI [SCHED_P&I]
    ,la.NoteRate [NOTE_INT_RATE]
    ,la.NextDueDate [BORR_NEXT_PAY_DUE_DATE]
    ,la.PaidToDate [PIF_DATE]
    ,s.ActlBegPrinBal [ACTL_BEG_PRIN_BAL]
    ,s.SchedBegPrinBal [SCHED_BEG_PRIN_BAL]
    ,s.BeginNonInterestBearingDeferredPrincipal [BEGIN_NON_INTEREST_BEARING_DEFERRED_PRINCIPAL]
    ,s.Begin3rdPtyEscAdvanceBal [BEGIN_3RD_PTY_ESC_ADVANCE_BAL]
    ,s.BeginServicingEscAdvanceBal [BEGIN_SERVICING_ESC_ADVANCE_BAL]
    ,s.SchedGrossInterestAmt [SCHED_GROSS_INTEREST_AMT]
    ,s.NetIntRate [NET_INT_RATE]
    ,s.SchedNetInt [SCHED_NET_INT]
    ,s.ActlNetInt [ACTL_NET_INT]
    ,s.ActlEndPrinBal [ACTL_END_PRIN_BAL]
    ,s.SchedPrinamt [SCHED_PRIN_AMT]
    ,s.SchedEndPrinBal [SCHED_END_PRIN_BAL]
    ,s.LoanLossAmt [LOAN_LOSS_AMT]
    ,s.ActualEndingBalanceTotalDebtOwed [ACTUAL_ENDING_BALANCE_TOTAL_DEBT_OWED]
    ,s.EndingNonInterestBearingDeferredPrincipalBal [ENDING_NON_INTEREST_BEARING_DEFERRED_BAL]
    ,s.End3rdPtyEscAdvanceBal [END_3RD_PTY_ESC_ADVANCE_BAL]
    ,s.EndEscrowAdvanceBal [END_ESCROW_ADVANCE_BAL]
FROM esp.MARS_DW.dbo.vw_Loans_Archive la
LEFT JOIN esp.Securitization.dbo.SecuritizationReporting s on s.LoanNbr = la.Account
JOIN #mappings mp on mp.NoteOwner = la.NoteOwner
WHERE 
    la.ArchiveDate = @ArchiveDate 
    AND s.ReportingDate = @reportingDate
    --AND la.NoteOwner LIKE '%2018-B%' 
)

,
WITH thirdpartyEscAdvRepayments AS
(
SELECT
a.name [Account]
,ac.AccountNo
,le.LocationID
,act.Code
,dd.Date
,CASE WHEN EntryType = -1 THEN fle.AMOUNT ELSE 0 END AS CreditAmount
,CASE WHEN EntryType = 1 THEN fle.AMOUNT ELSE 0 END AS DebitAmount
FROM DWH.GL.FactLedgerEntry fle WITH(NOLOCK)
JOIN DWH.GL.DimJournals j WITH (NOLOCK)
    ON  j.JournalID = fle.JournalID
JOIN DWH.GL.DimAssets a WITH (NOLOCK)
    ON  a.AssetID = fle.AssetID
JOIN DWH.GL.DimAccounts ac WITH(NOLOCK)
    ON  ac.AccountID = fle.AccountID
JOIN DWH.GL.DimLocationEntity le WITH(NOLOCK)
    ON  le.LocationEntityID = fle.LocationID
JOIN DWH.GL.DimActivity act WITH(NOLOCK)
    ON  act.ActivityID = fle.ActivityID
JOIN DWH.dbo.DimDate dd WITH (NOLOCK)
    ON  dd.DateID = fle.BatchDateID
JOIN MARS.dbo.vw_Loans vl WITH(NOLOCK)
    ON  vl.Account = a.Name
WHERE   fle.EntryState = 'Posted'
)


SELECT

*


FROM 

RptPop 

LEFT JOIN thirdpartyEscAdvRepayments on thirdpartyEscAdvRepayments.Account = RptPop.Account

I am getting this error:

Msg 156, Level 15, State 1, Line 82
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 82
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

I have no idea how to fix this or why I am getting this error, please let me know what I can do to fix this. It seems I need a semicolon somewhere but for the life of me I cant figure out where or why. Any suggestions would be really appreciated. The error message does not really give clear indication as to where I need to fix the problem.

Advertisement

Answer

your code need like below, so you have to remove 2nd with

with RptPop  as
(your code
), thirdpartyEscAdvRepayments as
 ( your code

 ) select -----
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement