SQL RULE on SAP BOFC 10.0 / SQL SERVER 2012

[Former Member]

Hello

Can you help me on a SAP migration SAP BOFC 7.5 at 10.0

I have a syntax error (file in attachment) for this sql Rule in SAP BOFC 10.0 on SQL server 2012 (this rule is OK on SAP BOFC 7.5 on sql server 2005)

Thanks

/* ------------------------------------------------------------------------------ */

/* This stored procedure is used to rollup accounts into a hierarchy based on the */

/* Chart of Accounts Sub-Total column (c_sstot) in the waccount table              */

/* ------------------------------------------------------------------------------ */

DECLARE @detaildim  varchar(255);

DECLARE @cursor  varchar(255);

CREATE TABLE [WORKTABLE]_total (accnt int, total int,sign int);

CREATE TABLE [WORKTABLE]_total1 (accnt int, total int,sign int);

CREATE TABLE [WORKTABLE]_total2 (accnt int, total int,sign int);

INSERT INTO [WORKTABLE]_total (accnt,total,sign) SELECT id,ct_0000_rollifrs,1 FROM ct_account

WHERE ct_0000_rollifrs!=0 and id not in (select distinct ct_0000_rollifrs from ct_account where ct_0000_rollifrs is not null);

INSERT INTO [WORKTABLE]_total1 SELECT * FROM [WORKTABLE]_total;

WHILE @@rowcount > 0

BEGIN

INSERT [WORKTABLE]_total2 SELECT a.accnt, b.ct_0000_rollifrs,1 FROM [WORKTABLE]_total1 a, ct_account b

WHERE a.total = b.id and  b.ct_0000_rollifrs != 0 and b.ct_0000_rollifrs is not null

INSERT [WORKTABLE]_total SELECT * FROM [WORKTABLE]_total2

DELETE  [WORKTABLE]_total1

INSERT [WORKTABLE]_total1 SELECT * FROM [WORKTABLE]_total2

DELETE [WORKTABLE]_total2

END;

UPDATE [WORKTABLE]_total SET sign=-1 FROM [WORKTABLE]_total t, ct_account w1, ct_account w2

WHERE t.accnt=w1.id and t.total=w2.id and w1.sign != w2.sign;

CREATE UNIQUE INDEX i on [WORKTABLE]_total(accnt,total);

DELETE FROM [WORKTABLE] WHERE accnt in (SELECT DISTINCT ct_0000_rollifrs FROM ct_account where ct_0000_rollifrs is not null);

/* Remove the TOP value from Total for grand total */

EXEC('INSERT INTO [WORKTABLE] (entity,entorig,curncy,accnt,flow,nature,period,partner,ctshare,enumber,techorig,globorig,journal,

amount,convamount,convamount2,consamount,rollup,rollup_partner,data_comment,

ct_0000_tftr, ct_0000_dest, ct_0000_zone, ct_0000_t3, ct_0000_rappro, ct_0000_det, ct_mu)

SELECT

entity,entorig,curncy,r.total,flow,nature,period,partner,ctshare,enumber,techorig,globorig,journal,

sum(amount*r.sign),sum(convamount*r.sign),sum(convamount2*r.sign),sum(consamount*r.sign),rollup,rollup_partner,data_comment,

ct_0000_tftr, ct_0000_dest, ct_0000_zone, ct_0000_t3,ct_0000_rappro, ct_0000_det, ct_mu

FROM [WORKTABLE] w,  [WORKTABLE]_total r

WHERE w.accnt = r.accnt

AND ct_0000_tftr = 0

GROUP BY entity,entorig,curncy,r.total,flow,nature,period,partner,ctshare,enumber,techorig,globorig,journal,

rollup,rollup_partner,data_comment,

ct_0000_tftr , ct_0000_dest, ct_0000_zone, ct_0000_t3,ct_0000_rappro, ct_0000_det, ct_mu ');

DROP TABLE [WORKTABLE]_total;

DROP TABLE [WORKTABLE]_total1;

DROP TABLE [WORKTABLE]_total2;

[Olj]

Hi Jalil,

can you help me? Where I can take infrormation about SQL rules(functions)?

We didn't use SQL rules before, or may be you tell a little about the basics?

Thanks ...

[Former Member]

The error message is

Incorrect syntax near ','