例如：表test (id,name ,score，class) 说明下：这个逻辑不存在的，关键问题怎么解决
select score+20 as s,( case when id in(1,2,3) then s else s+10 end ) as c from test;这个运行的时候 在case语句中的 s总报错 ！我现在就想用s这个别名，除了建个临时表还有其它什么办法 ？？？？
select score+20 as s,( case when id in(1,2,3) then s else s+10 end ) as c from test;这个运行的时候 在case语句中的 s总报错 ！我现在就想用s这个别名，除了建个临时表还有其它什么办法 ？？？？
select score+20 as s,( case when id in(1,2,3) then score+20 else score+30 end ) as c from test;
select score+20 as s,( case when id in(1,2,3) then score+20 else score+30 end ) as c from test;
from
(
select score+20 s , id from test
) t;
要么就用临时表 要么就再嵌套一层 在外层用s
create or replace package body sta_contract_pkg is procedure pro_sta_contract(i_etl_date in varchar2,
o_sqlcode out integer,
o_msg out varchar2) is
begin
o_sqlcode :=0;
o_msg:='SUCCESS';
insert into sta_contract(CONTRACT_ID ,
CONTRACT_CATE_TYPE ,
CUSTOMER_ID ,
CUST_MNG_ID ,
PRODUCT_CDE ,
TP_COA_ID ,
LOAN_GRADE_CDE ,
EXTERNAL_CONTRACT_NBR ,
CONTRACT_TRM ,
CONTRACT_START_DTE ,
CONTRACT_END_DTE ,
CONTRACT_ACTIVATION_DTE ,
CONTRACT_CREATION_DTE ,
OVERDUE_DD ,
LIVE_STS ,
CONTRACT_MATURITY_DTE ,
CONTRACT_CLOSING_DTE ,
CONTRACT_TYP ,
CLOSING_IND ,
NET_REFUND_AMT ,
CITY_CDE ,
LOAN_CLASSIFICATION_CDE ,
AREA_TYPE_CDE ,
PROJECT_NBR ,
LEASE_CATEGORY_CDE ,
ASSET_CATEGORY ,
FP_GROUP_ID ,
FINANCIAL_PRODUCT_ID ,
PROJECT_NME ,
DEPARTMENT_CDE ,
CHANNEL_CDE ,
TOTAL_DISBURSEMENT_AMT ,
MONTH_DISBURSEMENT_AMT ,
YEAR_DISBURSEMENT_AMT ,
SECURITY_DEPOSIT_AMT ,
HANDLING_FEE_AMT ,
MANAGMENT_FEE_AMT ,
BAL ,
MON_AVG_BAL ,
INTEREST_BAL ,
PAYABLE_PRINCIPAL_AMT ,
PAYABLE_INTEREST_AMT ,
INTEREST_RTE ,
OVERDUE_INTEREST_RTE ,
BASE_RTE ,
ACTUAL_RTE ,
XIRR_RTE ,
XNPV_AMT ,
CUR_GENERAL_PROVI_AMT ,
CUR_SPECIFIC_PROV_AMT ,
ORG_CDE ,
INDUSTRY_TYPE_CDE ,
INDUSTRY_SUBTYPE_CDE ,
LOW_COST_IND ,
TRANS_IND ,
FTP_INTEREST_RATE ,
ORIGINAL_ORIGINAL_AMT ,
ASSET_AMT ,
INTEREST_INCOME_AMT ,
INTEREST_OUTCOME_AMT ,
NON_INTEREST_INCOME_AMT ,
NON_INTEREST_OUTCOME_AMT ,
-- PROVISION_AMT ,
-- ASSET_NBR ,
-- FTP_INTEREST_OUTCOME_AMT ,
-- FTP_INTEREST_INCOME_AMT ,
-- NET_INTEREST_AMT ,
-- TAX_AMT ,
-- OPER_COST_AMT ,
-- NET_INCOME_AMT ,
-- CAPITAL_COST_AMT ,
-- PRODUCTIVENESS_AMT ,
-- ASSESS_PROFIT_AMT ,
-- ASSESS_PROFIT_FEE_AMT ,
-- SELL_FEE_AMT ,
-- PROJ_SUPPORT_FEE_AMT ,
-- TOTAL_ADJUST_AMT
)
select
cont.CONTRACT_ID as CONTRACT_ID , --合同号
'A05001' as CONTRACT_CATE_TYPE, --合同类别代码
cb.business_partner_id as CUSTOMER_ID , --客户号
cr.business_partner_id as CUST_MNG_ID , --客户经理号
lp.PRODUCT_CDE as PRODUCT_CDE , --产品号
'1' as TP_COA_ID , --FTP定价ID
cont.LOAN_GRADE_CDE as LOAN_GRADE_CDE , --贷款级别代码
cont.EXTERNAL_CONTRACT_NBR as EXTERNAL_CONTRACT_NBR ,--外部合同号
HERE (case when length(contract_maturity_dte) is null then contract_end_dte-contract_activation_dte
else contract_maturity_dte-contract_activation_dte end) as CONTRACT_TRM, --期限
-- '暂时没取' as CONTRACT_SECTION_TRM ,----期限分段
cont.CONTRACT_START_DTE as CONTRACT_START_DTE ,---开始日期
cont.CONTRACT_END_DTE as CONTRACT_END_DTE ,--结束日期
cont.CONTRACT_ACTIVATION_DTE as CONTRACT_ACTIVATION_DTE ,--生效日期
cont.CONTRACT_CREATION_DTE as CONTRACT_CREATION_DTE ,--合同创建日期
cont.OVERDUE_DD as OVERDUE_DD , ---逾期天数
cont.LIVE_STS as LIVE_STS , -----合同当前状态
cont.CONTRACT_MATURITY_DTE as CONTRACT_MATURITY_DTE ,--合同到期日
cont.CONTRACT_CLOSING_DTE as CONTRACT_CLOSING_DTE ,--合同关闭日期
cont.CONTRACT_TYP as CONTRACT_TYP ,--合同类型
cont.CLOSING_IND as CLOSING_IND ,--结束标识
cont.NET_REFUND_AMT as NET_REFUND_AMT ,--净偿还金额
cont.CITY_CDE as CITY_CDE , --城市代码
cont.LOAN_CLASSIFICATION_CDE as LOAN_CLASSIFICATION_CDE ,--五级分类代码
cont.AREA_TYPE_CDE as AREA_TYPE_CDE ,--区域类型代码
cont.PROJECT_NBR as PROJECT_NBR , --项目号
cont.LEASE_CATEGORY as LEASE_CATEGORY_CDE ,--租赁类别代码
cont.ASSET_CATEGORY as ASSET_CATEGORY ,--资产类别
(select code from ma_busi_code_map where sour_id = 'CMS' and sour_code_type = 'Z12' and sour_cde=cont.FP_GROUP_ID) as FP_GROUP_ID,--金融产品组ID
to_char(cont.FINANCIAL_PRODUCT_ID) as FINANCIAL_PRODUCT_ID ,--金融产品ID
cont.PROJECT_NME as PROJECT_NME , --项目名
su.DEPARTMENT_CDE as DEPARTMENT_CDE ,--部门号
cont.MARKETING_CHANNEL as CHANNEL_CDE ,--渠道号
case select lvl2_cde from v_dim_product where product_cde =lp.PRODUCT_CDE
when 'MS110000' then td.DISBURSEMENT_AMT
when 'MS120000' then ASSET_AMT
end case , -- 历史累计投放
case select lvl2_cde from v_dim_product where product_cde =lp.PRODUCT_CDE
when 'MS110000' then select substr(to_char(disbursement_dte,'yyyymmdd'),1,6),sum(disbursement_amt) from ODS_TRANCHES_DETAIL group by substr(to_char(disbursement_dte,'yyyymmdd'),1,6)
when 'MS120000' then ASSET_AMT --这个也要 按月来取 回头作
end case as MONTH_DISBURSEMENT_AMT ,--当月投放
case select lvl2_cde from v_dim_product where product_cde =lp.PRODUCT_CDE
when 'MS110000' then select substr(to_char(disbursement_dte,'yyyymmdd'),1,4),sum(disbursement_amt) from ODS_TRANCHES_DETAIL group by substr(to_char(disbursement_dte,'yyyymmdd'),1,4)
when 'MS120000' then ASSET_AMT --这个也要 按年来取 回头作
end case as YEAR_DISBURSEMENT_AMT ,--当年累计投放
td.SEC_DEPOSIT_AMT as SECURITY_DEPOSIT_AMT ,--保证金金额
as HANDLING_FEE_AMT , --zan mei zuo 手续费金额(当月发生额)
td.MANAGMENT_FEE_AMOUNT as MANAGMENT_FEE_AMT , --管理费金额
(case select lvl2_cde from v_dim_product where product_cde =lp.PRODUCT_CDE
when MS110000 then tp.BAL
when MS120000 then ASSET_AMT
END) as BAL , ---余额
'' as MON_AVG_BAL , --本月日均余额
(case select lvl2_cde from v_dim_product where product_cde =lp.PRODUCT_CDE
when 'MS110000' then tp.INTEREST_BAL) as INTEREST_BAL , --未偿还利息
(case select lvl2_cde from v_dim_product where product_cde =lp.PRODUCT_CDE
when 'MS110000' then tp.PAYABLE_PRINCIPAL_AMT) as PAYABLE_PRINCIPAL_AMT ,--本期应收本金
(case select lvl2_cde from v_dim_product where product_cde =lp.PRODUCT_CDE
when 'MS110000' then tp.PAYABLE_INTEREST_AMT) as PAYABLE_INTEREST_AMT ,--本期应收利息
agree.INTEREST_RTE as INTEREST_RTE, --合同利率
agree.OVERDUE_INTEREST_RTE as OVERDUE_INTEREST_RTE ,--逾期利率
agree.BASE_RTE as BASE_RTE ,--基准利率
agree.ACTUAL_RTE as ACTUAL_RTE ,--实际利率
agree.XIRR_RTE as XIRR_RTE ,--XIRR利率
agree.XNPV_AMT as XNPV_AMT , --XNPV金额
pt.provision_amt as CUR_GENERAL_PROVI_AMT ,--一般减值准备发生额
opt.provision_amt as CUR_SPECIFIC_PROV_AMT ,--特殊减值准备发生额
'' as ORG_CDE ,--机构组织
tt.industry_type_cde as INDUSTRY_TYPE_CDE ,--行业类型代码
tt.industry_subtype_cde as INDUSTRY_SUBTYPE_CDE ,--行业子类型代码
(case when SECURITY_DEPOSIT_AMT>=TOTAL_DISBURSEMENT_AMT then 'T'
else 'F' end ) as LOW_COST_IND ,---低成本标识
CONTRACT_TRM as TRANS_IND ,--流转天数
--- as FTP_INTEREST_RATE ,
--- as ORIGINAL_ORIGINAL_AMT ,
ca.ASSET_AMT as ASSET_AMT ,--资产额
sum(rt.INTEREST_SETTLED_AMT) as INTEREST_INCOME_AMT ,--利息收入(月发生)
-- as INTEREST_OUTCOME_AMT ,
sum(cc.settle_amt) as NON_INTEREST_INCOME_AMT , --非利息收入(月发生)
sum(pc.charge_payable_amt) as NON_INTEREST_OUTCOME_AMT , --非利息支出(月发生)
/*as PROVISION_AMT ,
as ASSET_NBR ,
as FTP_INTEREST_OUTCOME_AMT ,
as FTP_INTEREST_INCOME_AMT ,
as NET_INTEREST_AMT ,
as TAX_AMT ,
as OPER_COST_AMT ,
as NET_INCOME_AMT ,
as CAPITAL_COST_AMT ,
as PRODUCTIVENESS_AMT ,
as ASSESS_PROFIT_AMT ,
as ASSESS_PROFIT_FEE_AMT ,
as SELL_FEE_AMT ,
as PROJ_SUPPORT_FEE_AMT ,
as TOTAL_ADJUST_AMT */
FROM
ODS_CONTRACT cont
left outer join (select * from ods_contract_bp where role_cd=3) cb
on cont.contract_id=cb.contract_id
left outer join (select * from ODS_CONTRACT_RESPONSIBLE_PERSO where designation_cde=5) cr
on cont.contract_id=cr.contract_id
left outer join STA_LEASECATE_PRODUCT_RELA lp
on cont.LEASE_CATEGORY_CDE=lp.LEASE_CATEGORY_CDE
left outer join ODS_bp_sys_user su
on cr.business_partner_id=su.pb_secondary_id
left outer join (select contract_id,sum(DISBURSEMENT_AMT) as DISBURSEMENT_AMT from ODS_TRANCHES_DETAIL group by contract_id) td
on td.contract_id=cont.contract_id
left outer join (select sch.CONTRACT_ID as CONTRACT_ID,
sum(sch.principal_amt - plan.principal_settled_amt) as BAL,
sum(sch.interest_amt-plan.interest_settled_amt) as INTEREST_BAL,
sum(case when to_char(due_dte, 'YYYYMM') = substr('20101230', 1,6) then sch.PRINCIPAL_AMT else 0 end) as PAYABLE_PRINCIPAL_AMT,
sum(case when to_char(due_dte, 'YYYYMM') = substr('20101230', 1,6) then sch.INTEREST_AMT else 0 end) as PAYABLE_INTEREST_AMT, from
ODS_RENTAL_AMORTIZATION_SCHEDU sch,ODS_REPAYMENT_PLAN plan
where sch.CONTRACT_ID = plan.CONTRACT_ID and
sch.AGREEMENT_SEQ = plan.AGREEMENT_SEQ and
sch.RENTAL_ID = plan.RENTAL_ID
group by sch.CONTRACT_ID ) tp
on cont.contract_id = tp.contract_id
left outer join (select contract_id,
sum(INTEREST_RTE) as INTEREST_RTE,
sum(OVERDUE_INTEREST_RTE) as OVERDUE_INTEREST_RTE,
sum(BASE_RTE) as BASE_RTE,
sum(ACTUAL_RTE) as ACTUAL_RTE,
sum(XIRR_RTE) as XIRR_RTE,
sum(XNPV_AMT) as XNPV_AMT
from ODS_FINANCIAL_AGREEMENT
group by contract_id) agree
on agree.contract_id=cont.contract_id
left outer join (select a.contract_id as contract_id,
a.execution_dte,
a.provision_typ,
nvl(a.provision_amt,0)-nvl(c.provision_amt,0) as provision_amt
from ods_PROVISION_TRANSACTION a
left outer join ods_PROVISION_TRANSACTION c
on a.contract_id=c.contract_id and a.old_provision_dte=c.provision_dte and a.provision_typ=c.provision_typ
where substr(to_char(a.execution_dte,'yyyymmdd'),1,6)=substr(ETL_DATE,1,6) and a.provision_typ='G'
)pt
pt.contract_id=cont.contract_id
left outer join (select a.contract_id as contract_id,
a.execution_dte,
a.provision_typ,
nvl(a.provision_amt,0)-nvl(c.provision_amt,0) as provision_amt
from ods_PROVISION_TRANSACTION a
left outer join ods_PROVISION_TRANSACTION c
on a.contract_id=c.contract_id and a.old_provision_dte=c.provision_dte and a.provision_typ=c.provision_typ
where substr(to_char(a.execution_dte,'yyyymmdd'),1,6)=to_char(ETL_DATE,1,6) and a.provision_typ='S'
)opt
on opt.contract_id=cont.contract_id
left outer join(select contract_id,
industry_type_cde,
industry_subtype_cde
from (select * from ods_CONTRACT_bp where role_cde=3) b
left outer join ods_bp_company c
on b.business_partner_id=c.business_partner_id
)tt on cont.contract_id=tt.contract_id
left outer join(select b.contract_id as contract_id,
nvl(c.vehicle_sale_amt,0) as ASSET_AMT
from ods_contract_asset b
left outer join ods_vehicle_information c
on b.asset_id=c.asset_id
) ca on ca.contract_id=cont.contract_id
left outer join (select contract_id,INTEREST_SETTLED_AMT from ods_RENTAL_TRANS b
where b.execution_dte>=to_date(substr(ETL_DATE,1,6)||'01','yyyymmdd') and b.execution_dte<=to_date(ETL_DATE,'yyyymmdd')
) rt on rt.contract_id= cont.contract_id
left outer join (select settle_amt,
contract_id
from ods_ctr_charge_receivable
where execution_dte>=to_date(substr(ETL_DATE,1,6)||'01','yyyymmdd') and b.execution_dte<=to_date(ETL_DATE,'yyyymmdd')
) cc on cc.contract_id=cont.contract_id
left outer join (select contract_id,
charge_payable_amt
from ods_PAYABLE_CONTRACT b
left outer join ods_payable_main c
on b.payable_id=c.payable_id and execution_dte>=to_date(substr(ETL_DATE,6)||'01','yyyymmdd')and execution_dte<=to_date(ETL_DATE,'yyyymmdd')
) pc on pc.contract_id=cont.contract_id;
end;
end sta_contract_pkg;