none
Error in CTE Expression

    Question

  • I have created A CTE expression but error occured. Below is my expression

    DECLARE @startdate datetime, @enddate datetime, @ydate datetime
    SET @startdate = '2013-01-01 00:00:00.000' --Created date in Ariba
    SET @enddate = '2013-01-01 23:59:59.998' --Created date in Ariba
    SET @ydate = @startdate --Modified date in Ariba
    With CTE_Test (FinanceApprover,ARapprover,InvoiceName,CompanyCode,InvoiceType)
    As
    (
    Select
    FRAppr.APPROVER,
    AR.ar_Approver,
    App.ap_UniqueName,
    v3_1.zcoc_UniqueName,
    InvT1.aaiit_Acc_UniqueName

    FROM  ariba_user.v3_Acc_AAInvoiceEformTab Eform WITH(NOLOCK)
    INNER JOIN ariba_user.ApprovableTab App WITH(NOLOCK) ON (Eform.rootId = App.rootId)
    INNER JOIN  ariba_user.ApprovalRequestTab AR (NOLOCK) ON (App.rootId = AR.rootId)
    INNER JOIN ariba_user.v3_CompanyCodeTab v3_1 ON (Eform.aainv_Acc_CompanyCode = v3_1.rootId) 
    INNER JOIN ariba_user.v3_Acc_AAInvoiceTypeTab InvT1 ON (Eform.aainv_Acc_InvoiceType = InvT1.rootId)
    LEFT OUTER JOIN ariba_user.v3_Acc_AASubStatusTab SS ON (Eform.aainv_Acc_AASubStatus = SS.rootId)
    LEFT OUTER JOIN ARIBA_USER.ARB_AAFINANCEREVIEWER_VW FRAppr WITH (NOLOCK) ON (App.ap_UniqueName = FRAppr.INVOICE_NUMBER)
    WHERE AR.ar_Acc_AAType = 'Acc_AAFinanceReview' AND SS.aaiss_UniqueName not in ('CompletedManual','CompletedAutomatic','DeniedCompleted')
    AND dateadd(hh,-6,App.ap_TimeCreated) between @startdate and @enddate AND App.ap_Active = '1'
    AND v3_1.zcoc_UniqueName NOT IN ('8101')
    )
         
    select FinanceApprover,ARapprover,InvoiceName from CTE_Test

    error is

    Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

    what is the error in my expression

    Wednesday, February 06, 2013 5:26 AM

Answers

  • Try the below:

    DECLARE @startdate datetime, @enddate datetime, @ydate datetime SET @startdate = '2013-01-01 00:00:00.000' --Created date in Ariba SET @enddate = '2013-01-01 23:59:59.998' --Created date in Ariba SET @ydate = @startdate --Modified date in Ariba ;With CTE_Test (FinanceApprover,ARapprover,InvoiceName,CompanyCode,InvoiceType) As ( Select FRAppr.APPROVER, AR.ar_Approver, App.ap_UniqueName, v3_1.zcoc_UniqueName, InvT1.aaiit_Acc_UniqueName FROM ariba_user.v3_Acc_AAInvoiceEformTab Eform WITH(NOLOCK) INNER JOIN ariba_user.ApprovableTab App WITH(NOLOCK) ON (Eform.rootId = App.rootId) INNER JOIN ariba_user.ApprovalRequestTab AR (NOLOCK) ON (App.rootId = AR.rootId) INNER JOIN ariba_user.v3_CompanyCodeTab v3_1 ON (Eform.aainv_Acc_CompanyCode = v3_1.rootId) INNER JOIN ariba_user.v3_Acc_AAInvoiceTypeTab InvT1 ON (Eform.aainv_Acc_InvoiceType = InvT1.rootId) LEFT OUTER JOIN ariba_user.v3_Acc_AASubStatusTab SS ON (Eform.aainv_Acc_AASubStatus = SS.rootId) LEFT OUTER JOIN ARIBA_USER.ARB_AAFINANCEREVIEWER_VW FRAppr WITH (NOLOCK) ON (App.ap_UniqueName = FRAppr.INVOICE_NUMBER) WHERE AR.ar_Acc_AAType = 'Acc_AAFinanceReview' AND SS.aaiss_UniqueName not in ('CompletedManual','CompletedAutomatic','DeniedCompleted') AND dateadd(hh,-6,App.ap_TimeCreated) between @startdate and @enddate AND App.ap_Active = '1' AND v3_1.zcoc_UniqueName NOT IN ('8101') ) select FinanceApprover,ARapprover,InvoiceName from CTE_Test



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Proposed as answer by SqlShan Wednesday, February 06, 2013 5:41 AM
    • Edited by Latheesh NKMVP Wednesday, February 06, 2013 5:52 AM
    • Marked as answer by Naomi NModerator Thursday, February 07, 2013 3:44 AM
    Wednesday, February 06, 2013 5:36 AM

