SELECT pagestr, rowstr, colstr, sum(case den when 0 then 0 else num /
den end) ratio,sum(num),sum(den) From (SELECT pagestr, rowstr, colstr,
sum(ESTIMATED_VALUE) num, sum(Denom) den From ( select
coalesce(pagestr,'`0000 Total') pagestr, coalesce(rowstr,'`0000 Total')
rowstr, coalesce(colstr,'`0000 Total') colstr, sum(estimated_value)
estimated_value,sum(denom) denom from (SELECT case 1 when 1 then
'`0001 None`' end as pagestr,
FS_FIADB.EVALIDATORFUNCTIONS.congcdLabel(plot.congcd) as rowstr, case
coalesce(tree.spgrpcd,-1) when -1 then '`0004 Not available' when
least(tree.spgrpcd,24) then '`0001 Softwoods' when
least(tree.spgrpcd,48) then '`0002 Hardwoods' when
least(tree.spgrpcd,50) then '`0003 other' when least(tree.spgrpcd,52)
then '`0001 Softwoods' when least(tree.spgrpcd,54) then '`0002
Hardwoods' else '`0003 Other' end as colstr, SUM((GRM.TPAGROW_UNADJ *
(CASE WHEN COALESCE(GRM.SUBPTYP_GRM,0) = 0 THEN (0) WHEN
GRM.SUBPTYP_GRM = 1 THEN POP_STRATUM.ADJ_FACTOR_SUBP WHEN
GRM.SUBPTYP_GRM = 2 THEN POP_STRATUM.ADJ_FACTOR_MICR WHEN
GRM.SUBPTYP_GRM = 3 THEN POP_STRATUM.ADJ_FACTOR_MACR ELSE (0) END) *
(CASE WHEN BE.ONEORTWO = 2 THEN (CASE WHEN (GRM.COMPONENT = 'SURVIVOR'
OR GRM.COMPONENT = 'INGROWTH' OR GRM.COMPONENT LIKE 'REVERSION%') THEN
(TREE.VOLCFSND/PLOT.REMPER) WHEN (GRM.COMPONENT LIKE 'CUT%' OR
GRM.COMPONENT LIKE 'DIVERSION%') THEN (TRE_MIDPT.VOLCFSND/PLOT.REMPER)
ELSE (0) END) ELSE (CASE WHEN (GRM.COMPONENT = 'SURVIVOR' OR
GRM.COMPONENT = 'CUT1' OR GRM.COMPONENT = 'DIVERSION1' OR GRM.COMPONENT =
'MORTALITY1') THEN CASE WHEN TRE_BEGIN.TRE_CN IS NOT NULL THEN
-(TRE_BEGIN.VOLCFSND/PLOT.REMPER) ELSE -(PTREE.VOLCFSND/PLOT.REMPER) END
ELSE (0) END) END))*POP_STRATUM.EXPNS) AS ESTIMATED_VALUE, 0 as denom
FROM FS_FIADB.BEGINEND BE, FS_FIADB.POP_STRATUM POP_STRATUM JOIN
FS_FIADB.POP_PLOT_STRATUM_ASSGN POP_PLOT_STRATUM_ASSGN ON
(POP_STRATUM.CN = POP_PLOT_STRATUM_ASSGN.STRATUM_CN) JOIN FS_FIADB.PLOT
PLOT ON (POP_PLOT_STRATUM_ASSGN.PLT_CN = PLOT.CN) JOIN FS_FIADB.PLOTGEOM
PLOTGEOM ON (PLOT.CN = PLOTGEOM.CN) JOIN FS_FIADB.PLOT PPLOT ON
(PLOT.PREV_PLT_CN = PPLOT.CN) JOIN FS_FIADB.COND PCOND ON
(PLOT.PREV_PLT_CN = PCOND.PLT_CN) JOIN FS_FIADB.COND COND ON (PLOT.CN =
COND.PLT_CN) JOIN FS_FIADB.TREE TREE ON (TREE.CONDID = COND.CONDID AND
TREE.PLT_CN = PLOT.CN AND TREE.PREVCOND = PCOND.CONDID) LEFT OUTER JOIN
FS_FIADB.TREE PTREE ON (TREE.PREV_TRE_CN = PTREE.CN) LEFT OUTER JOIN
FS_FIADB.TREE_GRM_BEGIN TRE_BEGIN ON (TREE.CN = TRE_BEGIN.TRE_CN) LEFT
OUTER JOIN FS_FIADB.TREE_GRM_MIDPT TRE_MIDPT ON (TREE.CN =
TRE_MIDPT.TRE_CN) LEFT OUTER JOIN (SELECT TRE_CN, DIA_BEGIN, DIA_MIDPT,
DIA_END, SUBP_COMPONENT_AL_FOREST AS COMPONENT,
SUBP_SUBPTYP_GRM_AL_FOREST AS SUBPTYP_GRM, SUBP_TPAGROW_UNADJ_AL_FOREST
AS TPAGROW_UNADJ FROM FS_FIADB.TREE_GRM_COMPONENT) GRM ON (TREE.CN =
GRM.TRE_CN) where tree.prevcond=PCOND.condid and PCOND.cond_status_cd=1
and cond.cond_status_cd=1 AND ((pop_stratum.rscd=33 and
pop_stratum.evalid=131803)) and 1=1 GROUP BY case 1 when 1 then
'`0001 None`' end,FS_FIADB.EVALIDATORFUNCTIONS.congcdLabel(plot.congcd)
,case coalesce(tree.spgrpcd,-1) when -1 then '`0004 Not available' when
least(tree.spgrpcd,24) then '`0001 Softwoods' when
least(tree.spgrpcd,48) then '`0002 Hardwoods' when
least(tree.spgrpcd,50) then '`0003 other' when least(tree.spgrpcd,52)
then '`0001 Softwoods' when least(tree.spgrpcd,54) then '`0002
Hardwoods' else '`0003 Other' end ) tmpxxx group by
cube(pagestr,rowstr,colstr) Union select coalesce(pagestr,'`0000
Total') pagestr, coalesce(rowstr,'`0000 Total') rowstr,
coalesce(colstr,'`0000 Total') colstr, sum(estimated_value)
estimated_value,sum(denom) denom from (SELECT case 1 when 1 then
'`0001 None`' end as pagestr,
FS_FIADB.EVALIDATORFUNCTIONS.congcdLabel(plot.congcd) as rowstr, case
coalesce(tree.spgrpcd,-1) when -1 then '`0004 Not available' when
least(tree.spgrpcd,24) then '`0001 Softwoods' when
least(tree.spgrpcd,48) then '`0002 Hardwoods' when
least(tree.spgrpcd,50) then '`0003 other' when least(tree.spgrpcd,52)
then '`0001 Softwoods' when least(tree.spgrpcd,54) then '`0002
Hardwoods' else '`0003 Other' end as colstr, SUM(0) AS ESTIMATED_VALUE,
SUM(POP_STRATUM.EXPNS*GRM.TPAREMV_UNADJ * (CASE WHEN
COALESCE(GRM.SUBPTYP_GRM, 0) = 0 THEN (0) WHEN GRM.SUBPTYP_GRM = 1 THEN
POP_STRATUM.ADJ_FACTOR_SUBP WHEN GRM.SUBPTYP_GRM = 2 THEN
POP_STRATUM.ADJ_FACTOR_MICR WHEN GRM.SUBPTYP_GRM = 3 THEN
POP_STRATUM.ADJ_FACTOR_MACR ELSE (0) END) * (CASE WHEN (GRM.COMPONENT
LIKE 'CUT%' OR GRM.COMPONENT LIKE 'DIVERSION%') THEN TRE_MIDPT.VOLCFSND
ELSE (0) END)) AS DENOM FROM FS_FIADB.POP_STRATUM POP_STRATUM JOIN
FS_FIADB.POP_PLOT_STRATUM_ASSGN POP_PLOT_STRATUM_ASSGN ON
(POP_STRATUM.CN = POP_PLOT_STRATUM_ASSGN.STRATUM_CN) JOIN FS_FIADB.PLOT
PLOT ON (POP_PLOT_STRATUM_ASSGN.PLT_CN = PLOT.CN) JOIN FS_FIADB.PLOTGEOM
PLOTGEOM ON (PLOT.CN = PLOTGEOM.CN) JOIN FS_FIADB.COND COND ON (PLOT.CN
= COND.PLT_CN) JOIN (SELECT P.PREV_PLT_CN, T.* FROM FS_FIADB.PLOT P,
FS_FIADB.TREE T WHERE P.CN = T.PLT_CN) TREE ON ((TREE.CONDID =
COND.CONDID) AND (TREE.PLT_CN = COND.PLT_CN)) LEFT OUTER JOIN
FS_FIADB.PLOT PPLOT ON (PLOT.PREV_PLT_CN = PPLOT.CN) LEFT OUTER JOIN
FS_FIADB.COND PCOND ON ((TREE.PREVCOND = PCOND.CONDID) AND
(TREE.PREV_PLT_CN = PCOND.PLT_CN)) LEFT OUTER JOIN FS_FIADB.TREE PTREE
ON (TREE.PREV_TRE_CN = PTREE.CN) LEFT OUTER JOIN FS_FIADB.TREE_GRM_BEGIN
TRE_BEGIN ON (TREE.CN = TRE_BEGIN.TRE_CN) LEFT OUTER JOIN
FS_FIADB.TREE_GRM_MIDPT TRE_MIDPT ON (TREE.CN = TRE_MIDPT.TRE_CN) LEFT
OUTER JOIN (SELECT TRE_CN, DIA_BEGIN, DIA_MIDPT, DIA_END,
SUBP_COMPONENT_AL_FOREST AS COMPONENT, SUBP_SUBPTYP_GRM_AL_FOREST AS
SUBPTYP_GRM, SUBP_TPAREMV_UNADJ_AL_FOREST AS TPAREMV_UNADJ FROM
FS_FIADB.TREE_GRM_COMPONENT) GRM ON (TREE.CN = GRM.TRE_CN) where
tree.prevcond=PCOND.condid and PCOND.cond_status_cd=1 and
cond.cond_status_cd=1 AND ((pop_stratum.rscd=33 and
pop_stratum.evalid=131803)) and 1=1 GROUP BY case 1 when 1 then
'`0001 None`' end,FS_FIADB.EVALIDATORFUNCTIONS.congcdLabel(plot.congcd)
,case coalesce(tree.spgrpcd,-1) when -1 then '`0004 Not available' when
least(tree.spgrpcd,24) then '`0001 Softwoods' when
least(tree.spgrpcd,48) then '`0002 Hardwoods' when
least(tree.spgrpcd,50) then '`0003 other' when least(tree.spgrpcd,52)
then '`0001 Softwoods' when least(tree.spgrpcd,54) then '`0002
Hardwoods' else '`0003 Other' end ) tmpyyy group by
cube(pagestr,rowstr,colstr)) tmptable GROUP BY pagestr, rowstr, colstr)
tmp2table group by pagestr, rowstr, colstr order by pagestr, rowstr,
colstr
|