Query not working

[former_member645084]

Hi

Error - 1). [Microsoft][SQL Server Native Client 11.0][SQL Server]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  2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ','. 3). [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement 'User-Defined Values' (CSHS) (s) could not be prepared.



/* SELECT FROM [dbo].[OWHS] T0 */
DECLARE @Whse AS nvarchar(10)
/* WHERE */
SET @Whse = /* T0.WhsCode */ '[%0]'

with CTE_A1 as
(
    Select 
        A.BatchNum as Batchnum,B.ItemCode as ItemCode,
        B.ItemName as ItemName,A.WhsCode as Whscode,(Select AvgPrice from OITW where ItemCode = A.ItemCode And WhsCode = A.WhsCode) as Price,
        (Select InDate from OBTN where DistNumber = A.BatchNum and ItemCode = b.ItemCode) as DocDate
    from IBT1 A
    Inner Join OITM B on A.ItemCode = B.ItemCode
    where A.WhsCode =  @Whse
)
select * from cte_A1

Thanks

[msundararaja_perumal · Accepted solution]

Hello,

The parameter fetching has issue your query try the below:

DECLARE @Whse AS nvarchar(10)

SET @Whse =  (Select  A.WhsCode From OWHS A Where A.WhsCode='[%0]') 

Select   A.BatchNum as Batchnum,B.ItemCode as ItemCode,  B.ItemName as ItemName,A.WhsCode as Whscode,(Select AvgPrice from OITW where ItemCode = A.ItemCode And WhsCode = A.WhsCode) as Price,  (Select InDate from OBTN where DistNumber = A.BatchNum and ItemCode = b.ItemCode) as DocDate  from IBT1 A  Inner Join OITM B on A.ItemCode = B.ItemCode  where A.WhsCode =  @Whse

Thanks.

[former_member645084 · Accepted solution]

Hi Sundararaja

I have to use CTE . With that it is giving error

Thanks

[msundararaja_perumal · Accepted solution]

jsshivalik ,

CTE is not supported in query manager, you have to create as procedure with parameter and call the procedure in SAP.

Thanks.