HI,

This view is working fine. but i need to change the view definition. But unfortunately i am unable to compile this view. The systax is wierd and looks like some portion of the view sql is hidden.

specially this part ( t coalesce function doesnt end)..yet the view is runing fine and giving correct data ? how to unhide the rest of the SQL..it shoule be there otherwise the view would not have executed properly

CASE WHEN VPC.PROFIT_CNTR_CODE IS NOT NULL
THEN CASE WHEN pa.cmpy_code IN ('K404','K416','K417','K437') THEN 'NZC' ELSE CASE WHEN PL2.PL_LVL_2_NODE_CODE = 'SME'
THEN COALESCE(PL2.PL_LVL_2_NODE_CODE, ' from
(select a.mth_id, a.cmpy_code, a.cc_code, a.pc_code, a.plob, a.ccy_code, sum(a.mgt_exp) as mgt_exp, sum(a.chc) as chc, sum(a.non_tech)




-----------------------------------------------------


CREATE VIEW "MSTRPPM"."V_DATA_POST_ALLOC_CALC_POST"
( "MNGMT_FLG", "APRA_FLG", "EIFRS_FLG", "SEGMENT_CODE", "SUBSEGMENT_CODE",
"COUNTRY_CODE", "STATE_CODE", "PRODUCT_CODE", "CMPY_CODE", "MTH_ID",
"CC_CODE", "PC_CODE", "PLOB", "GLOB", "CCY_CODE",
"CC_TRANS", "MGT_EXP", "CHC", "NON_TECH" )
AS
select CASE WHEN pa.cmpy_code IN ('K400','K404','K410','K416') THEN 'Y' ELSE 'N'
END AS MNGMT_FLG, CASE WHEN pa.cmpy_code IN ('K400','K404','K410','K411','K432','K416','K417', 'K437') THEN 'Y' ELSE 'N'
END AS APRA_FLG, CASE WHEN pa.cmpy_code IN ('K400','K404','K410','K432','K416','K437') THEN 'Y' ELSE 'N'
END AS EIFRS_FLG, CASE WHEN coalesce(pa.pc_code,'') in ('PK4100298') then 'COM' ELSE CASE WHEN VPC.PROFIT_CNTR_CODE IS NULL THEN 'GLO' ELSE 'COM'
END
END AS SEGMENT_CODE,
CASE WHEN coalesce(pa.pc_code,'') in ('PK4100298') then 'MID' ELSE CASE WHEN VPC.PROFIT_CNTR_CODE IS NOT NULL
THEN CASE WHEN pa.cmpy_code IN ('K404','K416','K417','K437') THEN 'NZC' ELSE CASE WHEN PL2.PL_LVL_2_NODE_CODE = 'SME'
THEN COALESCE(PL2.PL_LVL_2_NODE_CODE, ' from
(select a.mth_id, a.cmpy_code, a.cc_code, a.pc_code, a.plob, a.ccy_code, sum(a.mgt_exp) as mgt_exp, sum(a.chc) as chc, sum(a.non_tech) as non_tech, '' as cc_trans
from EBIPPM.DATA_PPM_MI_GI_POST_ALLOC_2 a where cc_code not like ('CK40090%') and cc_code not in ('CK400910','CK400911','CK400912','CK400913','CK40 0914','CK400915','CK400916','CK400919')
group by cmpy_code, mth_id, cc_code, pc_code, plob, ccy_code
union all
select c.mth_id, c.cmpy_code, d.cc_orig, c.pc_code, c.plob, c.ccy_code, d.alloc_pct*c.mgt_exp/100.000000 as alloc_mgt_exp, d.alloc_pct*c.chc/100.000000 as alloc_chc,
d.alloc_pct*c.non_tech/100.000000 as alloc_non_tech, d.cc_trans from
(select c.mth_id, c.cmpy_code, c.cc_code, c.pc_code, c.plob, c.ccy_code, sum(c.mgt_exp) + sum(c.exp_mga) as mgt_exp,
sum(c.chc) as chc, sum(c.non_tech) as non_tech
from EBIPPM.DATA_PPM_MI_GI_POST_ALLOC_2 c where
(c.cc_code like ('CK40090%') or c.cc_code in ('CK400910','CK400911','CK400912','CK400913','CK40 0914','CK400915','CK400916','CK400919') )
group by c.mth_id, c.cmpy_code, c.cc_code, c.pc_code, c.plob, c.ccy_code ) c
inner join
(select a.mth_id, a.cc_orig, a.cc_trans, sum(a.alloc_val) as alloc_tot, b.trans_tot, case when b.trans_tot = 0 then 0 else sum(a.alloc_val) * 100.000000 / b.trans_tot end as alloc_pct
from EBIPPM.DATA_PPM_MI_GI_POST_ALLOC_1 a
inner join
(select mth_id, cc_trans, sum(alloc_val) as trans_tot from EBIPPM.DATA_PPM_MI_GI_POST_ALLOC_1
where cc_orig not like ('CK40090%') and cc_orig not in ('CK400910','CK400911','CK400912','CK400913','CK40 0914','CK400915','CK400916') group by mth_id, cc_trans )
b on b.cc_trans = a.cc_trans and b.mth_id = a.mth_id where a.cc_orig not like ('CK40090%') and a.cc_orig not in ('CK400910','CK400911','CK400912','CK400913','CK40 0914','CK400915','CK400916','CK400919')
group by a.mth_id, a.cc_orig, a.cc_trans, b.trans_tot ) d
on d.cc_trans = c.cc_code and d.mth_id = c.mth_id )
AS pa
LEFT OUTER JOIN
MODEL.V_DIM_GI_COMMERCIAL_PROFIT_CENTRES VPC ON VPC.PROFIT_CNTR_CODE = pa.pc_code
LEFT OUTER JOIN EBIPPM.REL_PPM_POSTING_LOB PL2 ON PL2.POSTING_LOB_CODE = pa.plob AND PL2.PL_HIER_NAME LIKE 'AU6%'
LEFT OUTER JOIN EBIPPM.REF_PPM_MI_CONFIG_VALUES REF3 ON REF3.PARAM_CODE = 'GC_PC_SUBSEGMENT' AND REF3.PARAM_VALUE_1 = pa.pc_code
LEFT OUTER JOIN EBIPPM.REF_PPM_MI_CONFIG_VALUES REF ON REF.PARAM_CODE = 'PRODUCT' AND REF.PARAM_VALUE_2 = PL2.PL_LVL_3_NODE_CODE
LEFT OUTER JOIN MSTRPPM.V_REL_PRFT_CENTRE_STATE S ON S.PROFIT_CNTR = pc_code
WITH NO ROW MOVEMENT;