Retained Earnings Report option is 1
Work Period = S-1
Reporting Period = 5

rowsetid: 17
next rowcalc = 100 TO 250 "#,##0.00;(#,##0.00);0.00"
calcwork was with quote: +100 TO 250 "#,##0.00;(#,##0.00);0.00"
calcwork now with quote:+100 TO 250
top loop calcwork: +100 TO 250
+100 TO 250
counter 1
check calcwork +100 TO 250
reset calcwork to rowcalc +100 TO 250
top loop calcwork: +100 TO 250
+100 TO 250
counter 2
check calcwork +100 TO 250
reset calcwork to rowcalc +100 TO 250
top loop calcwork: +100 TO 250
+100 TO 250
counter 3
check calcwork +100 TO 250
TO From Row 100
TO Thru Row 250
TO Oper +
calcwork was +100 TO 250
totext = 100 TO 250
patptr = 2
worklen = 10
just set calcwork to
top loop calcwork:

counter 3
check calcwork
reset calcwork to rowcalc +100 TO 250
top loop calcwork: +100 TO 250
+100 TO 250
counter 4
check calcwork +100 TO 250
getting word
FromRow
100
@PatPtr = 1
@Position = 1
top loop calcwork: ~100 TO 250
~100 TO 250
counter 4
check calcwork ~100 TO 250
reset calcwork to rowcalc +100 TO 250
top loop calcwork: +100 TO 250
+100 TO 250
counter 5
check calcwork +100 TO 250
reset calcwork to rowcalc +100 TO 250
top loop calcwork: +100 TO 250
+100 TO 250
counter 6
check calcwork +100 TO 250
reset calcwork to rowcalc +100 TO 250
top loop calcwork: +100 TO 250
+100 TO 250
counter 7
check calcwork +100 TO 250
reset calcwork to rowcalc +100 TO 250
top loop calcwork: +100 TO 250
+100 TO 250
counter 8
check calcwork +100 TO 250
reset calcwork to rowcalc +100 TO 250
top loop calcwork: +100 TO 250
+100 TO 250
counter 9
check calcwork +100 TO 250
reset calcwork to rowcalc +100 TO 250
end rowcalc_cursor
update caltab 2
sqlstat = create statistics stat1 on #linked_gl(glnum,glrc1,seg1)
index1 = create index idx1 on #linked_gl(seg1,glnum,glrc1)
index2 = create index idx2 on #linked_gl(glnum,glrc1,seg1)
SEGSQL: update #k1 set filter01 = substring(linklow,1,4),filterh01 = substring(linkhigh,1,4) ,filter02 = substring(linklow,5,2),filterh02 = substring(linkhigh,5,2) ,filter03 = substring(linklow,7,4),filterh03 = substring(linkhigh,7,4) ,filter04 = substring(linklow,11,4),filterh04 = substring(linkhigh,11,4) ,filter05 = substring(linklow,15,4),filterh05 = substring(linkhigh,15,4)
UPDATE #glseg SQL:INSERT INTO #glseg select node,parentnode as parent,parentrefid,leaf,segnum,rownum,colnum,negate,crbal,nozero,modifier,dbonly,cronly,glnum,glrc1,suppress_round,bookcode from #linked_gl join #k1 on 1=1  and isnull(query,'') = '' and LTRIM(RTRIM(case when segnum = 0 then glnum collate database_default  when segnum = 1 then seg1 collate database_default when segnum = 2 then seg2 collate database_default when segnum = 3 then seg3 collate database_default when segnum = 4 then seg4 collate database_default when segnum = 5 then seg5 collate database_default end )) like replace(linklow,'?','_') collate database_default where len(linkhigh) = 0 union all select node,parentnode as parent,parentrefid,leaf,segnum,rownum,colnum,negate,crbal,nozero,modifier,dbonly,cronly,glnum,glrc1,suppress_round,bookcode from #linked_gl join #k1 on 1=1  and isnull(query,'') = ''and seg1 collate database_default between replace(filter01,'?',' ') collate database_default  and replace(filterh01,'?','z') collate database_default and seg2 collate database_default = replace(filter02,'?',' ') collate database_default and seg3 collate database_default = replace(filter03,'?',' ') collate database_default and seg4 collate database_default = replace(filter04,'?',' ') collate database_default and seg5 collate database_default = replace(filter05,'?',' ') collate database_default  where len(linkhigh) > 0  and isnull(query,'') = '' and segnum = 0 union all select node,parentnode as parent,parentrefid,leaf,segnum,rownum,colnum,negate,crbal,nozero,modifier,dbonly,cronly,glnum,glrc1,suppress_round,bookcode from #linked_gl join #k1 on 1=1  and isnull(query,'') = '' and case  when segnum = 1 then seg1 collate database_default when segnum = 2 then seg2 collate database_default when segnum = 3 then seg3 collate database_default when segnum = 4 then seg4 collate database_default when segnum = 5 then seg5 collate database_default end between replace(linklow,'?',' ') collate database_default and replace(linkhigh,'?','z')  collate database_default where len(linkhigh) > 0  and isnull(query,'') = '' and segnum <> 0
AFTER COLMODE #glseg SQL:INSERT INTO #glseg select node,parentnode as parent,parentrefid,leaf,segnum,rownum,colnum,negate,crbal,nozero,modifier,dbonly,cronly,glnum,glrc1,suppress_round,bookcode from #linked_gl join #k1 on 1=1  and isnull(query,'') = '' and LTRIM(RTRIM(case when segnum = 0 then glnum collate database_default  when segnum = 1 then seg1 collate database_default when segnum = 2 then seg2 collate database_default when segnum = 3 then seg3 collate database_default when segnum = 4 then seg4 collate database_default when segnum = 5 then seg5 collate database_default end )) like replace(linklow,'?','_') collate database_default where len(linkhigh) = 0 union all select node,parentnode as parent,parentrefid,leaf,segnum,rownum,colnum,negate,crbal,nozero,modifier,dbonly,cronly,glnum,glrc1,suppress_round,bookcode from #linked_gl join #k1 on 1=1  and isnull(query,'') = ''and seg1 collate database_default between replace(filter01,'?',' ') collate database_default  and replace(filterh01,'?','z') collate database_default and seg2 collate database_default = replace(filter02,'?',' ') collate database_default and seg3 collate database_default = replace(filter03,'?',' ') collate database_default and seg4 collate database_default = replace(filter04,'?',' ') collate database_default and seg5 collate database_default = replace(filter05,'?',' ') collate database_default  where len(linkhigh) > 0  and isnull(query,'') = '' and segnum = 0 union all select node,parentnode as parent,parentrefid,leaf,segnum,rownum,colnum,negate,crbal,nozero,modifier,dbonly,cronly,glnum,glrc1,suppress_round,bookcode from #linked_gl join #k1 on 1=1  and isnull(query,'') = '' and case  when segnum = 1 then seg1 collate database_default when segnum = 2 then seg2 collate database_default when segnum = 3 then seg3 collate database_default when segnum = 4 then seg4 collate database_default when segnum = 5 then seg5 collate database_default end between replace(linklow,'?',' ') collate database_default and replace(linkhigh,'?','z')  collate database_default where len(linkhigh) > 0  and isnull(query,'') = '' and segnum <> 0
RETURNING because if not exists(select * from #k1 where isnull(query,'') <> '')
ExecuteAccountSQL sql 1 = DECLARE glrange_list CURSOR FOR SELECT t0.* FROM #k1 AS t0

@CacheTable = NULL
Now @CacheTable = NULL
Base Period = 5
Base Year = 2019
Year Ind = BASE
Period Ind = BASE
Periodctl =
Returned ColPeriod: 5
Reporting Currency:
Conversion Method:
Currency Filter:
Column Period = 5
Column Year = 2019
Book = 1
Setdefid = 0
back from dfxPopEntityOpts
INSERT INTO #minmax SELECT MIN(fmsbegdt) AS begdt,MAX(fmsenddt) AS enddt FROM fmsper WHERE fmsyr = 2019                 AND fmsper = 5
Going to BuildColumnSQL BB for CURBB and Currency =  and Column = 1
Calling dfxfn_BuildColumnSQL
insert into #glcoldata select t0.masked_glnum,t0.gldes,t0.acctid,t0.seg1,t0.seg2,t0.seg3,t0.seg4,t0.seg5,t0.seg6,t0.seg7,t0.seg8,t0.seg9,t0.seg10,t1.node,t1.parent,t1.parentrefid,t1.leaf,t1.rownum,0,t1.nozero,1, isnull(round(sum(round(cast(t2.glcydb as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) *  (CASE WHEN t1.cronly = 1 THEN 0 ELSE 1 END), isnull(round(sum(round(cast(t2.glcycr as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) *  (CASE WHEN t1.dbonly = 1 THEN 0 ELSE 1 END), isnull(round((sum(round(cast(isnull(t2.glcydb,0) as decimal(16,4)),4))* (CASE WHEN t1.cronly = 1 THEN 0 ELSE 1 END))-sum(round(cast(t2.glcycr as decimal(16,4)),4))* (CASE WHEN t1.dbonly = 1 THEN 0 ELSE 1 END)* (CASE WHEN t1.cronly = 1 THEN -1 ELSE 1 END)+round(sum(round(cast(isnull(t2.glcybu,0) as decimal(16,4)),4)),4),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END)* (CASE WHEN t1.crbal = 1 THEN -1 ELSE 1 END), isnull(round(sum(round(cast(isnull(t2.glcybu,0) as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) * (CASE WHEN t1.crbal = 1 THEN -1 ELSE 1 END) ,t1.suppress_round from gl as t0 with (nolock)  join #trmethod on old_trmethod = t0.trmethod  join #glacctrow as t1 on t0.glnum = t1.glnum and t0.glrc1 = t1.glrc1 AND rownum <> 0 AND colnum = 0 AND modifier = '' left join #linked_glamt as t2 with (nolock) on t2.glnum = t1.glnum and t2.glrc1 = t1.glrc1 and glyr = 2019       and t2.gltype COLLATE DATABASE_DEFAULT IN (SELECT gltype COLLATE DATABASE_DEFAULT FROM df_dfx_bookcode AS j0 WHERE bookcode = '1' AND j0.gltype COLLATE DATABASE_DEFAULT = t2.gltype COLLATE DATABASE_DEFAULT) where 1=1 and t0.glcat IN ('A','C','L') and ( len(t1.bookcode) = 0 OR t1.bookcode IS NULL OR t1.bookcode = '1') group by t0.masked_glnum,t0.gldes,t0.acctid,t0.seg1,t0.seg2,t0.seg3,t0.seg4,t0.seg5,t0.seg6,t0.seg7,t0.seg8,t0.seg9,t0.seg10,t1.node,t1.parent,t1.parentrefid,t1.leaf,t1.rownum,t1.nozero,t1.negate,t1.crbal,t1.dbonly,t1.cronly,t1.suppress_round  having sum(glcydb) <> 0 or sum(glcycr) <> 0 or sum(glcybu) <> 0
Enterprise
Going to BuildColumnSQL for CURBB and Column = 1
insert into #glcoldata select t0.masked_glnum,t0.gldes,t0.acctid,t0.seg1,t0.seg2,t0.seg3,t0.seg4,t0.seg5,t0.seg6,t0.seg7,t0.seg8,t0.seg9,t0.seg10,t1.node,t1.parent,t1.parentrefid,t1.leaf,t1.rownum,0,t1.nozero,1, isnull(round(sum(round(cast(t2.glcydb as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) *  (CASE WHEN t1.cronly = 1 THEN 0 ELSE 1 END), isnull(round(sum(round(cast(t2.glcycr as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) *  (CASE WHEN t1.dbonly = 1 THEN 0 ELSE 1 END), isnull(round((sum(round(cast(isnull(t2.glcydb,0) as decimal(16,4)),4))* (CASE WHEN t1.cronly = 1 THEN 0 ELSE 1 END))-sum(round(cast(t2.glcycr as decimal(16,4)),4))* (CASE WHEN t1.dbonly = 1 THEN 0 ELSE 1 END)* (CASE WHEN t1.cronly = 1 THEN -1 ELSE 1 END)+round(sum(round(cast(isnull(t2.glcybu,0) as decimal(16,4)),4)),4),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END)* (CASE WHEN t1.crbal = 1 THEN -1 ELSE 1 END), isnull(round(sum(round(cast(isnull(t2.glcybu,0) as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) * (CASE WHEN t1.crbal = 1 THEN -1 ELSE 1 END) ,t1.suppress_round from gl as t0 with (nolock)  join #trmethod on old_trmethod = t0.trmethod  join #glacctrow as t1 on t0.glnum = t1.glnum and t0.glrc1 = t1.glrc1 AND rownum <> 0 AND colnum = 0 AND modifier = '' left join #linked_glamt as t2 with (nolock) on t2.glnum = t1.glnum and t2.glrc1 = t1.glrc1 and glyr = 2019       AND glper < 5                    and t2.gltype COLLATE DATABASE_DEFAULT IN (SELECT gltype COLLATE DATABASE_DEFAULT FROM df_dfx_bookcode AS j0 WHERE bookcode = '1' AND j0.gltype COLLATE DATABASE_DEFAULT = t2.gltype COLLATE DATABASE_DEFAULT) where 1=1 and ( len(t1.bookcode) = 0 OR t1.bookcode IS NULL OR t1.bookcode = '1') group by t0.masked_glnum,t0.gldes,t0.acctid,t0.seg1,t0.seg2,t0.seg3,t0.seg4,t0.seg5,t0.seg6,t0.seg7,t0.seg8,t0.seg9,t0.seg10,t1.node,t1.parent,t1.parentrefid,t1.leaf,t1.rownum,t1.nozero,t1.negate,t1.crbal,t1.dbonly,t1.cronly,t1.suppress_round  having sum(glcydb) <> 0 or sum(glcycr) <> 0 or sum(glcybu) <> 0
Warning: Null value is eliminated by an aggregate or other SET operation.
going to Process Excel
ProcessExcel:5                    Year: 2019       BalType: CUR/BB Base Period: 5 ColNum: 1 ColPeriod = 5
INSERT INTO #glcoldata (rownum,rowmod,nozero,colnum,debit,credit,balance,budget,acctid,node,parent,parentrefid,leaf) select b1.rownum,0,b1.nozero,1, case when cast(b0.cell as decimal(24,8)) > 0 then cast(b0.cell as decimal(24,8)) else 0 end,  case when cast(b0.cell as decimal(24,8)) < 0 then cast(b0.cell as decimal(24,8)) else 0 end, cast(b0.cell as decimal(24,8)) * (CASE WHEN b1.crbal = 1 THEN -1 ELSE 1 END),0,1, node,parentnode,parentrefid,leaf from #final_rules AS b0  join df_dfx_rowdtl AS b1 ON b0.rownum = b1.rownum AND b1.rowsetid = 17 where isnumeric(b0.cell) = 1 and b0.cell <> '-' AND b0.colnum-1 = 1

Base Period = 5
Base Year = 2019
Year Ind = BASE
Period Ind = BASE
Periodctl =
Returned ColPeriod: 5
Reporting Currency:
Conversion Method:
Currency Filter:
Column Period = 5
Column Year = 2019
Book = 1
Setdefid = 0
back from dfxPopEntityOpts
INSERT INTO #minmax SELECT MIN(fmsbegdt) AS begdt,MAX(fmsenddt) AS enddt FROM fmsper WHERE fmsyr = 2019                 AND fmsper = 5
Enterprise
Going to BuildColumnSQL for CUR and Column = 2
insert into #glcoldata select t0.masked_glnum,t0.gldes,t0.acctid,t0.seg1,t0.seg2,t0.seg3,t0.seg4,t0.seg5,t0.seg6,t0.seg7,t0.seg8,t0.seg9,t0.seg10,t1.node,t1.parent,t1.parentrefid,t1.leaf,t1.rownum,0,t1.nozero,2, isnull(round(sum(round(cast(t2.glcydb as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) *  (CASE WHEN t1.cronly = 1 THEN 0 ELSE 1 END), isnull(round(sum(round(cast(t2.glcycr as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) *  (CASE WHEN t1.dbonly = 1 THEN 0 ELSE 1 END), isnull(round((sum(round(cast(isnull(t2.glcydb,0) as decimal(16,4)),4))* (CASE WHEN t1.cronly = 1 THEN 0 ELSE 1 END))-sum(round(cast(t2.glcycr as decimal(16,4)),4))* (CASE WHEN t1.dbonly = 1 THEN 0 ELSE 1 END)* (CASE WHEN t1.cronly = 1 THEN -1 ELSE 1 END)+round(sum(round(cast(isnull(t2.glcybu,0) as decimal(16,4)),4)),4),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END)* (CASE WHEN t1.crbal = 1 THEN -1 ELSE 1 END), isnull(round(sum(round(cast(isnull(t2.glcybu,0) as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) * (CASE WHEN t1.crbal = 1 THEN -1 ELSE 1 END) ,t1.suppress_round from gl as t0 with (nolock)  join #trmethod on old_trmethod = t0.trmethod  join #glacctrow as t1 on t0.glnum = t1.glnum and t0.glrc1 = t1.glrc1 AND rownum <> 0 AND colnum = 0 AND modifier = '' left join #linked_glamt as t2 with (nolock) on t2.glnum = replace(t1.glnum,' ','') and t2.glrc1 = t1.glrc1 and glyr = 2019       AND glper = 5                    and t2.gltype COLLATE DATABASE_DEFAULT IN (SELECT gltype COLLATE DATABASE_DEFAULT FROM df_dfx_bookcode AS j0 WHERE bookcode = '1' AND j0.gltype COLLATE DATABASE_DEFAULT = t2.gltype COLLATE DATABASE_DEFAULT) where 1=1 and ( len(t1.bookcode) = 0 OR t1.bookcode IS NULL OR t1.bookcode = '1') group by t0.masked_glnum,t0.gldes,t0.acctid,t0.seg1,t0.seg2,t0.seg3,t0.seg4,t0.seg5,t0.seg6,t0.seg7,t0.seg8,t0.seg9,t0.seg10,t1.node,t1.parent,t1.parentrefid,t1.leaf,t1.rownum,t1.nozero,t1.negate,t1.crbal,t1.dbonly,t1.cronly,t1.suppress_round  having sum(glcydb) <> 0 or sum(glcycr) <> 0 or sum(glcybu) <> 0
Warning: Null value is eliminated by an aggregate or other SET operation.
going to Process Excel
ProcessExcel:5                    Year: 2019       BalType: CUR Base Period: 5 ColNum: 2 ColPeriod = 5
INSERT INTO #glcoldata (rownum,rowmod,nozero,colnum,debit,credit,balance,budget,acctid,node,parent,parentrefid,leaf) select b1.rownum,0,b1.nozero,2, case when cast(b0.cell as decimal(24,8)) > 0 then cast(b0.cell as decimal(24,8)) else 0 end,  case when cast(b0.cell as decimal(24,8)) < 0 then cast(b0.cell as decimal(24,8)) else 0 end, cast(b0.cell as decimal(24,8)) * (CASE WHEN b1.crbal = 1 THEN -1 ELSE 1 END),0,1, node,parentnode,parentrefid,leaf from #final_rules AS b0  join df_dfx_rowdtl AS b1 ON b0.rownum = b1.rownum AND b1.rowsetid = 17 where isnumeric(b0.cell) = 1 and b0.cell <> '-' AND b0.colnum-1 = 2

Base Period = 5
Base Year = 2019
Year Ind = BASE
Period Ind = BASE
Periodctl =
Returned ColPeriod: 5
Reporting Currency:
Conversion Method:
Currency Filter:
Column Period = 5
Column Year = 2019
Book = 1
Setdefid = 0
back from dfxPopEntityOpts
INSERT INTO #minmax SELECT MIN(fmsbegdt) AS begdt,MAX(fmsenddt) AS enddt FROM fmsper WHERE fmsyr = 2019                 AND fmsper = 5
Enterprise
Going to BuildColumnSQL for CUR and Column = 3
insert into #glcoldata select t0.masked_glnum,t0.gldes,t0.acctid,t0.seg1,t0.seg2,t0.seg3,t0.seg4,t0.seg5,t0.seg6,t0.seg7,t0.seg8,t0.seg9,t0.seg10,t1.node,t1.parent,t1.parentrefid,t1.leaf,t1.rownum,0,t1.nozero,3, isnull(round(sum(round(cast(t2.glcydb as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) *  (CASE WHEN t1.cronly = 1 THEN 0 ELSE 1 END), isnull(round(sum(round(cast(t2.glcycr as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) *  (CASE WHEN t1.dbonly = 1 THEN 0 ELSE 1 END), isnull(round((sum(round(cast(isnull(t2.glcydb,0) as decimal(16,4)),4))* (CASE WHEN t1.cronly = 1 THEN 0 ELSE 1 END))-sum(round(cast(t2.glcycr as decimal(16,4)),4))* (CASE WHEN t1.dbonly = 1 THEN 0 ELSE 1 END)* (CASE WHEN t1.cronly = 1 THEN -1 ELSE 1 END)+round(sum(round(cast(isnull(t2.glcybu,0) as decimal(16,4)),4)),4),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END)* (CASE WHEN t1.crbal = 1 THEN -1 ELSE 1 END), isnull(round(sum(round(cast(isnull(t2.glcybu,0) as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) * (CASE WHEN t1.crbal = 1 THEN -1 ELSE 1 END) ,t1.suppress_round from gl as t0 with (nolock)  join #trmethod on old_trmethod = t0.trmethod  join #glacctrow as t1 on t0.glnum = t1.glnum and t0.glrc1 = t1.glrc1 AND rownum <> 0 AND colnum = 0 AND modifier = '' left join #linked_glamt as t2 with (nolock) on t2.glnum = replace(t1.glnum,' ','') and t2.glrc1 = t1.glrc1 and glyr = 2019       AND glper = 5                    and t2.gltype COLLATE DATABASE_DEFAULT IN (SELECT gltype COLLATE DATABASE_DEFAULT FROM df_dfx_bookcode AS j0 WHERE bookcode = '1' AND j0.gltype COLLATE DATABASE_DEFAULT = t2.gltype COLLATE DATABASE_DEFAULT) where 1=1 and ( len(t1.bookcode) = 0 OR t1.bookcode IS NULL OR t1.bookcode = '1') group by t0.masked_glnum,t0.gldes,t0.acctid,t0.seg1,t0.seg2,t0.seg3,t0.seg4,t0.seg5,t0.seg6,t0.seg7,t0.seg8,t0.seg9,t0.seg10,t1.node,t1.parent,t1.parentrefid,t1.leaf,t1.rownum,t1.nozero,t1.negate,t1.crbal,t1.dbonly,t1.cronly,t1.suppress_round  having sum(glcydb) <> 0 or sum(glcycr) <> 0 or sum(glcybu) <> 0
Warning: Null value is eliminated by an aggregate or other SET operation.
going to Process Excel
ProcessExcel:5                    Year: 2019       BalType: CUR Base Period: 5 ColNum: 3 ColPeriod = 5
INSERT INTO #glcoldata (rownum,rowmod,nozero,colnum,debit,credit,balance,budget,acctid,node,parent,parentrefid,leaf) select b1.rownum,0,b1.nozero,3, case when cast(b0.cell as decimal(24,8)) > 0 then cast(b0.cell as decimal(24,8)) else 0 end,  case when cast(b0.cell as decimal(24,8)) < 0 then cast(b0.cell as decimal(24,8)) else 0 end, cast(b0.cell as decimal(24,8)) * (CASE WHEN b1.crbal = 1 THEN -1 ELSE 1 END),0,1, node,parentnode,parentrefid,leaf from #final_rules AS b0  join df_dfx_rowdtl AS b1 ON b0.rownum = b1.rownum AND b1.rowsetid = 17 where isnumeric(b0.cell) = 1 and b0.cell <> '-' AND b0.colnum-1 = 3

Base Period = 5
Base Year = 2019
Year Ind = BASE
Period Ind = BASE
Periodctl =
Returned ColPeriod: 5
Reporting Currency:
Conversion Method:
Currency Filter:
Column Period = 5
Column Year = 2019
Book = 1
Setdefid = 0
back from dfxPopEntityOpts
INSERT INTO #minmax SELECT MIN(fmsbegdt) AS begdt,MAX(fmsenddt) AS enddt FROM fmsper WHERE fmsyr = 2019                 AND fmsper = 5
Going to BuildColumnSQL BB for YTD and Currency =  and Column = 4
Calling dfxfn_BuildColumnSQL
insert into #glcoldata select t0.masked_glnum,t0.gldes,t0.acctid,t0.seg1,t0.seg2,t0.seg3,t0.seg4,t0.seg5,t0.seg6,t0.seg7,t0.seg8,t0.seg9,t0.seg10,t1.node,t1.parent,t1.parentrefid,t1.leaf,t1.rownum,0,t1.nozero,4, isnull(round(sum(round(cast(t2.glcydb as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) *  (CASE WHEN t1.cronly = 1 THEN 0 ELSE 1 END), isnull(round(sum(round(cast(t2.glcycr as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) *  (CASE WHEN t1.dbonly = 1 THEN 0 ELSE 1 END), isnull(round((sum(round(cast(isnull(t2.glcydb,0) as decimal(16,4)),4))* (CASE WHEN t1.cronly = 1 THEN 0 ELSE 1 END))-sum(round(cast(t2.glcycr as decimal(16,4)),4))* (CASE WHEN t1.dbonly = 1 THEN 0 ELSE 1 END)* (CASE WHEN t1.cronly = 1 THEN -1 ELSE 1 END)+round(sum(round(cast(isnull(t2.glcybu,0) as decimal(16,4)),4)),4),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END)* (CASE WHEN t1.crbal = 1 THEN -1 ELSE 1 END), isnull(round(sum(round(cast(isnull(t2.glcybu,0) as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) * (CASE WHEN t1.crbal = 1 THEN -1 ELSE 1 END) ,t1.suppress_round from gl as t0 with (nolock)  join #trmethod on old_trmethod = t0.trmethod  join #glacctrow as t1 on t0.glnum = t1.glnum and t0.glrc1 = t1.glrc1 AND rownum <> 0 AND colnum = 0 AND modifier = '' left join #linked_glamt as t2 with (nolock) on t2.glnum = t1.glnum and t2.glrc1 = t1.glrc1 and glyr = 2019       and t2.gltype COLLATE DATABASE_DEFAULT IN (SELECT gltype COLLATE DATABASE_DEFAULT FROM df_dfx_bookcode AS j0 WHERE bookcode = '1' AND j0.gltype COLLATE DATABASE_DEFAULT = t2.gltype COLLATE DATABASE_DEFAULT) where 1=1 and t0.glcat IN ('A','C','L') and ( len(t1.bookcode) = 0 OR t1.bookcode IS NULL OR t1.bookcode = '1') group by t0.masked_glnum,t0.gldes,t0.acctid,t0.seg1,t0.seg2,t0.seg3,t0.seg4,t0.seg5,t0.seg6,t0.seg7,t0.seg8,t0.seg9,t0.seg10,t1.node,t1.parent,t1.parentrefid,t1.leaf,t1.rownum,t1.nozero,t1.negate,t1.crbal,t1.dbonly,t1.cronly,t1.suppress_round  having sum(glcydb) <> 0 or sum(glcycr) <> 0 or sum(glcybu) <> 0
Enterprise
Going to BuildColumnSQL for YTD and Column = 4
insert into #glcoldata select t0.masked_glnum,t0.gldes,t0.acctid,t0.seg1,t0.seg2,t0.seg3,t0.seg4,t0.seg5,t0.seg6,t0.seg7,t0.seg8,t0.seg9,t0.seg10,t1.node,t1.parent,t1.parentrefid,t1.leaf,t1.rownum,0,t1.nozero,4, isnull(round(sum(round(cast(t2.glcydb as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) *  (CASE WHEN t1.cronly = 1 THEN 0 ELSE 1 END), isnull(round(sum(round(cast(t2.glcycr as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) *  (CASE WHEN t1.dbonly = 1 THEN 0 ELSE 1 END), isnull(round((sum(round(cast(isnull(t2.glcydb,0) as decimal(16,4)),4))* (CASE WHEN t1.cronly = 1 THEN 0 ELSE 1 END))-sum(round(cast(t2.glcycr as decimal(16,4)),4))* (CASE WHEN t1.dbonly = 1 THEN 0 ELSE 1 END)* (CASE WHEN t1.cronly = 1 THEN -1 ELSE 1 END)+round(sum(round(cast(isnull(t2.glcybu,0) as decimal(16,4)),4)),4),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END)* (CASE WHEN t1.crbal = 1 THEN -1 ELSE 1 END), isnull(round(sum(round(cast(isnull(t2.glcybu,0) as decimal(16,4)),4)),4),0) * (CASE WHEN t1.negate = 1 THEN -1 ELSE 1 END) * (CASE WHEN t1.crbal = 1 THEN -1 ELSE 1 END) ,t1.suppress_round from gl as t0 with (nolock)  join #trmethod on old_trmethod = t0.trmethod  join #glacctrow as t1 on t0.glnum = t1.glnum and t0.glrc1 = t1.glrc1 AND rownum <> 0 AND colnum = 0 AND modifier = '' left join #linked_glamt as t2 with (nolock) on t2.glnum = t1.glnum and t2.glrc1 = t1.glrc1 and glyr = 2019       AND glper <= 5                    and t2.gltype COLLATE DATABASE_DEFAULT IN (SELECT gltype COLLATE DATABASE_DEFAULT FROM df_dfx_bookcode AS j0 WHERE bookcode = '1' AND j0.gltype COLLATE DATABASE_DEFAULT = t2.gltype COLLATE DATABASE_DEFAULT) where 1=1 and ( len(t1.bookcode) = 0 OR t1.bookcode IS NULL OR t1.bookcode = '1') group by t0.masked_glnum,t0.gldes,t0.acctid,t0.seg1,t0.seg2,t0.seg3,t0.seg4,t0.seg5,t0.seg6,t0.seg7,t0.seg8,t0.seg9,t0.seg10,t1.node,t1.parent,t1.parentrefid,t1.leaf,t1.rownum,t1.nozero,t1.negate,t1.crbal,t1.dbonly,t1.cronly,t1.suppress_round  having sum(glcydb) <> 0 or sum(glcycr) <> 0 or sum(glcybu) <> 0
Warning: Null value is eliminated by an aggregate or other SET operation.
going to Process Excel
ProcessExcel:5                    Year: 2019       BalType: YTD Base Period: 5 ColNum: 4 ColPeriod = 5
INSERT INTO #glcoldata (rownum,rowmod,nozero,colnum,debit,credit,balance,budget,acctid,node,parent,parentrefid,leaf) select b1.rownum,0,b1.nozero,4, case when cast(b0.cell as decimal(24,8)) > 0 then cast(b0.cell as decimal(24,8)) else 0 end,  case when cast(b0.cell as decimal(24,8)) < 0 then cast(b0.cell as decimal(24,8)) else 0 end, cast(b0.cell as decimal(24,8)) * (CASE WHEN b1.crbal = 1 THEN -1 ELSE 1 END),0,1, node,parentnode,parentrefid,leaf from #final_rules AS b0  join df_dfx_rowdtl AS b1 ON b0.rownum = b1.rownum AND b1.rowsetid = 17 where isnumeric(b0.cell) = 1 and b0.cell <> '-' AND b0.colnum-1 = 4

going to Process Excel for Non CPO/RPO data

INSERT INTO #glcoldata (rownum,rowmod,nozero,colnum,debit,credit,balance,budget,acctid,node,parent,parentrefid,leaf) select b1.rownum,0,b1.nozero,-1, case when cast(b0.cell as decimal(24,8)) > 0 then cast(b0.cell as decimal(24,8)) else 0 end,  case when cast(b0.cell as decimal(24,8)) < 0 then cast(b0.cell as decimal(24,8)) else 0 end, cast(b0.cell as decimal(24,8)) * (CASE WHEN b1.crbal = 1 THEN -1 ELSE 1 END),0,1, node,parentnode,parentrefid,leaf from #final_rules AS b0  join df_dfx_rowdtl AS b1 ON b0.rownum = b1.rownum AND b1.rowsetid = 17 where isnumeric(b0.cell) = 1 and b0.cell <> '-' AND b0.colnum-1 = -1
queueid = NULL
in dfxRefactorRows
leaving refactor rows
going to dfxCalcRows
factor/proximity
1
1
1
NO ROUND!
insert into #calcdata
select node,parent,parentrefid,leaf,rownum,rowmod,1,colnum,isnull(sum(debit),0) as debit,isnull(sum(credit),0) as credit,isnull(sum(balance),0) as balance,isnull(sum(budget),0) as budget
from #glcoldata
where rowmod = 0 and suppress_round <> 1 AND EXISTS(select * from df_dfx_reportdef as j0 JOIN df_dfx_coldtl as j1 ON j0.colsetid = j1.colsetid     AND j1.colnum = #glcoldata.colnum and (j1.format like '%.%' OR j1.suppress_round = 1) WHERE j0.reportdef_id = 11) group by node,parent,parentrefid,leaf,rownum,rowmod,colnum
SUPPRESS ROUND!
SUPPRESS ROUND!
insert into #calcdata
select node,parent,parentrefid,leaf,rownum,rowmod,1,colnum,isnull(sum(debit),0) as debit,isnull(sum(credit),0) as credit,isnull(sum(balance),0) as balance,isnull(sum(budget),0) as budget
from #glcoldata
where rowmod = 0 and suppress_round = 1 AND NOT EXISTS(select * from df_dfx_reportdef as j0 JOIN df_dfx_coldtl as j1 ON j0.colsetid = j1.colsetid     AND j1.colnum = #glcoldata.colnum and (j1.format like '%.%' OR j1.suppress_round = 1) WHERE j0.reportdef_id = 11) group by node,parent,parentrefid,leaf,rownum,rowmod,colnum
SUPPRESS ROUND!
insert into #calcdata
select node,parent,parentrefid,leaf,rownum,rowmod,1,colnum,isnull(sum(debit),0) as debit,isnull(sum(credit),0) as credit,isnull(sum(balance),0) as balance,isnull(sum(budget),0) as budget
from #glcoldata
where rowmod = 0 and suppress_round = 1 AND EXISTS(select * from df_dfx_reportdef as j0 JOIN df_dfx_coldtl as j1 ON j0.colsetid = j1.colsetid     AND j1.colnum = #glcoldata.colnum and (j1.format like '%.%' OR j1.suppress_round = 1) WHERE j0.reportdef_id = 11) group by node,parent,parentrefid,leaf,rownum,rowmod,colnum
ROUND!
insert into #calcdata
select node,parent,parentrefid,leaf,rownum,rowmod,1,colnum,round(isnull(sum(debit),0),2) as debit,round(isnull(sum(credit),0),2) as credit,round(isnull(sum(balance),0),2) as balance,round(isnull(sum(budget),0),2) as budget
from #glcoldata
where rowmod = 0 and suppress_round = 0 and not exists(select * from df_dfx_reportdef as j0 join df_dfx_coldtl as j1 on j0.colsetid = j1.colsetid     and j1.colnum = #glcoldata.colnum and (j1.format like '%.%' or j1.suppress_round = 1) where j0.reportdef_id = 11) group by node,parent,parentrefid,leaf,rownum,rowmod,colnum
have a record
inspecting
end of file: 0
lpctl = -1
local_precedence = 0
SELECT node,parent,parentrefid,leaf,colnum,1 as rownum, cast(0 as decimal(24,8)) as d001, cast(0 as decimal(24,8)) as c001, cast(0 as decimal(24,8)) as b001, cast(0 as decimal(24,8)) as u001 INTO ##workdata530 FROM #calcdata WHERE 1=0 GROUP BY node,parent,parentrefid,leaf,colnum
start operator = +
lparen = (
rowctl = 7
literal = 0
operator = +
INSERT INTO ##workdata530 SELECT node,parent,parentrefid,leaf,colnum,1,sum(debit),sum(credit),sum(balance),sum(budget) FROM #calcdata WHERE rownum BETWEEN 1    AND 6    AND (rowmod = 0 or rowmod = 1   ) GROUP BY node,parent,parentrefid,leaf,colnum
have a record
inspecting
into calcdata
INSERT INTO #calcdata (rownum,rowmod,node,parent,parentrefid,leaf,colnum,nozero,debit,credit,balance,budget)SELECT 7,7,node,parent,parentrefid,leaf,colnum,1,(+SUM(d001)),(+SUM(c001)),(+SUM(b001)),(+SUM(u001)) FROM ##workdata530 GROUP BY node,parent,parentrefid,leaf,colnum
DROP TABLE ##workdata530
end of file: 1
done dfxCalcRows
column_list start col = -1
INSERT INTO ##tempresults530(resulttype,parentcode,leafcode,node,parent,parentrefid,leaf,rowdesc,rownum,nozero,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,noprint,debit02,credit02,balance02,budget02,debit03,credit03,balance03,budget03,debit04,credit04,balance04,budget04) SELECT 'F',NULL,NULL,NULL,NULL,NULL,NULL,rowdesc,v.rownum,v.nozero,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,v.noprint,isnull(sum(debit02),0) as debit02,isnull(sum(credit02),0) as credit02 ,isnull(sum(balance02),0) as balance02,isnull(sum(budget02),0) as budget02,isnull(sum(debit03),0) as debit03,isnull(sum(credit03),0) as credit03 ,isnull(sum(balance03),0) as balance03,isnull(sum(budget03),0) as budget03,isnull(sum(debit04),0) as debit04,isnull(sum(credit04),0) as credit04 ,isnull(sum(balance04),0) as balance04,isnull(sum(budget04),0) as budget04 FROM (SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,sum(debit) as debit02,sum(credit) as credit02,sum(balance) as balance02,sum(budget) as budget02,cast(0 as decimal(16,2)) as debit03,cast(0 as decimal(16,2)) as credit03,cast(0 as decimal(16,2)) as balance03,cast(0 as decimal(16,2)) as budget03,cast(0 as decimal(16,2)) as debit04,cast(0 as decimal(16,2)) as credit04,cast(0 as decimal(16,2)) as balance04,cast(0 as decimal(16,2)) as budget04 FROM #glcoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 1 and not (t0.nozero = 1 and t0.balance = 0) and acctid is null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor having ((sum(t0.balance) <> 0)and t0.nozero = 1) or t0.nozero = 0 UNION SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,0 as debit02,0 as credit02,0 as balance02,0 as budget02,sum(debit) as debit03,sum(credit) as credit03,sum(balance) as balance03,sum(budget) as budget03,0 as debit04,0 as credit04,0 as balance04,0 as budget04 FROM #glcoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 2 and not (t0.nozero = 1 and t0.balance = 0 and t0.debit = 0 and t0.credit = 0) and acctid is null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor having ((sum(t0.balance) <> 0 or sum(t0.debit) <> 0 or sum(t0.credit) <> 0) and t0.nozero = 1) or t0.nozero = 0 UNION SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,0 as debit02,0 as credit02,0 as balance02,0 as budget02,0 as debit03,0 as credit03,0 as balance03,0 as budget03,sum(debit) as debit04,sum(credit) as credit04,sum(balance) as balance04,sum(budget) as budget04 FROM #glcoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 3 and not (t0.nozero = 1 and t0.balance = 0 and t0.debit = 0 and t0.credit = 0) and acctid is null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor having ((sum(t0.balance) <> 0 or sum(t0.debit) <> 0 or sum(t0.credit) <> 0) and t0.nozero = 1) or t0.nozero = 0) AS v group by v.rowdesc,v.rownum,v.nozero,v.noprint ,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor
done execute
INSERT INTO ##tempresults530(resulttype,parentcode,leafcode,node,parent,parentrefid,leaf,rowdesc,rownum,nozero,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,noprint,debit02,credit02,balance02,budget02,debit03,credit03,balance03,budget03,debit04,credit04,balance04,budget04) SELECT 'A',NULL,NULL,NULL,NULL,NULL,NULL,rowdesc,v.rownum,v.nozero,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,v.noprint,isnull(sum(debit02),0) as debit02,isnull(sum(credit02),0) as credit02 ,isnull(sum(balance02),0) as balance02,isnull(sum(budget02),0) as budget02,isnull(sum(debit03),0) as debit03,isnull(sum(credit03),0) as credit03 ,isnull(sum(balance03),0) as balance03,isnull(sum(budget03),0) as budget03,isnull(sum(debit04),0) as debit04,isnull(sum(credit04),0) as credit04 ,isnull(sum(balance04),0) as balance04,isnull(sum(budget04),0) as budget04 FROM (SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,sum(debit) as debit02,sum(credit) as credit02,sum(balance) as balance02,sum(budget) as budget02,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,cast(0 as decimal(16,2)) as debit03,cast(0 as decimal(16,2)) as credit03,cast(0 as decimal(16,2)) as balance03,cast(0 as decimal(16,2)) as budget03,cast(0 as decimal(16,2)) as debit04,cast(0 as decimal(16,2)) as credit04,cast(0 as decimal(16,2)) as balance04,cast(0 as decimal(16,2)) as budget04 FROM #glcoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 1 and not (t0.nozero = 1 and t0.balance = 0) and acctid is not null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5 having ((sum(t0.balance) <> 0)and t0.nozero = 1) or t0.nozero = 0 UNION SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,0 as debit02,0 as credit02,0 as balance02,0 as budget02,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,sum(debit) as debit03,sum(credit) as credit03,sum(balance) as balance03,sum(budget) as budget03,0 as debit04,0 as credit04,0 as balance04,0 as budget04 FROM #glcoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 2 and not (t0.nozero = 1 and t0.balance = 0 and t0.debit = 0 and t0.credit = 0) and acctid is not null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5 having ((sum(t0.balance) <> 0 or sum(t0.debit) <> 0 or sum(t0.credit) <> 0) and t0.nozero = 1) or t0.nozero = 0 UNION SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,0 as debit02,0 as credit02,0 as balance02,0 as budget02,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,0 as debit03,0 as credit03,0 as balance03,0 as budget03,sum(debit) as debit04,sum(credit) as credit04,sum(balance) as balance04,sum(budget) as budget04 FROM #glcoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 3 and not (t0.nozero = 1 and t0.balance = 0 and t0.debit = 0 and t0.credit = 0) and acctid is not null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5 having ((sum(t0.balance) <> 0 or sum(t0.debit) <> 0 or sum(t0.credit) <> 0) and t0.nozero = 1) or t0.nozero = 0) AS v group by v.rowdesc,v.rownum,v.nozero,v.noprint ,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5
done execute
startcol = 4
colcount = 6
column_list start col = 4
INSERT INTO ##tempresults530(resulttype,parentcode,leafcode,node,parent,parentrefid,leaf,rowdesc,rownum,nozero,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,noprint,debit05,credit05,balance05,budget05,debit06,credit06,balance06,budget06) SELECT 'F',NULL,NULL,NULL,NULL,NULL,NULL,rowdesc,v.rownum,v.nozero,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,v.noprint,isnull(sum(debit05),0) as debit05,isnull(sum(credit05),0) as credit05 ,isnull(sum(balance05),0) as balance05,isnull(sum(budget05),0) as budget05,isnull(sum(debit06),0) as debit06,isnull(sum(credit06),0) as credit06 ,isnull(sum(balance06),0) as balance06,isnull(sum(budget06),0) as budget06 FROM (SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,sum(debit) as debit05,sum(credit) as credit05,sum(balance) as balance05,sum(budget) as budget05,cast(0 as decimal(16,2)) as debit06,cast(0 as decimal(16,2)) as credit06,cast(0 as decimal(16,2)) as balance06,cast(0 as decimal(16,2)) as budget06 FROM #glcoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 4 and not (t0.nozero = 1 and t0.balance = 0) and acctid is null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor having ((sum(t0.balance) <> 0)and t0.nozero = 1) or t0.nozero = 0 UNION SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,0 as debit05,0 as credit05,0 as balance05,0 as budget05,sum(debit) as debit06,sum(credit) as credit06,sum(balance) as balance06,sum(budget) as budget06 FROM #glcoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 5 and not (t0.nozero = 1 and t0.balance = 0) and acctid is null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor having ((sum(t0.balance) <> 0)and t0.nozero = 1) or t0.nozero = 0) AS v group by v.rowdesc,v.rownum,v.nozero,v.noprint ,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor
done execute
INSERT INTO ##tempresults530(resulttype,parentcode,leafcode,node,parent,parentrefid,leaf,rowdesc,rownum,nozero,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,noprint,debit05,credit05,balance05,budget05,debit06,credit06,balance06,budget06) SELECT 'A',NULL,NULL,NULL,NULL,NULL,NULL,rowdesc,v.rownum,v.nozero,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,v.noprint,isnull(sum(debit05),0) as debit05,isnull(sum(credit05),0) as credit05 ,isnull(sum(balance05),0) as balance05,isnull(sum(budget05),0) as budget05,isnull(sum(debit06),0) as debit06,isnull(sum(credit06),0) as credit06 ,isnull(sum(balance06),0) as balance06,isnull(sum(budget06),0) as budget06 FROM (SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,sum(debit) as debit05,sum(credit) as credit05,sum(balance) as balance05,sum(budget) as budget05,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,cast(0 as decimal(16,2)) as debit06,cast(0 as decimal(16,2)) as credit06,cast(0 as decimal(16,2)) as balance06,cast(0 as decimal(16,2)) as budget06 FROM #glcoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 4 and not (t0.nozero = 1 and t0.balance = 0) and acctid is not null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5 having ((sum(t0.balance) <> 0)and t0.nozero = 1) or t0.nozero = 0 UNION SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,0 as debit05,0 as credit05,0 as balance05,0 as budget05,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,sum(debit) as debit06,sum(credit) as credit06,sum(balance) as balance06,sum(budget) as budget06 FROM #glcoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 5 and not (t0.nozero = 1 and t0.balance = 0) and acctid is not null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5 having ((sum(t0.balance) <> 0)and t0.nozero = 1) or t0.nozero = 0) AS v group by v.rowdesc,v.rownum,v.nozero,v.noprint ,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5
done execute
startcol = 9
colcount = 6
to xfer
INSERT INTO ##glresults530 SELECT resulttype,parentcode,leafcode,node,parent,parentrefid,leaf,rowdesc,rownum,nozero,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,noprint,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,isnull(sum(debit02),0),isnull(sum(credit02),0),isnull(sum(balance02),0),isnull(sum(budget02),0),isnull(sum(debit03),0),isnull(sum(credit03),0),isnull(sum(balance03),0),isnull(sum(budget03),0),isnull(sum(debit04),0),isnull(sum(credit04),0),isnull(sum(balance04),0),isnull(sum(budget04),0),isnull(sum(debit05),0),isnull(sum(credit05),0),isnull(sum(balance05),0),isnull(sum(budget05),0),isnull(sum(debit06),0),isnull(sum(credit06),0),isnull(sum(balance06),0),isnull(sum(budget06),0)
INSERT INTO ##glresults530 SELECT resulttype,parentcode,leafcode,node,parent,parentrefid,leaf,rowdesc,rownum,nozero,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,noprint,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,isnull(sum(debit02),0),isnull(sum(credit02),0),isnull(sum(balance02),0),isnull(sum(budget02),0),isnull(sum(debit03),0),isnull(sum(credit03),0),isnull(sum(balance03),0),isnull(sum(budget03),0),isnull(sum(debit04),0),isnull(sum(credit04),0),isnull(sum(balance04),0),isnull(sum(budget04),0),isnull(sum(debit05),0),isnull(sum(credit05),0),isnull(sum(balance05),0),isnull(sum(budget05),0),isnull(sum(debit06),0),isnull(sum(credit06),0),isnull(sum(balance06),0),isnull(sum(budget06),0) FROM ##tempresults530 GROUP BY resulttype,parentcode,leafcode,node,parent,parentrefid,leaf,rowdesc,rownum,nozero,noprint,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor
Warning: Null value is eliminated by an aggregate or other SET operation.
back xfer
INSERT INTO ##glresults530(resulttype,rownum,rowdesc,noprint,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,debit02,credit02,balance02,budget02,debit03,credit03,balance03,budget03,debit04,credit04,balance04,budget04,debit05,credit05,balance05,budget05,debit06,credit06,balance06,budget06) select distinct 'F',r0.rownum,r0.rowdesc,r0.noprint,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL from df_dfx_rowdtl as r0  left join df_dfx_fontstyle as f0 on r0.fontstyle = f0.fontstyle_id where (r0.rowsetid = 17 AND r0.noprint = 0 AND (r0.rowtype IN (5,6,7,8,10,11,12,13,14) or (r0.rowtype = 0 and r0.setdefid = 0 and r0.nozero = 0 )))
INSERT INTO ##glresults530(resulttype,rownum,rowdesc,noprint,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,debit02,credit02,balance02,budget02,debit03,credit03,balance03,budget03,debit04,credit04,balance04,budget04,debit05,credit05,balance05,budget05,debit06,credit06,balance06,budget06) select distinct 'F',r0.rownum,r0.rowdesc,r0.noprint,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 from df_dfx_rowdtl as r0  left join df_dfx_fontstyle as f0 on r0.fontstyle = f0.fontstyle_id where r0.rowsetid = 17 and r0.noprint = 0 and r0.nozero = 0 and  r0.rowcalc not like 'RND(%' and  NOT EXISTS (SELECT 1 FROM ##glresults530 as j0 WHERE resulttype = 'F' AND j0.rownum = r0.rownum)
UPDATE ##glresults530 SET balance03 = debit03
UPDATE ##glresults530 SET balance04 = credit04
Calling dfxCalcColumns
B+C-+D
returned:B+C-+D
returned:B+C-+D
^02+^03-+^04
@toptr =
0
fetch calcwork: ^02+^03-+^04
rparen count = 0
lparen count = 0
UPDATE ##glresults530 SET balance06=ISNULL((balance02+balance03-+balance04),0)
return from dfxCalcColumns
Calling dfxRefactorColumns
calcrowfinal
calc row final execution begins
##fincalc530
SELECT * INTO ##fincalc530 FROM ##glresults530 AS t0 WHERE 1=1 AND EXISTS(select * from #worktab as j0 where t0.rownum between j0.fromrownum and j0.torownum and
				(exists(select * from #worktab as j1 where j1.rownum = j0.rownum  and substring(operator,1,1) in ('*','/','=','+','-'))) or exists(select * from df_dfx_rowdtl as j1 where j1.rowsetid = 17 and j1.rownum = t0.rownum and j1.rowtype in (0,5) and j1.setdefid <> 0)) AND resulttype = 'F'
INSERT INTO #fincoldata(masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,node,parent,parentrefid,leaf,rownum,rowmod,nozero,colnum,debit,credit,balance,budget) SELECT masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,node,parent,parentrefid,leaf,rownum,0,nozero,1,debit02,credit02,balance02,budget02 FROM ##fincalc530 AS t0 WHERE NOT EXISTS(SELECT * FROM df_dfx_rowdtl AS j0 WHERE j0.rowsetid = 17 AND j0.rownum = t0.rownum AND j0.rowtype in (3))AND (t0.masked_glnum IS NOT NULL or resulttype = 'F')
INSERT INTO #fincoldata(masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,node,parent,parentrefid,leaf,rownum,rowmod,nozero,colnum,debit,credit,balance,budget) SELECT masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,node,parent,parentrefid,leaf,rownum,0,nozero,2,debit03,credit03,balance03,budget03 FROM ##fincalc530 AS t0 WHERE NOT EXISTS(SELECT * FROM df_dfx_rowdtl AS j0 WHERE j0.rowsetid = 17 AND j0.rownum = t0.rownum AND j0.rowtype in (3))AND (t0.masked_glnum IS NOT NULL or resulttype = 'F')
INSERT INTO #fincoldata(masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,node,parent,parentrefid,leaf,rownum,rowmod,nozero,colnum,debit,credit,balance,budget) SELECT masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,node,parent,parentrefid,leaf,rownum,0,nozero,3,debit04,credit04,balance04,budget04 FROM ##fincalc530 AS t0 WHERE NOT EXISTS(SELECT * FROM df_dfx_rowdtl AS j0 WHERE j0.rowsetid = 17 AND j0.rownum = t0.rownum AND j0.rowtype in (3))AND (t0.masked_glnum IS NOT NULL or resulttype = 'F')
INSERT INTO #fincoldata(masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,node,parent,parentrefid,leaf,rownum,rowmod,nozero,colnum,debit,credit,balance,budget) SELECT masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,node,parent,parentrefid,leaf,rownum,0,nozero,4,debit05,credit05,balance05,budget05 FROM ##fincalc530 AS t0 WHERE NOT EXISTS(SELECT * FROM df_dfx_rowdtl AS j0 WHERE j0.rowsetid = 17 AND j0.rownum = t0.rownum AND j0.rowtype in (3))AND (t0.masked_glnum IS NOT NULL or resulttype = 'F')
INSERT INTO #fincoldata(masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,node,parent,parentrefid,leaf,rownum,rowmod,nozero,colnum,debit,credit,balance,budget) SELECT masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,node,parent,parentrefid,leaf,rownum,0,nozero,5,debit06,credit06,balance06,budget06 FROM ##fincalc530 AS t0 WHERE NOT EXISTS(SELECT * FROM df_dfx_rowdtl AS j0 WHERE j0.rowsetid = 17 AND j0.rownum = t0.rownum AND j0.rowtype in (3))AND (t0.masked_glnum IS NOT NULL or resulttype = 'F')
factor/proximity
1
1
1
NO ROUND!
insert into #calcdata
select node,parent,parentrefid,leaf,rownum,rowmod,1,colnum,isnull(sum(debit),0) as debit,isnull(sum(credit),0) as credit,isnull(sum(balance),0) as balance,isnull(sum(budget),0) as budget
from #fincoldata
where rowmod = 0 and suppress_round <> 1 AND EXISTS(select * from df_dfx_reportdef as j0 JOIN df_dfx_coldtl as j1 ON j0.colsetid = j1.colsetid     AND j1.colnum = #fincoldata.colnum and (j1.format like '%.%' OR j1.suppress_round = 1) WHERE j0.reportdef_id = 11) group by node,parent,parentrefid,leaf,rownum,rowmod,colnum
SUPPRESS ROUND!
SUPPRESS ROUND!
insert into #calcdata
select node,parent,parentrefid,leaf,rownum,rowmod,1,colnum,isnull(sum(debit),0) as debit,isnull(sum(credit),0) as credit,isnull(sum(balance),0) as balance,isnull(sum(budget),0) as budget
from #fincoldata
where rowmod = 0 and suppress_round = 1 AND NOT EXISTS(select * from df_dfx_reportdef as j0 JOIN df_dfx_coldtl as j1 ON j0.colsetid = j1.colsetid     AND j1.colnum = #fincoldata.colnum and (j1.format like '%.%' OR j1.suppress_round = 1) WHERE j0.reportdef_id = 11) group by node,parent,parentrefid,leaf,rownum,rowmod,colnum
SUPPRESS ROUND!
insert into #calcdata
select node,parent,parentrefid,leaf,rownum,rowmod,1,colnum,isnull(sum(debit),0) as debit,isnull(sum(credit),0) as credit,isnull(sum(balance),0) as balance,isnull(sum(budget),0) as budget
from #fincoldata
where rowmod = 0 and suppress_round = 1 AND EXISTS(select * from df_dfx_reportdef as j0 JOIN df_dfx_coldtl as j1 ON j0.colsetid = j1.colsetid     AND j1.colnum = #fincoldata.colnum and (j1.format like '%.%' OR j1.suppress_round = 1) WHERE j0.reportdef_id = 11) group by node,parent,parentrefid,leaf,rownum,rowmod,colnum
ROUND!
insert into #calcdata
select node,parent,parentrefid,leaf,rownum,rowmod,1,colnum,round(isnull(sum(debit),0),2) as debit,round(isnull(sum(credit),0),2) as credit,round(isnull(sum(balance),0),2) as balance,round(isnull(sum(budget),0),2) as budget
from #fincoldata
where rowmod = 0 and suppress_round = 0 and not exists(select * from df_dfx_reportdef as j0 join df_dfx_coldtl as j1 on j0.colsetid = j1.colsetid     and j1.colnum = #fincoldata.colnum and (j1.format like '%.%' or j1.suppress_round = 1) where j0.reportdef_id = 11) group by node,parent,parentrefid,leaf,rownum,rowmod,colnum
Warning: Null value is eliminated by an aggregate or other SET operation.
have a record
inspecting
end of file: 0
lpctl = -1
local_precedence = 0
SELECT node,parent,parentrefid,leaf,colnum,1 as rownum, cast(0 as decimal(24,8)) as d001, cast(0 as decimal(24,8)) as c001, cast(0 as decimal(24,8)) as b001, cast(0 as decimal(24,8)) as u001 INTO ##workdata530 FROM #calcdata WHERE 1=0 GROUP BY node,parent,parentrefid,leaf,colnum
start operator = +
lparen = (
rowctl = 7
literal = 0
operator = +
INSERT INTO ##workdata530 SELECT node,parent,parentrefid,leaf,colnum,1,sum(debit),sum(credit),sum(balance),sum(budget) FROM #calcdata WHERE rownum BETWEEN 1    AND 6    AND (rowmod = 0 or rowmod = 1   ) GROUP BY node,parent,parentrefid,leaf,colnum
have a record
inspecting
into calcdata
INSERT INTO #calcdata (rownum,rowmod,node,parent,parentrefid,leaf,colnum,nozero,debit,credit,balance,budget)SELECT 7,7,node,parent,parentrefid,leaf,colnum,1,(+SUM(d001)),(+SUM(c001)),(+SUM(b001)),(+SUM(u001)) FROM ##workdata530 GROUP BY node,parent,parentrefid,leaf,colnum
DROP TABLE ##workdata530
end of file: 1
in dfxRefactorRows
leaving refactor rows
column_list start col = -1
INSERT INTO ##tempresults530(resulttype,parentcode,leafcode,node,parent,parentrefid,leaf,rowdesc,rownum,nozero,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,noprint,debit02,credit02,balance02,budget02,debit03,credit03,balance03,budget03,debit04,credit04,balance04,budget04) SELECT 'F',NULL,NULL,NULL,NULL,NULL,NULL,rowdesc,v.rownum,v.nozero,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,v.noprint,isnull(sum(debit02),0) as debit02,isnull(sum(credit02),0) as credit02 ,isnull(sum(balance02),0) as balance02,isnull(sum(budget02),0) as budget02,isnull(sum(debit03),0) as debit03,isnull(sum(credit03),0) as credit03 ,isnull(sum(balance03),0) as balance03,isnull(sum(budget03),0) as budget03,isnull(sum(debit04),0) as debit04,isnull(sum(credit04),0) as credit04 ,isnull(sum(balance04),0) as balance04,isnull(sum(budget04),0) as budget04 FROM (SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,sum(debit) as debit02,sum(credit) as credit02,sum(balance) as balance02,sum(budget) as budget02,cast(0 as decimal(16,2)) as debit03,cast(0 as decimal(16,2)) as credit03,cast(0 as decimal(16,2)) as balance03,cast(0 as decimal(16,2)) as budget03,cast(0 as decimal(16,2)) as debit04,cast(0 as decimal(16,2)) as credit04,cast(0 as decimal(16,2)) as balance04,cast(0 as decimal(16,2)) as budget04 FROM #fincoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 1 and not (t0.nozero = 1 and t0.balance = 0) and acctid is null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor having ((sum(t0.balance) <> 0)and t0.nozero = 1) or t0.nozero = 0 UNION SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,0 as debit02,0 as credit02,0 as balance02,0 as budget02,sum(debit) as debit03,sum(credit) as credit03,sum(balance) as balance03,sum(budget) as budget03,0 as debit04,0 as credit04,0 as balance04,0 as budget04 FROM #fincoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 2 and not (t0.nozero = 1 and t0.balance = 0 and t0.debit = 0 and t0.credit = 0) and acctid is null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor having ((sum(t0.balance) <> 0 or sum(t0.debit) <> 0 or sum(t0.credit) <> 0) and t0.nozero = 1) or t0.nozero = 0 UNION SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,0 as debit02,0 as credit02,0 as balance02,0 as budget02,0 as debit03,0 as credit03,0 as balance03,0 as budget03,sum(debit) as debit04,sum(credit) as credit04,sum(balance) as balance04,sum(budget) as budget04 FROM #fincoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 3 and not (t0.nozero = 1 and t0.balance = 0 and t0.debit = 0 and t0.credit = 0) and acctid is null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor having ((sum(t0.balance) <> 0 or sum(t0.debit) <> 0 or sum(t0.credit) <> 0) and t0.nozero = 1) or t0.nozero = 0) AS v group by v.rowdesc,v.rownum,v.nozero,v.noprint ,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor
done execute
startcol = 4
colcount = 6
column_list start col = 4
INSERT INTO ##tempresults530(resulttype,parentcode,leafcode,node,parent,parentrefid,leaf,rowdesc,rownum,nozero,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,noprint,debit05,credit05,balance05,budget05,debit06,credit06,balance06,budget06) SELECT 'F',NULL,NULL,NULL,NULL,NULL,NULL,rowdesc,v.rownum,v.nozero,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,v.noprint,isnull(sum(debit05),0) as debit05,isnull(sum(credit05),0) as credit05 ,isnull(sum(balance05),0) as balance05,isnull(sum(budget05),0) as budget05,isnull(sum(debit06),0) as debit06,isnull(sum(credit06),0) as credit06 ,isnull(sum(balance06),0) as balance06,isnull(sum(budget06),0) as budget06 FROM (SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,sum(debit) as debit05,sum(credit) as credit05,sum(balance) as balance05,sum(budget) as budget05,cast(0 as decimal(16,2)) as debit06,cast(0 as decimal(16,2)) as credit06,cast(0 as decimal(16,2)) as balance06,cast(0 as decimal(16,2)) as budget06 FROM #fincoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 4 and not (t0.nozero = 1 and t0.balance = 0) and acctid is null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor having ((sum(t0.balance) <> 0)and t0.nozero = 1) or t0.nozero = 0 UNION SELECT rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,0 as debit05,0 as credit05,0 as balance05,0 as budget05,sum(debit) as debit06,sum(credit) as credit06,sum(balance) as balance06,sum(budget) as budget06 FROM #fincoldata AS t0 join df_dfx_rowdtl as t1 on t0.rownum = t1.rownum and t1.rowsetid = 17 left join df_dfx_fontstyle as t2 on t1.fontstyle = t2.fontstyle_id where t0.colnum = 5 and not (t0.nozero = 1 and t0.balance = 0) and acctid is null group by rowdesc,t0.rownum,t0.nozero,t1.noprint,colnum,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor having ((sum(t0.balance) <> 0)and t0.nozero = 1) or t0.nozero = 0) AS v group by v.rowdesc,v.rownum,v.nozero,v.noprint ,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor
done execute
startcol = 9
colcount = 6
to xfer
INSERT INTO ##fincalc530 SELECT resulttype,parentcode,leafcode,node,parent,parentrefid,leaf,rowdesc,rownum,nozero,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,noprint,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,isnull(sum(debit02),0),isnull(sum(credit02),0),isnull(sum(balance02),0),isnull(sum(budget02),0),isnull(sum(debit03),0),isnull(sum(credit03),0),isnull(sum(balance03),0),isnull(sum(budget03),0),isnull(sum(debit04),0),isnull(sum(credit04),0),isnull(sum(balance04),0),isnull(sum(budget04),0),isnull(sum(debit05),0),isnull(sum(credit05),0),isnull(sum(balance05),0),isnull(sum(budget05),0),isnull(sum(debit06),0),isnull(sum(credit06),0),isnull(sum(balance06),0),isnull(sum(budget06),0)
INSERT INTO ##fincalc530 SELECT resulttype,parentcode,leafcode,node,parent,parentrefid,leaf,rowdesc,rownum,nozero,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,noprint,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor,isnull(sum(debit02),0),isnull(sum(credit02),0),isnull(sum(balance02),0),isnull(sum(budget02),0),isnull(sum(debit03),0),isnull(sum(credit03),0),isnull(sum(balance03),0),isnull(sum(budget03),0),isnull(sum(debit04),0),isnull(sum(credit04),0),isnull(sum(balance04),0),isnull(sum(budget04),0),isnull(sum(debit05),0),isnull(sum(credit05),0),isnull(sum(balance05),0),isnull(sum(budget05),0),isnull(sum(debit06),0),isnull(sum(credit06),0),isnull(sum(balance06),0),isnull(sum(budget06),0) FROM ##tempresults530 GROUP BY resulttype,parentcode,leafcode,node,parent,parentrefid,leaf,rowdesc,rownum,nozero,noprint,masked_glnum,gldes,acctid,seg1,seg2,seg3,seg4,seg5,fontfamily,fontsize,isbold,isitalic,isunderline,forecolor,backcolor
Warning: Null value is eliminated by an aggregate or other SET operation.
back xfer
DELETE FROM ##glresults530 WHERE EXISTS(select * from ##fincalc530 as j0 where j0.rownum = ##glresults530.rownum and isnull(##glresults530.node,0) = isnull(j0.node,0) and isnull(##glresults530.leaf,0) = isnull(j0.leaf,0) and isnull(##glresults530.parent,0) = isnull(j0.parent,0) and isnull(##glresults530.parentrefid,0) = isnull(j0.parentrefid,0) and ##glresults530.resulttype = j0.resulttype)
INSERT INTO ##glresults530 SELECT * FROM ##fincalc530
B+C-+D
returned:B+C-+D
returned:B+C-+D
^02+^03-+^04
@toptr =
0
fetch calcwork: ^02+^03-+^04
rparen count = 0
lparen count = 0
UPDATE ##glresults530 SET balance06=ISNULL((balance02+balance03-+balance04),0) WHERE EXISTS(select * from #calctab as j0 where (##glresults530.rownum = j0.rownum) and
			exists(select * from #calctab as j1 where j1.rownum = j0.rownum  and substring(operator,1,1) in ('*','/','=','+','-') and coldest <> 5 ))
ExceptionLevel:1
Format:#,##0.00;(#,##0.00);0.00"
INSERT INTO #ValidRownum(node,leaf,rownum)
SELECT  node,leaf,rownum FROM ##glresults530 AS g0
WHERE g0.resulttype = 'F' and isnull(balance02,0) <> 0 OR isnull(balance03,0) <> 0 OR isnull(balance04,0) <> 0 OR isnull(balance05,0) <> 0 OR isnull(balance06,0) <> 0
relatedsql get
Jun 17 2019  3:26PM
SELECT * INTO ##related530 FROM ##glresults530 AS t0 WHERE resulttype = 'F' AND EXISTS(select 1 from #calctab as j0 where j0.rownum = t0.rownum) AND (EXISTS (SELECT 1 FROM  #calctab AS j0  JOIN df_dfx_rowdtl AS j1 ON j0.rownum = j1.rownum AND j1.rowsetid = 17 AND j1.rowtype NOT IN (2,3) JOIN df_dfx_rowdtl AS j2 ON upper(j2.rowlabel) = upper(j0.fromrow) AND j2.rowsetid = 17 WHERE j0.rownum = t0.rownum  AND (SELECT COUNT(1) FROM #ValidRownum AS g0 WHERE g0.rownum = j2.rownum ) = 0))
Jun 17 2019  3:26PM
CREATE INDEX i_1 ON ##related530(resulttype,rownum)
SELECT t0.* INTO ##drilldata530 FROM ##glresults530 AS t0  LEFT JOIN df_dfx_rowdtl AS t1 ON t0.rownum = t1.rownum AND t1.rowsetid = 17 WHERE rowtype IN (0,2,3) AND resulttype = 'F'
UPDATE ##drilldata530 SET resulttype = 'X', leafcode=rowdesc,rowdesc = leafcode
INSERT INTO #zero SELECT resulttype,isnull(t0.node,0),isnull(t0.leaf,0),t0.rownum,isnull(parentrefid,0),isnull(acctid,0) FROM ##glresults530 AS t0  JOIN df_dfx_rowdtl AS t1 ON t0.rownum = t1.rownum AND t1.rowsetid = 17 WHERE 1=1  AND ((t1.rowtype IN (2,3) OR (t1.rowtype = 0 AND t1.setdefid <> 0)) AND (isnull(t1.nozero,0) = 1 AND round(isnull(balance02,0), 2)= 0  AND  round(isnull(balance03,0), 2)= 0  AND  round(isnull(balance04,0), 2)= 0  AND  round(isnull(balance05,0), 2)= 0  AND  round(isnull(balance06,0), 2)= 0 )) AND isnull(t1.rowcalc,'') not like '%\%%' escape '\'
SELECT t0.*,CASE WHEN t1.rowtype IN (10,11) THEN 10 WHEN t1.rowtype = 0 AND t1.setdefid = 0 THEN 5 WHEN t1.rowtype IS NULL THEN 5 ELSE t1.rowtype END as rowtype ,fm0.format,CAST('' AS VARCHAR(20)) as acct_string,tr.tabkey,tr.tabkey2,isnull(cs.currsymb,0) as currsymb,ISNULL(rs.sortind,0) as sortind,'1' as bookcode,'USD' as rptcurr, t1.suppress_round FROM ##glresults530 AS t0 LEFT JOIN df_dfx_rowdtl AS t1 ON t0.rownum = t1.rownum AND t1.rowsetid = 17 LEFT JOIN #rowformats AS fm0 ON fm0.rownum = t0.rownum LEFT JOIN #tabrow AS tr ON tr.rownum = t0.rownum LEFT JOIN #currsymb AS cs ON cs.rownum = t0.rownum AND ISNULL(cs.leaf,'') = ISNULL(t0.leaf,'') AND ISNULL(cs.node,'') = ISNULL(t0.node,'') LEFT JOIN #rowsort AS rs ON isnull(rs.node,'') = isnull(t0.node,'') AND isnull(rs.leaf,'') = isnull(t0.leaf,'') AND rs.rownum = t0.rownum WHERE NOT (resulttype = 'A' AND masked_glnum IS NULL) AND (t0.rownum <= 8          OR t0.rownum IN (9998,9999)) AND ISNULL(t1.noprint,0) <> 1 AND isnull(t1.rowtype,'0') <> '9'	AND NOT EXISTS(SELECT * FROM #zero AS j0 WHERE j0.resulttype = t0.resulttype AND j0.node = isnull(t0.node,0) and j0.leaf = isnull(t0.leaf,0) and j0.rownum = t0.rownum and j0.parentrefid = isnull(t0.parentrefid,0) and j0.acctid = isnull(t0.acctid,0))AND NOT EXISTS (SELECT * FROM ##related530 AS g0 WHERE g0.rownum = t0.rownum AND isnull(g0.node,0) = isnull(t0.node,0) and isnull(g0.leaf,0) = isnull(t0.leaf,0)) UNION SELECT t0.*,CASE WHEN t1.rowtype IN (10,11) THEN 10 WHEN t1.rowtype = 0 AND t1.setdefid = 0 THEN 5 WHEN t1.rowtype IS NULL THEN 5 ELSE t1.rowtype END as rowtype ,fm0.format,CAST('' AS VARCHAR(20)) as acct_string,tr.tabkey,tr.tabkey2,isnull(cs.currsymb,0) as currsymb,ISNULL(rs.sortind,0) as sortind,'1' as bookcode,'USD' as rptcurr, t1.suppress_round FROM ##drilldata530 AS t0 LEFT JOIN df_dfx_rowdtl AS t1 ON t0.rownum = t1.rownum AND t1.rowsetid = 17 LEFT JOIN #rowformats AS fm0 ON fm0.rownum = t0.rownum LEFT JOIN #tabrow AS tr ON tr.rownum = t0.rownum LEFT JOIN #currsymb AS cs ON cs.rownum = t0.rownum AND ISNULL(cs.leaf,'') = ISNULL(t0.leaf,'') AND ISNULL(cs.node,'') = ISNULL(t0.node,'') LEFT JOIN #rowsort AS rs ON isnull(rs.node,'') = isnull(t0.node,'') AND isnull(rs.leaf,'') = isnull(t0.leaf,'') AND rs.rownum = t0.rownum WHERE NOT (resulttype = 'A' AND masked_glnum IS NULL) AND (t0.rownum <= 8          OR t0.rownum IN (9998,9999)) AND ISNULL(t1.noprint,0) <> 1 AND isnull(t1.rowtype,'0') <> '9'	AND NOT EXISTS(SELECT * FROM #zero AS j0 WHERE j0.resulttype = t0.resulttype AND j0.node = isnull(t0.node,0) and j0.leaf = isnull(t0.leaf,0) and j0.rownum = t0.rownum and j0.parentrefid = isnull(t0.parentrefid,0) and j0.acctid = isnull(t0.acctid,0))AND NOT EXISTS (SELECT * FROM ##related530 AS g0 WHERE g0.rownum = t0.rownum AND isnull(g0.node,0) = isnull(t0.node,0) and isnull(g0.leaf,0) = isnull(t0.leaf,0)) ORDER BY resulttype desc,t0.node,t0.leaf,sortind,t0.rownum,masked_glnum

TRUNCATE TABLE dbo.df_dfx_cache11_3
HERE!
SELECT * INTO dbo.df_dfx_cache11_3 FROM #glacctrow