Parameters not working
Dear All,
We had developed the query by using union. when i am tring to run that query by giving some parametrs then it gives error that incorrect syntax near the word from. but when instead of those parameters if i give direct filter like Refdate >= '07.01.08' and Refdate >= '07.31.08' ..... then it fetches the correct result. the query is as below
SELECT FINAL. * FROM (
SELECT distinct (A.TRANSID) AS [JE Number], B.MEMO,
(D.CARDCODE) as [Business Partner], B.REFDATE, (B.BASEREF) as [Invoice Number],
case
when isnull(A.credit,0)=0 then a.debit
when isnull(a.debit,0)=0 then a.credit
end as [Vat Amount],
E.SEGMENT_3, A.VATGROUP
FROM JDT1 A LEFT OUTER JOIN OJDT B
ON A.TRANSID=B.TRANSID
LEFT OUTER JOIN OINV C
ON B.BASEREF=C.DOCNUM
LEFT OUTER JOIN OCRD D
ON C.CARDCODE=D.CARDCODE
RIGHT OUTER JOIN OACT E
ON D.DEBPAYACCT=E.ACCTCODE
LEFT OUTER JOIN OASC F
ON F.CODE=E.SEGMENT_3
WHERE B.TRANSTYPE <> 14 AND B.TRANSTYPE <> 24 AND B.TRANSTYPE <> 18 AND B.TRANSTYPE <> 19 AND B.TRANSTYPE <> 46
UNION
SELECT distinct (A.TRANSID) AS [JE Number], B.MEMO,
(D.CARDCODE) as [Business Partner], B.REFDATE, (B.BASEREF) as [Invoice Number],
case
when isnull(A.credit,0)=0 then a.debit
when isnull(a.debit,0)=0 then a.credit
end as [Vat Amount],
E.SEGMENT_3, A.VATGROUP
FROM JDT1 A LEFT OUTER JOIN OJDT B
ON A.TRANSID=B.TRANSID
LEFT OUTER JOIN ORIN C
ON B.BASEREF=C.DOCNUM
LEFT OUTER JOIN OCRD D
ON C.CARDCODE=D.CARDCODE
RIGHT OUTER JOIN OACT E
ON D.DEBPAYACCT=E.ACCTCODE
LEFT OUTER JOIN OASC F
ON F.CODE=E.SEGMENT_3
WHERE B.TRANSTYPE <> 13 AND B.TRANSTYPE <> 24 AND B.TRANSTYPE <> 18 AND B.TRANSTYPE <> 19 AND B.TRANSTYPE <> 46
UNION
SELECT distinct (A.TRANSID) AS [JE Number], B.MEMO,
(D.CARDCODE) as [Business Partner], B.REFDATE, (B.BASEREF) as [Invoice Number],
case
when isnull(A.credit,0)=0 then a.debit
when isnull(a.debit,0)=0 then a.credit
end as [Vat Amount],
E.SEGMENT_3, A.VATGROUP
FROM JDT1 A LEFT OUTER JOIN OJDT B
ON A.TRANSID=B.TRANSID
LEFT OUTER JOIN OPCH C
ON B.BASEREF=C.DOCNUM
LEFT OUTER JOIN OCRD D
ON C.CARDCODE=D.CARDCODE
RIGHT OUTER JOIN OACT E
ON D.DEBPAYACCT=E.ACCTCODE
LEFT OUTER JOIN OASC F
ON F.CODE=E.SEGMENT_3
WHERE B.TRANSTYPE <> 19 AND B.TRANSTYPE <> 24 AND B.TRANSTYPE <> 13 AND B.TRANSTYPE <> 14 AND B.TRANSTYPE <> 46
UNION
SELECT distinct (A.TRANSID) AS [JE Number], B.MEMO,
(D.CARDCODE) as [Business Partner], B.REFDATE, (B.BASEREF) as [Invoice Number],
case
when isnull(A.credit,0)=0 then a.debit
when isnull(a.debit,0)=0 then a.credit
end as [Vat Amount],
E.SEGMENT_3, A.VATGROUP
FROM JDT1 A LEFT OUTER JOIN OJDT B
ON A.TRANSID=B.TRANSID
LEFT OUTER JOIN ORPC C
ON B.BASEREF=C.DOCNUM
LEFT OUTER JOIN OCRD D
ON C.CARDCODE=D.CARDCODE
RIGHT OUTER JOIN OACT E
ON D.DEBPAYACCT=E.ACCTCODE
LEFT OUTER JOIN OASC F
ON F.CODE=E.SEGMENT_3
WHERE B.TRANSTYPE <> 18 AND B.TRANSTYPE <> 24 AND B.TRANSTYPE <> 13 AND B.TRANSTYPE <> 14 AND B.TRANSTYPE <> 46
) FINAL
WHERE RefDate >=[%0] and RefDate <=[%1] and Segment_3 =[%2] and vatgroup =[%3]
Please help me as soon as possible.
Thanks
Tavargeri Ganesh