I am trying to use rollup for a total, but it doesn't work because what I'm totaling is too conditional. I even tried adding on the totals to the end using a union and a specialized function, but for some reason, the order by stops working when I do the union (all field aliases and types are the same).
I know the structure of the query itself probably isn't the best, but if anyone has any ideas on how I can get the grand total, please tell me. The subtotals from the rollup work fine, though.
The order by query runs fine against both parts of the union when they are run individually, but once I make it part of the union - the first part of the order by is an unknown field and the second is not a SELECTed list expression. I've commented out the union here. The statement works fine without it except for the grand total being all 0s. I kind of understand why it's all 0s, but not how to get around that.
This is running in Oracle 10g2.
I am trying to get totals for decisions by each of the four component values: ex_land, ex_imp,tx_land,tx_imp and then subtotals for each component values. That works for the following query, but I cannot get a grand total through the rollup.
CREATE
TABLE "FL_APP"
(
"APP_ID" NUMBER(*,0),
"FL_STAT_CODE" VARCHAR2(20 BYTE),
CONSTRAINT "FL_APP PK" PRIMARY KEY ("APP_ID") ENABLE,
CONSTRAINT "FL_APP_FL_STAT_DIM_FK1" FOREIGN KEY ("FL_STAT_CODE")
REFERENCES "FL"."FL_STAT_DIM" ("FL_STAT_CODE") ON
DELETE
CASCADE ENABLE
) ;
CREATE
TABLE "FL_APP_PROP"
(
"APP_ID" NUMBER(*,0),
"FL_DECISION_CODE" VARCHAR2(10 BYTE),
"CUR_EX_LAND_MV" NUMBER,
"CUR_TX_LAND_MV" NUMBER,
"CUR_EX_IMP_MV" NUMBER,
"CUR_TX_IMP_MV" NUMBER,
"NEW_EX_LAND_MV" NUMBER,
"NEW_TX_LAND_MV" NUMBER,
"NEW_EX_IMP_MV" NUMBER,
"NEW_TX_IMP_MV" NUMBER,
CONSTRAINT "FL_APP_PROP_PK" PRIMARY KEY ("APP_ID") ENABLE,
CONSTRAINT "FL_APP_PROP_DEC_FK" FOREIGN KEY ("FL_DECISION_CODE")
REFERENCES "FL"."FL_DECISION_DIM" ("FL_DECISION_CODE") ENABLE
) ;
CREATE
TABLE "FL"."FL_DECISION_DIM"
(
"FL_DECISION_CODE" VARCHAR2(6 BYTE),
"CODE_DESC" VARCHAR2(44 BYTE),
CONSTRAINT "FL_DECISION_DIM PK" PRIMARY KEY ("FL_DECISION_CODE") ENABLE
) ;
create or replace
function decisionsort(fl_decision_code varchar2)
return number
--returns number to sort by for fl_decision_code so that appears in specified order
as
sortarg number;
begin
case fl_decision_code
when 'DMV' then
sortarg := 1;
when 'DNU' then
sortarg := 2;
when 'DAE' then
sortarg := 3;
when 'DL' then
sortarg := 4;
when 'AMVNU' then
sortarg := 5;
when 'AAE' then
sortarg := 6;
when 'AMALC' then
sortarg := 7;
when 'WITHDR' then
sortarg := 8;
else
sortarg := 9;
end case;
return sortarg;
end decisionsort;
select * from (
select nvl(fd.code_desc,'Total') code_desc, k.* from
(select fl_decision_code,
decode(mvcomponent, 1, 'Tax Land',2,'Tax Imp', 3, 'Ex Land',4,'Ex Imp') mvcomponentname,
mvcomponent,
decode(mvcomponent, 1, sum(inctxland_mv), 2, sum(inctximp_mv), 3, sum(incexland_mv), 4, sum(inceximp_mv)) increasetotal,
decode(mvcomponent, 1, sum(dectxland_mv), 2, sum(dectximp_mv),3, sum(decexland_mv), 4, sum(deceximp_mv)) decreasetotal,
decode(mvcomponent, 1, sum(inctxland_mv-dectxland_mv), 2, sum(inctximp_mv- dectximp_mv), 3, sum(incexland_mv-decexland_mv), 4, sum(inceximp_mv-deceximp_mv)) nettotal
from
(select cd.fl_decision_code,
mvcomponent,
cd.code_desc,
curmv,
newmv,
(case when cur_tx_land_mv < new_tx_land_mv and mvcomponent = 1
then new_tx_land_mv - cur_tx_land_mv
else 0
end
) inctxland_mv,
(case when cur_tx_imp_mv < new_tx_imp_mv and mvcomponent = 2
then new_tx_imp_mv - cur_tx_imp_mv
else 0
end
)inctximp_mv,
(case when cur_ex_land_mv < new_ex_land_mv and mvcomponent = 3
then new_ex_land_mv - cur_ex_land_mv
else 0
end
) incexland_mv,
(case when cur_ex_imp_mv < new_ex_imp_mv and mvcomponent = 4
then new_ex_imp_mv - cur_ex_imp_mv
else 0
end
)inceximp_mv,
(case when cur_tx_land_mv > new_tx_land_mv and mvcomponent = 1
then cur_tx_land_mv - new_tx_land_mv
else 0
end
)dectxland_mv,
(case when cur_tx_imp_mv > new_tx_imp_mv and mvcomponent = 2
then cur_tx_imp_mv - new_tx_imp_mv
else 0
end
)dectximp_mv,
(case when cur_ex_land_mv > new_ex_land_mv and mvcomponent = 3
then cur_ex_land_mv - new_ex_land_mv
else 0
end
)decexland_mv,
(case when cur_ex_imp_mv > new_ex_imp_mv and mvcomponent = 4
then cur_ex_imp_mv - new_ex_imp_mv
else 0
end
)deceximp_mv
from
(
SELECT
fp.fl_decision_code,
cur_ex_land_mv+cur_tx_land_mv+cur_ex_imp_mv+cur_tx_imp_mv curmv,
cur_ex_land_mv,cur_tx_land_mv,cur_ex_imp_mv,cur_tx_imp_mv,
new_ex_land_mv+new_tx_land_mv+new_ex_imp_mv+new_tx_imp_mv newmv,
new_ex_land_mv,new_tx_land_mv,new_ex_imp_mv,new_tx_imp_mv
FROM
fl_app f,
fl_app_prop fp
WHERE
f.app_id = fp.app_id
AND fl_decision_code like 'A%'
and fl_stat_code in ('COMPL','APPR_BY_SUP')
and cur_ex_land_mv+cur_tx_land_mv+cur_ex_imp_mv+cur_tx_imp_mv = new_ex_land_mv+new_tx_land_mv+new_ex_imp_mv+new_tx_imp_mv
) i
right OUTER JOIN fl_decision_dim cd
ON i.fl_decision_code = cd.fl_decision_code
cross join (select rownum mvcomponent from fl_decision_dim where rownum <= 4)
where cd.fl_decision_code like 'A%'
) j
GROUP BY
rollup(mvcomponent,fl_decision_code) )k
left outer join fl_decision_dim fd
on k.fl_decision_code = fd.fl_decision_code
)
--union all
--select 'Grand Total' code_desc, null,null fl_decision_code, null mvcomponent,componentgrandtotal(3), componentgrandtotal(3), 0 from dual
order by mvcomponent nulls last, decisionsort(fl_decision_code) nulls last ;