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