Skip to Content
0
Jan 23, 2013 at 01:58 PM

Query Error

188 Views

Hello,

I have created the following query to get a report of the sales and purchase for the specified items and it works

Select * from (

SELECT T0.DocNum, T2.[WhsName], T0.DocDate,'Invoice' as 'Type',T1.[ItemCode], T1.[Dscription]

,T1.[LineTotal]-T1.[LineTotal]/( T0.[DocTotal]+T0.[DiscSum])*T0.[DiscSum] as 'Amount'

FROM OINV T0

INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OWHS T2 ON T1.[WhsCode]= T2.WhsCode

WHERE T1.[ItemCode] in ('VCDM','VOXM','VOXE')

-- and T0.docdate >= [%0] and T0.docdate <= [%1]

Union All

SELECT T0.DocNum, T2.[WhsName], T0.DocDate,'Expenses' as 'Type', T1.[ItemCode], T1.[Dscription]

,-(T1.[LineTotal]-T1.[LineTotal]/( T0.[DocTotal]+T0.[DiscSum])*T0.[DiscSum]) as 'Amount'

FROM OPCH T0

INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OWHS T2 ON T1.WhsCode = T2.WhsCode

WHERE T1.[ItemCode] in ('VCDM','VOXM','VOXE')

-- and T0.docdate >= [%0] and T0.docdate <= [%1]

) tbl

ORDER BY tbl.[WhsName], tbl.[ItemCode]

When I add the commented line of code "and T0.docdate >= [%0] and T0.docdate <= [%1] " , I get the following error:

1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near 'This'.

2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement 'Received Alerts' (OAIB) (s) could not be prepared.

any help is appreciated.

Thanks,

Alaa