Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Error in CTE Expression

Answered Error in CTE Expression

  • Wednesday, February 06, 2013 5:26 AM
     
     
    Sign In to Vote
    0

    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

     

All Replies

  • Wednesday, February 06, 2013 5:32 AM
     
     Proposed
    Sign In to Vote
    0
    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:36 AM
     
     Answered Has Code
    Sign In to Vote
    0

    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.


     
  • Wednesday, February 06, 2013 5:44 AM
     
     
    Sign In to Vote
    0

    Msg 102, Level 15, State 1, Line 24

    Incorrect syntax near ')'.

    is the new error

     
  • Wednesday, February 06, 2013 5:53 AM
     
     
    Sign In to Vote
    0

    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:59 AM
     
     
    Sign In to Vote
    0
    yes its working thankyou very much