Skip to Content
0
May 15, 2008 at 07:55 AM

Complex QUERY with Variables

2655 Views

I created a complex query to extract data from Journal entry and from Journal voucher. I post here a simplified version where I have deleted UDO references.

I buit it in the Query analizer and it runs ok.

I copied in SAP and it runs ok too.

Now, I need to ask for the start date and end date, as in the first lines of script. But SAP raise an error message I dont understand. I tried to use '[%0]' and '[%1]' insthead of '2008/01/01' and '2008/02/29'

DECLARE @D1 DATETIME
DECLARE @D2 DATETIME

SET @D1='2008/01/01'
SET @D2='2008/02/29'

SELECT * FROM
(
SELECT   
	A.ACCOUNT AS 'CO.GE ITALIA',
	OACT.ACCTNAME AS 'NOME CO.GE ITALIA',
	SUM(CASE WHEN (A.ACCOUNT>='500000' AND A.ACCOUNT<'600000' AND DEBIT<>0) THEN -DEBIT WHEN (A.ACCOUNT>='600000' AND A.ACCOUNT<'700000')THEN 0 ELSE CREDIT END - 
		CASE WHEN (A.ACCOUNT>='500000' AND A.ACCOUNT<'600000') THEN 0 WHEN (A.ACCOUNT>='600000' AND A.ACCOUNT<'700000' AND CREDIT<>0)THEN -CREDIT ELSE DEBIT END) AS 'TOTALE'
FROM (
SELECT BTF1.* FROM BTF1
UNION ALL
SELECT JDT1.* FROM JDT1
) AS A 
LEFT JOIN OACT ON (OACT.ACCTCODE=A.ACCOUNT)
LEFT JOIN OBTF ON (OBTF.BATCHNUM=A.BATCHNUM AND OBTF.TRANSID=A.TRANSID)
WHERE A.REFDATE>=@D1 AND A.REFDATE<=@D2 AND (OBTF.BTFSTATUS<>'C' OR OBTF.BTFSTATUS IS NULL) AND (OACT.U_CUENTA<>'' AND OACT.U_CUENTA IS NOT NULL) AND (OACT.U_CUENTA<>'432000000')
GROUP BY A.ACCOUNT, OACT.ACCTNAME
UNION ALL
SELECT   
	A.SHORTNAME AS 'CO.GE ITALIA',
	OCRD.CARDNAME AS 'NOME CO.GE ITALIA',
	SUM(CASE WHEN (A.ACCOUNT>='500000' AND A.ACCOUNT<'600000' AND DEBIT<>0) THEN -DEBIT WHEN (A.ACCOUNT>='600000' AND A.ACCOUNT<'700000')THEN 0 ELSE CREDIT END - 
		CASE WHEN (A.ACCOUNT>='500000' AND A.ACCOUNT<'600000') THEN 0 WHEN (A.ACCOUNT>='600000' AND A.ACCOUNT<'700000' AND CREDIT<>0)THEN -CREDIT ELSE DEBIT END) AS 'TOTALE'
FROM (
SELECT BTF1.* FROM BTF1
UNION ALL
SELECT JDT1.* FROM JDT1
) AS A 
LEFT JOIN OACT ON (OACT.ACCTCODE=A.ACCOUNT)
LEFT JOIN OBTF ON (OBTF.BATCHNUM=A.BATCHNUM AND OBTF.TRANSID=A.TRANSID)
LEFT JOIN OCRD ON (OCRD.CARDCODE=A.SHORTNAME)
WHERE A.REFDATE>=@D1 AND A.REFDATE<=@D2 AND (OBTF.BTFSTATUS<>'C' OR OBTF.BTFSTATUS IS NULL) AND (OACT.U_CUENTA<>'' AND OACT.U_CUENTA IS NOT NULL) AND (OACT.U_CUENTA='432000000')
GROUP BY A.SHORTNAME, OCRD.CARDNAME
) AS B