All replies

  • Put a semi column (;) before with clause.

    sarat chandra sahoo

    • Proposed as answer by Rishabh K Wednesday, February 06, 2013 5:45 AM
    Wednesday, February 06, 2013 5:32 AM
  • Try the below:

    DECLARE @startdate datetime, @enddate datetime, @ydate datetime SET @startdate = '2013-01-01 00:00:00.000' --Created date in Ariba SET @enddate = '2013-01-01 23:59:59.998' --Created date in Ariba SET @ydate = @startdate --Modified date in Ariba ;With CTE_Test (FinanceApprover,ARapprover,InvoiceName,CompanyCode,InvoiceType) As ( Select FRAppr.APPROVER, AR.ar_Approver, App.ap_UniqueName, v3_1.zcoc_UniqueName, InvT1.aaiit_Acc_UniqueName FROM ariba_user.v3_Acc_AAInvoiceEformTab Eform WITH(NOLOCK) INNER JOIN ariba_user.ApprovableTab App WITH(NOLOCK) ON (Eform.rootId = App.rootId) INNER JOIN ariba_user.ApprovalRequestTab AR (NOLOCK) ON (App.rootId = AR.rootId) INNER JOIN ariba_user.v3_CompanyCodeTab v3_1 ON (Eform.aainv_Acc_CompanyCode = v3_1.rootId) INNER JOIN ariba_user.v3_Acc_AAInvoiceTypeTab InvT1 ON (Eform.aainv_Acc_InvoiceType = InvT1.rootId) LEFT OUTER JOIN ariba_user.v3_Acc_AASubStatusTab SS ON (Eform.aainv_Acc_AASubStatus = SS.rootId) LEFT OUTER JOIN ARIBA_USER.ARB_AAFINANCEREVIEWER_VW FRAppr WITH (NOLOCK) ON (App.ap_UniqueName = FRAppr.INVOICE_NUMBER) WHERE AR.ar_Acc_AAType = 'Acc_AAFinanceReview' AND SS.aaiss_UniqueName not in ('CompletedManual','CompletedAutomatic','DeniedCompleted') AND dateadd(hh,-6,App.ap_TimeCreated) between @startdate and @enddate AND App.ap_Active = '1' AND v3_1.zcoc_UniqueName NOT IN ('8101') ) select FinanceApprover,ARapprover,InvoiceName from CTE_Test



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Proposed as answer by SqlShan Wednesday, February 06, 2013 5:41 AM
    • Edited by Latheesh NKMVP Wednesday, February 06, 2013 5:52 AM
    • Marked as answer by Naomi NModerator Thursday, February 07, 2013 3:44 AM
    Wednesday, February 06, 2013 5:36 AM
  • Msg 102, Level 15, State 1, Line 24

    Incorrect syntax near ')'.

    is the new error

    Wednesday, February 06, 2013 5:44 AM
  • Try the above code. I gues you didnt copy the select query from the snippet.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, February 06, 2013 5:53 AM
  • yes its working thankyou very much
    Wednesday, February 06, 2013 5:59 AM