xsong123 commented on issue #15147:
URL:
https://github.com/apache/shardingsphere/issues/15147#issuecomment-1050447683
> SQL parser is necessary procedure. ShardingSphere need the SQL parser
module to understand SQL, it is the basic module which can not skip.
**but now complex sql which's tables don't sharding parser error:**
Caused by: org.apache.ibatis.executor.ExecutorException: Error preparing
statement. Cause:
com.google.common.util.concurrent.UncheckedExecutionException:
org.apache.shardingsphere.sql.parser.exception.SQLParsingException: You have an
error in your SQL syntax
at
org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:97)
~[mybatis-3.5.3.jar:3.5.3]
at
org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)
~[mybatis-3.5.3.jar:3.5.3]
at
org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:87)
~[mybatis-3.5.3.jar:3.5.3]
at
org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)
~[mybatis-3.5.3.jar:3.5.3]
at
org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
~[mybatis-3.5.3.jar:3.5.3]
at
org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
~[mybatis-3.5.3.jar:3.5.3]
... 39 more
Caused by: com.google.common.util.concurrent.UncheckedExecutionException:
org.apache.shardingsphere.sql.parser.exception.SQLParsingException: You have an
error in your SQL syntax
at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2051)
~[guava-29.0-jre.jar:?]
at com.google.common.cache.LocalCache.get(LocalCache.java:3951)
~[guava-29.0-jre.jar:?]
at com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:3974)
~[guava-29.0-jre.jar:?]
at
com.google.common.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4958)
~[guava-29.0-jre.jar:?]
at
com.google.common.cache.LocalCache$LocalLoadingCache.getUnchecked(LocalCache.java:4964)
~[guava-29.0-jre.jar:?]
at
org.apache.shardingsphere.infra.parser.sql.SQLStatementParserEngine.parse(SQLStatementParserEngine.java:48)
~[shardingsphere-infra-parser-5.0.0.jar:5.0.0]
at
org.apache.shardingsphere.infra.parser.ShardingSphereSQLParserEngine.parse0(ShardingSphereSQLParserEngine.java:71)
~[shardingsphere-infra-parser-5.0.0.jar:5.0.0]
at
org.apache.shardingsphere.infra.parser.ShardingSphereSQLParserEngine.parse(ShardingSphereSQLParserEngine.java:60)
~[shardingsphere-infra-parser-5.0.0.jar:5.0.0]
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:157)
~[shardingsphere-jdbc-core-5.0.0.jar:5.0.0]
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:128)
~[shardingsphere-jdbc-core-5.0.0.jar:5.0.0]
at
org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection.prepareStatement(ShardingSphereConnection.java:78)
~[shardingsphere-jdbc-core-5.0.0.jar:5.0.0]
at sun.reflect.GeneratedMethodAccessor142.invoke(Unknown Source) ~[?:?]
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
~[?:1.8.0_181]
at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_181]
at
org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:55)
~[mybatis-3.5.3.jar:3.5.3]
at com.sun.proxy.$Proxy276.prepareStatement(Unknown Source) ~[?:?]
at
org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:86)
~[mybatis-3.5.3.jar:3.5.3]
at
org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
~[mybatis-3.5.3.jar:3.5.3]
at
org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)
~[mybatis-3.5.3.jar:3.5.3]
at
org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:87)
~[mybatis-3.5.3.jar:3.5.3]
at
org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)
~[mybatis-3.5.3.jar:3.5.3]
at
org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
~[mybatis-3.5.3.jar:3.5.3]
at
org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
~[mybatis-3.5.3.jar:3.5.3]
... 39 more
Caused by:
org.apache.shardingsphere.sql.parser.exception.SQLParsingException: You have an
error in your SQL syntax
at
org.apache.shardingsphere.sql.parser.core.database.parser.SQLParserExecutor.twoPhaseParse(SQLParserExecutor.java:68)
~[shardingsphere-sql-parser-engine-5.0.0.jar:5.0.0]
at
org.apache.shardingsphere.sql.parser.core.database.parser.SQLParserExecutor.parse(SQLParserExecutor.java:49)
~[shardingsphere-sql-parser-engine-5.0.0.jar:5.0.0]
at
org.apache.shardingsphere.sql.parser.api.SQLParserEngine.parse(SQLParserEngine.java:51)
~[shardingsphere-sql-parser-engine-5.0.0.jar:5.0.0]
at
org.apache.shardingsphere.infra.parser.sql.SQLStatementParserExecutor.parse(SQLStatementParserExecutor.java:47)
~[shardingsphere-infra-parser-5.0.0.jar:5.0.0]
at
org.apache.shardingsphere.infra.parser.cache.SQLStatementCacheLoader.load(SQLStatementCacheLoader.java:40)
~[shardingsphere-infra-parser-5.0.0.jar:5.0.0]
at
org.apache.shardingsphere.infra.parser.cache.SQLStatementCacheLoader.load(SQLStatementCacheLoader.java:29)
~[shardingsphere-infra-parser-5.0.0.jar:5.0.0]
at
com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3529)
~[guava-29.0-jre.jar:?]
at
com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2278)
~[guava-29.0-jre.jar:?]
at
com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2155)
~[guava-29.0-jre.jar:?]
at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2045)
~[guava-29.0-jre.jar:?]
at com.google.common.cache.LocalCache.get(LocalCache.java:3951)
~[guava-29.0-jre.jar:?]
at com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:3974)
~[guava-29.0-jre.jar:?]
at
com.google.common.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4958)
~[guava-29.0-jre.jar:?]
at
com.google.common.cache.LocalCache$LocalLoadingCache.getUnchecked(LocalCache.java:4964)
~[guava-29.0-jre.jar:?]
at
org.apache.shardingsphere.infra.parser.sql.SQLStatementParserEngine.parse(SQLStatementParserEngine.java:48)
~[shardingsphere-infra-parser-5.0.0.jar:5.0.0]
at
org.apache.shardingsphere.infra.parser.ShardingSphereSQLParserEngine.parse0(ShardingSphereSQLParserEngine.java:71)
~[shardingsphere-infra-parser-5.0.0.jar:5.0.0]
at
org.apache.shardingsphere.infra.parser.ShardingSphereSQLParserEngine.parse(ShardingSphereSQLParserEngine.java:60)
~[shardingsphere-infra-parser-5.0.0.jar:5.0.0]
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:157)
~[shardingsphere-jdbc-core-5.0.0.jar:5.0.0]
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:128)
~[shardingsphere-jdbc-core-5.0.0.jar:5.0.0]
at
org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection.prepareStatement(ShardingSphereConnection.java:78)
~[shardingsphere-jdbc-core-5.0.0.jar:5.0.0]
at sun.reflect.GeneratedMethodAccessor142.invoke(Unknown Source) ~[?:?]
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
~[?:1.8.0_181]
at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_181]
at
org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:55)
~[mybatis-3.5.3.jar:3.5.3]
at com.sun.proxy.$Proxy276.prepareStatement(Unknown Source) ~[?:?]
at
org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:86)
~[mybatis-3.5.3.jar:3.5.3]
at
org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
~[mybatis-3.5.3.jar:3.5.3]
at
org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)
~[mybatis-3.5.3.jar:3.5.3]
at
org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:87)
~[mybatis-3.5.3.jar:3.5.3]
at
org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)
~[mybatis-3.5.3.jar:3.5.3]
at
org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
~[mybatis-3.5.3.jar:3.5.3]
at
org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
~[mybatis-3.5.3.jar:3.5.3]
... 39 more
**the sql as below:**
INSERT INTO lsr_standing_bk_fb2107202348_20211125 (
PRODUCT_CODE,
DATA_DATE,
CONTRACT_NO,
ASSET_CODE,
ASSET_NAME,
ASSET_AMT,
BUSINESS_TYPE,
VALUE_DATE,
MATURITY_DATE,
ASSET_TERM_DAY,
ASSET_TERM_MONTH,
ASSET_TERM_YEAR,
REPAYMENT_TYPE,
ASSET_INCOME_TYPE,
RATE_TYPE,
FEE_RATE_TYPE,
FEE_TYPE,
GUAR_TYPE,
MAIN_GUAR_TYPE,
LOAN_USAGE,
IS_PURCHASE,
IS_CUR_PURCHASE,
PURCHASE_DATE,
PURCHASE_AMT,
COLL_INIT_VALUATION,
LTV,
HAND_HOUSE,
COLL_HOUSE_CITY_TYPE,
COLL_HOUSE_AREA,
INIT_COLL_RATE,
INIT_DATE_PRIN_BAL,
CUR_OPEN_PRIN_BAL,
CUR_CLOSE_PRIN_BAL,
INIT_DATE_ACCT_AGE_DAY,
CUR_OPEN_ACCT_AGE_DAY,
CUR_CLOSE_ACCT_AGE_DAY,
INIT_DATE_ACCT_AGE_MONTH,
CUR_OPEN_ACCT_AGE_MONTH,
CUR_CLOSE_ACCT_AGE_MONTH,
INIT_DATE_ACCT_AGE_YEAR,
CUR_OPEN_ACCT_AGE_YEAR,
CUR_CLOSE_ACCT_AGE_YEAR,
INIT_DATE_REMAIN_TERM_DAY,
CUR_OPEN_REMAIN_TERM_DAY,
CUR_CLOSE_REMAIN_TERM_DAY,
INIT_DATE_REMAIN_TERM_MONTH,
CUR_OPEN_REMAIN_TERM_MONTH,
CUR_CLOSE_REMAIN_TERM_MONTH,
INIT_DATE_REMAIN_TERM_YEAR,
CUR_OPEN_REMAIN_TERM_YEAR,
CUR_CLOSE_REMAIN_TERM_YEAR,
INIT_DATE_ANNL_YIELD,
CUR_OPEN_ANNL_YIELD,
CUR_CLOSE_ANNL_YIELD,
FEE_RATE,
INIT_DATE_COMPREHENSIVE_YIELD,
CUR_OPEN_COMPREHENSIVE_YIELD,
CUR_CLOSE_COMPREHENSIVE_YIELD,
INIT_DATE_FIVE_CLASS,
CUR_OPEN_FIVE_CLASS,
CUR_CLOSE_FIVE_CLASS,
IS_CHANGE_FIVE_CLASS,
INIT_DATE_OVERDUE_DAY,
INIT_DATE_OVERDUE_DEGREE,
OPEN_OVERDUE_DAY,
OPEN_OVERDUE_DEGREE,
CLOSE_OVERDUE_DAY,
CLOSE_OVERDUE_DEGREE,
IS_ARREARS,
OVERDUE_INTEREST,
IS_PREPAYMENT,
IS_DEFAULT,
CUST_NO,
CUST_NAME,
CUST_TYPE,
CUST_PROVINCE,
CUST_CITY,
NS_FIRST_INDUSTRY,
NS_SECOND_INDUSTRY,
JOB,
GENDER,
MARITAL_STATUS,
BIRTHDATE,
INIT_DATE_INCOME_DEPT_RATIO,
CUR_OPEN_INCOME_DEPT_RATIO,
CUR_CLOSE_INCOME_DEPT_RATIO,
INIT_DATE_CUST_AGE,
CUR_OPEN_CUST_AGE,
CUR_CLOSE_CUST_AGE,
INIT_DATE_ANNL_INCOME,
CUR_OPEN_ANNL_INCOME,
CUR_CLOSE_ANNL_INCOME,
INIT_DATE_CREDIT_SCORING,
CUR_OPEN_CREDIT_SCORING,
CUR_CLOSE_CREDIT_SCORING,
INIT_DATE_CREDIT_RATING,
CUR_OPEN_CREDIT_RATING,
CUR_CLOSE_CREDIT_RATING,
IS_CHANGE_CREDIT_RATING,
DEFAULT_DATE,
DEFAULT_DATE_PRIN_BAL,
CUR_DEFAULT_REMAIN_PRIN_BAL,
LAWSUIT_STATUS,
DEFAULT_TYPE,
DISP_START_DATE,
DISP_END_DATE,
CUR_DEFAULT_RCVR_PRIN,
CUR_DEFAULT_RCVR_INT,
CUR_DEFAULT_RCVR_AMT,
DEFAULT_CUML_RCVR_PRIN,
DEFAULT_CUML_RCVR_INT,
CLOSE_DEFAULT_RCVR_AMT,
DISP_BUDGET,
CUR_DISP_PERF_COST,
CLOSE_DISP_PERF_COST,
CUR_ACT_DEDUCT_PERF_COST,
CLOSE_ACT_DEDUCT_PERF_COST,
PRIN_LOSS,
INT_LOSS,
IS_LOSS,
LOSS_DATE,
CUR_PLAN_RCVR_PRIN,
CUR_PLAN_RCVR_INT,
CUR_PLAN_RCVR_SUB_TOTAL,
NEXT_PLAN_RCVR_PRIN,
NEXT_PLAN_RCVR_INT,
NORM_RCVR_CNT,
PART_PRIOR_RCVR_CNT,
PRIOR_SETTL_CNT,
ARREARS_RCVR_CNT,
DEFAULT_RCVR_CNT,
DEPOSIT_RETN_CNT,
CNT_SUB_TOTAL,
NORM_RCVR_PRIN,
PART_PRIOR_RCVR_PRIN,
PRIOR_SETTL_PRIN,
ARREARS_RCVR_PRIN,
DEFAULT_RCVR_PRIN,
DEPOSIT_RETN_PRIN,
PRIN_SUB_TOTAL,
NORM_RCVR_INT,
PART_PRIOR_RCVR_INT,
PRIOR_SETTL_INT,
ARREARS_RCVR_INT,
DEFAULT_RCVR_INT,
DEPOSIT_RETN_INT,
INT_SUB_TOTAL,
NORM_RCVR_PENALTY,
PART_PRIOR_RCVR_PENALTY,
PRIOR_SETTL_PENALTY,
ARREARS_RCVR_PENALTY,
DEFAULT_RCVR_PENALTY,
DEPOSIT_RETN_PENALTY,
PENALTY_SUB_TOTAL,
NORM_RCVR_DEFAULT_INT,
PART_PRIOR_RCVR_DEFAULT_INT,
PRIOR_SETTL_DEFAULT_INT,
ARREARS_RCVR_DEFAULT_INT,
DEFAULT_RCVR_DEFAULT_INT,
DEPOSIT_RETN_DEFAULT_INT,
DEFAULT_INT_SUB_TOTAL,
NORM_RCVR_COMPOUND_INT,
PART_PRIOR_RCVR_COMPOUND_INT,
PRIOR_SETTL_COMPOUND_INT,
ARREARS_RCVR_COMPOUND_INT,
DEFAULT_RCVR_COMPOUND_INT,
DEPOSIT_RETN_COMPOUND_INT,
COMPOUND_INT_SUB_TOTAL,
NORM_RCVR_MGMT_FEE,
PART_PRIOR_RCVR_MGMT_FEE,
PRIOR_SETTL_MGMT_FEE,
ARREARS_RCVR_MGMT_FEE,
DEFAULT_RCVR_MGMT_FEE,
DEPOSIT_RETN_MGMT_FEE,
MGMT_FEE_SUB_TOTAL,
NORM_RCVR_OTHER_AMT,
PART_PRIOR_RCVR_OTHER_AMT,
PRIOR_SETTL_OTHER_AMT,
ARREARS_RCVR_OTHER_AMT,
DEFAULT_RCVR_OTHER_AMT,
DEPOSIT_RETN_OTHER_AMT,
OTHER_AMT_SUB_TOTAL,
INT_RCVR_AMT,
PRIN_RCVR_AMT,
RCVR_AMT_SUB_TOTAL,
RDM_REPO_STATUS,
RDM_REPO_TYPE,
RDM_REPO_DATE,
RDM_REPO_DEAL_DATE,
RDM_REPO_TRADE_DATE,
RDM_REPO_PRIN_BAL,
RDM_REPO_PRICE,
RDM_REASON,
CLOSE_ASSET_PRIN_STATUS,
CLOSE_ASSET_SETTL_STATUS,
GUAR_CATEGORY,
REPO_PRIN_BAL,
REPO_AMT,
REPO_TIME,
CREATE_TIME,
UPDATE_TIME
) SELECT
'XMDM202107202348',
to_date (
'2021-11-25 0:00:00',
'yyyy-MM-dd hh24:mi:ss'
),
IFNULL(s.contractno, s.assetcode) contract_no,
s.assetcode asset_code,
s.assetname asset_name,
s.FinanceAmount asset_amt,
CASE
WHEN sdd1.cnname IS NOT NULL THEN
sdd1.cnname
ELSE
s.AssetSubType
END business_type,
s.ValueDate value_date,
s.MaturityDate maturity_date,
s.termday asset_term_day,
s.term asset_term_month,
s.termyear asset_term_year,
CASE
WHEN sdd2.cnname IS NOT NULL THEN
sdd2.cnname
ELSE
s.paytype
END repayment_type,
NULL asset_income_type,
CASE
WHEN s.ratetype = 'FI' THEN
'固定'
WHEN s.ratetype = 'FL' THEN
'浮动'
WHEN s.ratetype = 'FV' THEN
'变动'
ELSE
s.ratetype
END rate_type,
NULL fee_rate_type,
NULL fee_type,
CASE
WHEN sdd3.cnname IS NOT NULL THEN
sdd3.cnname
ELSE
s.guaranteeway
END guar_type,
CASE
WHEN s.MainGuaranteeWay = 'XY' THEN
'00'
WHEN s.MainGuaranteeWay = 'BZ' THEN
'01'
WHEN s.MainGuaranteeWay = 'DY' THEN
'02'
WHEN s.MainGuaranteeWay = 'ZY' THEN
'03'
WHEN s.MainGuaranteeWay = 'YQSG' THEN
'04'
ELSE
s.MainGuaranteeWay
END main_guar_type,
CASE
WHEN sdd4.cnname IS NOT NULL THEN
sdd4.cnname
ELSE
s.ContractUse
END loan_usage,
CASE
WHEN s.portfoliocode != 'ZCB202107202450' THEN
'1'
ELSE
'0'
END is_purchase,
CASE
WHEN s.portfoliocode != 'ZCB202107202450'
AND s.CyclePurchaseDate >= to_date (
'2021-10-26 0:00:00',
'yyyy-MM-dd hh24:mi:ss'
)
AND s.CyclePurchaseDate <= to_date (
'2021-11-25 0:00:00',
'yyyy-MM-dd hh24:mi:ss'
) THEN
'1'
ELSE
'0'
END is_cur_purchase,
s.CyclePurchaseDate purchase_date,
CASE
WHEN s.portfoliocode != 'ZCB202107202450' THEN
s.packetbalance
ELSE
NULL
END purchase_amt,
NULL coll_init_valuation,
NULL ltv,
NULL hand_house,
NULL coll_house_city_type,
NULL coll_house_area,
NULL init_coll_rate,
lpbi.prin_bal init_date_prin_bal,
CASE
WHEN s.portfoliocode = 'ZCB202107202450'
AND 5 = 1 THEN
lpbi.prin_bal
ELSE
NULL
END cur_open_prin_bal,
s.principalbal cur_close_prin_bal,
lpbi.acct_age_day init_date_acct_age_day,
CASE
WHEN s.portfoliocode = 'ZCB202107202450'
AND 5 = 1 THEN
lpbi.acct_age_day
ELSE
NULL
END cur_open_acct_age_day,
s.accountageday cur_close_acct_age_day,
lpbi.acct_age_month init_date_acct_age_month,
CASE
WHEN s.portfoliocode = 'ZCB202107202450'
AND 5 = 1 THEN
lpbi.acct_age_month
ELSE
NULL
END cur_open_acct_age_month,
s.accountage cur_close_acct_age_month,
lpbi.acct_age_year init_date_acct_age_year,
CASE
WHEN s.portfoliocode = 'ZCB202107202450'
AND 5 = 1 THEN
lpbi.acct_age_year
ELSE
NULL
END cur_open_acct_age_year,
s.accountageyear cur_close_acct_age_year,
lpbi.remain_term_day init_date_remain_term_day,
CASE
WHEN s.portfoliocode = 'ZCB202107202450'
AND 5 = 1 THEN
lpbi.remain_term_day
ELSE
NULL
END cur_open_remain_term_day,
s.retermday cur_close_remain_term_day,
lpbi.remain_term_month init_date_remain_term_month,
CASE
WHEN s.portfoliocode = 'ZCB202107202450'
AND 5 = 1 THEN
lpbi.remain_term_month
ELSE
NULL
END cur_open_remain_term_month,
s.reterm cur_close_remain_term_month,
lpbi.remain_term_year init_date_remain_term_year,
CASE
WHEN s.portfoliocode = 'ZCB202107202450'
AND 5 = 1 THEN
lpbi.remain_term_year
ELSE
NULL
END cur_open_remain_term_year,
s.retermyear cur_close_remain_term_year,
lpbi.annl_yield init_date_annl_yield,
CASE
WHEN s.portfoliocode = 'ZCB202107202450'
AND 5 = 1 THEN
lpbi.annl_yield
ELSE
NULL
END cur_open_annl_yield,
s.nowrate cur_close_annl_yield,
NULL fee_rate,
lpbi.comprehensive_yield init_date_comprehensive_yield,
CASE
WHEN s.portfoliocode = 'ZCB202107202450'
AND 5 = 1 THEN
lpbi.comprehensive_yield
ELSE
NULL
END cur_open_comprehensive_yield,
s.ComprehensiveRate cur_close_comprehensive_yield,
lpbi.five_class init_date_annl_yield,
CASE
WHEN s.portfoliocode = 'ZCB202107202450'
AND 5 = 1 THEN
lpbi.five_class
ELSE
NULL
END cur_open_five_class,
CASE
WHEN sdd7.cnname IS NOT NULL THEN
sdd7.cnname
ELSE
s.fiveclass
END cur_close_five_class,
'1' is_change_five_class,
lpbi.overdue_day init_date_overdue_day,
lpbi.overdue_degree init_date_overdue_degree,
CASE
WHEN s.portfoliocode = 'ZCB202107202450'
AND 5 = 1 THEN
lpbi.overdue_day
ELSE
NULL
END open_overdue_day,
CASE
WHEN s.portfoliocode = 'ZCB202107202450'
AND 5 = 1 THEN
lpbi.overdue_degree
ELSE
NULL
END open_overdue_degree,
s.existencedays close_overdue_day,
NULL close_overdue_degree,
CASE
WHEN s.existencedays > 61
AND s.existencedays <=
AND s.isdefault = 'N' THEN
'1'
ELSE
'0'
END is_arrears,
s.OwnInterest overdue_interest,
CASE
WHEN s.IsHappenPrepayment = 'Y' THEN
'1'
ELSE
'0'
END is_prepayment,
CASE
WHEN tad.default_date IS NULL THEN
'0'
ELSE
'1'
END is_default,
s.custcode cust_no,
s.custname cust_name,
'0' CUST_TYPE,
CASE
WHEN sdd8.cnname IS NOT NULL THEN
sdd8.cnname
ELSE
s.province
END cust_province,
CASE
WHEN sdd9.cnname IS NOT NULL THEN
sdd9.cnname
ELSE
s.city
END cust_city,
NULL ns_first_industry,
CASE
WHEN sdd11.cnname IS NOT NULL THEN
sdd11.cnname
ELSE
s.tradetype
END ns_second_industry,
CASE
WHEN sdd12.cnname IS NOT NULL THEN
sdd12.cnname
ELSE
s.jobtype
END job,
CASE
WHEN sdd13.cnname IS NOT NULL THEN
sdd13.cnname
ELSE
s.sex
END gender,
CASE
WHEN sdd14.cnname IS NOT NULL THEN
sdd14.cnname
ELSE
s.marriagestate
END marital_status,
s.birthday birthdate,
lpbi.income_dept_ratio init_date_income_dept_ratio,
CASE
WHEN s.portfoliocode = 'ZCB202107202450'
AND 5 = 1 THEN
lpbi.income_dept_ratio
ELSE
NULL
END cur_open_income_dept_ratio,
CASE
WHEN s.deptincomeratio IS NULL THEN
CASE
WHEN IFNULL(lpbi.prin_bal, 0) = 0 THEN
0
ELSE
s.yearincome / lpbi.prin_bal
END
ELSE
s.deptincomeratio
END cur_close_income_dept_ratio,
lpbi.cust_age init_date_cust_age,
CASE
WHEN s.portfoliocode = 'ZCB202107202450'
AND 5 = 1 THEN
lpbi.cust_age
ELSE
NULL
END cur_open_cust_age,
s.age cur_close_cust_age,
lpbi.annl_income init_date_annl_income,
CASE
WHEN s.portfoliocode = 'ZCB202107202450'
AND 5 = 1 THEN
lpbi.annl_income
ELSE
NULL
END cur_open_annl_income,
s.yearincome cur_close_annl_income,
lpbi.credit_scoring init_date_credit_scoring,
CASE
WHEN s.portfoliocode = 'ZCB202107202450'
AND 5 = 1 THEN
lpbi.credit_scoring
ELSE
NULL
END cur_open_credit_scoring,
s.EvaluatePoint cur_close_credit_scoring,
lpbi.credit_rating init_date_credit_rating,
CASE
WHEN s.portfoliocode = 'ZCB202107202450'
AND 5 = 1 THEN
lpbi.credit_rating
ELSE
NULL
END cur_open_credit_rating,
NULL cur_close_credit_rating,
'1' is_change_credit_rating,
tad.default_date,
tad.default_date_prin_bal,
tad.cur_default_remain_prin_bal,
tad.lawsuit_status,
tad.default_type,
tad.disp_start_date,
tad.disp_end_date,
tadd.cur_default_rcvr_prin,
tadd.cur_default_rcvr_int,
tadd.cur_default_rcvr_amt,
tadd.default_cuml_rcvr_prin,
tadd.default_cuml_rcvr_int,
tadd.close_default_rcvr_amt,
tad.disp_budget,
tadd.cur_disp_perf_cost,
tadd.close_disp_perf_cost,
tadd.cur_act_deduct_perf_cost,
tadd.close_act_deduct_perf_cost,
tad.prin_loss,
tad.int_loss,
tad.is_loss,
tad.loss_date,
NULL cur_plan_rcvr_prin,
NULL cur_plan_rcvr_int,
NULL cur_plan_rcvr_sub_total,
NULL next_plan_rcvr_prin,
NULL next_plan_rcvr_int,
CASE
WHEN tar.norm_rcvr_cnt > 0 THEN
1
ELSE
0
END norm_rcvr_cnt,
CASE
WHEN tar.part_prior_rcvr_cnt > 0 THEN
1
ELSE
0
END part_prior_rcvr_cnt,
CASE
WHEN tar.prior_settl_cnt > 0 THEN
1
ELSE
0
END prior_settl_cnt,
CASE
WHEN tar.arrears_rcvr_cnt > 0 THEN
1
ELSE
0
END arrears_rcvr_cnt,
CASE
WHEN tar.default_rcvr_cnt > 0 THEN
1
ELSE
0
END default_rcvr_cnt,
CASE
WHEN tar.deposit_retn_cnt > 0 THEN
1
ELSE
0
END deposit_retn_cnt,
CASE
WHEN tar.norm_rcvr_cnt > 0 THEN
1
ELSE
0
END + CASE
WHEN tar.part_prior_rcvr_cnt > 0 THEN
1
ELSE
0
END + CASE
WHEN tar.prior_settl_cnt > 0 THEN
1
ELSE
0
END + CASE
WHEN tar.arrears_rcvr_cnt > 0 THEN
1
ELSE
0
END + CASE
WHEN tar.default_rcvr_cnt > 0 THEN
1
ELSE
0
END + CASE
WHEN tar.deposit_retn_cnt > 0 THEN
1
ELSE
0
END cnt_sub_total,
tar.norm_rcvr_prin,
tar.part_prior_rcvr_prin,
tar.prior_settl_prin,
tar.arrears_rcvr_prin,
tar.default_rcvr_prin,
tar.deposit_retn_prin,
tar.prin_sub_total,
tar.norm_rcvr_int,
tar.part_prior_rcvr_int,
tar.prior_settl_int,
tar.arrears_rcvr_int,
tar.default_rcvr_int,
tar.deposit_retn_int,
tar.int_sub_total,
tar.norm_rcvr_penalty,
tar.part_prior_rcvr_penalty,
tar.prior_settl_penalty,
tar.arrears_rcvr_penalty,
tar.default_rcvr_penalty,
tar.deposit_retn_penalty,
tar.penalty_sub_total,
tar.norm_rcvr_default_int,
tar.part_prior_rcvr_default_int,
tar.prior_settl_default_int,
tar.arrears_rcvr_default_int,
tar.default_rcvr_default_int,
tar.deposit_retn_default_int,
tar.default_int_sub_total,
tar.norm_rcvr_compound_int,
tar.part_prior_rcvr_compound_int,
tar.prior_settl_compound_int,
tar.arrears_rcvr_compound_int,
tar.default_rcvr_compound_int,
tar.deposit_retn_compound_int,
tar.compound_int_sub_total,
tar.norm_rcvr_mgmt_fee,
tar.part_prior_rcvr_mgmt_fee,
tar.prior_settl_mgmt_fee,
tar.arrears_rcvr_mgmt_fee,
tar.default_rcvr_mgmt_fee,
tar.deposit_retn_mgmt_fee,
tar.mgmt_fee_sub_total,
tar.norm_rcvr_other_amt,
tar.part_prior_rcvr_other_amt,
tar.prior_settl_other_amt,
tar.arrears_rcvr_other_amt,
tar.default_rcvr_other_amt,
tar.deposit_retn_other_amt,
tar.other_amt_sub_total,
tar.int_sub_total + tar.penalty_sub_total + tar.default_int_sub_total +
tar.compound_int_sub_total + tar.mgmt_fee_sub_total + tar.other_amt_sub_total
int_rcvr_amt,
tar.prin_sub_total prin_rcvr_amt,
tar.prin_sub_total + tar.int_sub_total + tar.penalty_sub_total +
tar.default_int_sub_total + tar.compound_int_sub_total + tar.mgmt_fee_sub_total
+ tar.other_amt_sub_total rcvr_amt_sub_total,
IFNULL(trr.rdm_repo_status, '00') rdm_repo_status,
trr.rdm_repo_type,
trr.rdm_repo_date,
trr.rdm_repo_deal_date,
CASE
WHEN trr.rdm_repo_date IS NOT NULL THEN
ELSE
NULL
END rdm_repo_trade_date,
trr.rdm_repo_prin_bal,
trr.rdm_repo_price,
trr.rdm_reason,
CASE
WHEN sdd15.cnname IS NOT NULL THEN
sdd15.cnname
ELSE
s.PrincipalState
END close_asset_prin_status,
CASE
WHEN s.PrincipalState = 'YJQ' THEN
'01'
ELSE
'00'
END close_asset_settl_status,
CASE
WHEN s.MainGuaranteeWay = 'XY' THEN
'00'
ELSE
'01'
END guar_category,
NULL REPOPRINCIPALBAL,
NULL REPOAMOUNT,
NULL REPOTIME,
NOW(),
NOW()
FROM
Asset_Structure_fb2107202348 s
LEFT JOIN stt_dict sdd1 ON sdd1.itemcode = s.AssetSubType
AND SDD1.clsno = 'BUSINESSTYPE'
LEFT JOIN stt_dict sdd2 ON sdd2.itemcode = s.paytype
AND SDD2.clsno = 'PERSONERLOANREPAYMENT'
LEFT JOIN stt_dict sdd3 ON sdd3.itemcode = s.guaranteeway
AND SDD3.clsno = 'GUARANTEEWAY'
LEFT JOIN stt_dict sdd4 ON sdd4.itemcode = s.ContractUse
AND SDD4.clsno = 'CONTRACTUSE'
LEFT JOIN stt_dict sdd7 ON sdd7.itemcode = s.fiveclass
AND SDD7.clsno = 'FIVECLASS'
LEFT JOIN stt_dict sdd8 ON sdd8.itemcode = s.province
AND SDD8.clsno = 'PROVINCE'
LEFT JOIN stt_dict sdd9 ON sdd9.itemcode = s.city
AND SDD9.clsno = 'CITY'
LEFT JOIN stt_dict sdd11 ON sdd11.itemcode = s.tradetype
AND SDD11.clsno = 'INTM_TRADETYPE'
LEFT JOIN stt_dict sdd12 ON sdd12.itemcode = s.jobtype
AND SDD12.clsno = 'JOBTYPE'
LEFT JOIN stt_dict sdd13 ON sdd13.itemcode = s.sex
AND SDD13.clsno = 'INTM_SEX'
LEFT JOIN stt_dict sdd14 ON sdd14.itemcode = s.marriagestate
AND SDD14.clsno = 'INTM_ISMARRIAGE'
LEFT JOIN stt_dict sdd15 ON sdd15.itemcode = s.PrincipalState
AND SDD15.clsno = 'PRINCIPALSTATE'
LEFT JOIN lsr_packet_basic_info_fb2107202348 lpbi ON s.assetcode =
lpbi.asset_code
AND lpbi.product_code = 'XMDM202107202348'
LEFT JOIN (
SELECT
ddt.DefaultDate default_date,
ddt.DefaultDateBalance default_date_prin_bal,
ddt.AssetDefaultPrin cur_default_remain_prin_bal,
CASE
WHEN ddt.FinalDisposalStatus = 'JC1' THEN
'01'
WHEN ddt.FinalDisposalStatus = 'JC2' THEN
'02'
WHEN ddt.FinalDisposalStatus = 'JC3' THEN
'31'
WHEN ddt.FinalDisposalStatus = 'JC4' THEN
'32'
WHEN ddt.FinalDisposalStatus = 'JC5' THEN
'33'
WHEN ddt.FinalDisposalStatus = 'JC6' THEN
'42'
WHEN ddt.FinalDisposalStatus = 'JC7' THEN
'41'
WHEN ddt.FinalDisposalStatus = 'JC8' THEN
'03'
ELSE
ddt.FinalDisposalStatus
END lawsuit_status,
CASE
WHEN ddt.DefaultType = 'A' THEN
'00'
WHEN ddt.DefaultType = 'B' THEN
'01'
WHEN ddt.DefaultType = 'D' THEN
'02'
WHEN ddt.DefaultType = 'E' THEN
'03'
ELSE
ddt.DefaultType
END default_type,
ddt.FirstDisposalDate disp_start_date,
ddt.DisposalEndDate disp_end_date,
ddt.DisposalBudget disp_budget,
ddt.lossprincipal prin_loss,
ddt.lossinterest int_loss,
CASE
WHEN ddt.lossprincipal > 0
OR ddt.lossinterest > 0 THEN
'1'
ELSE
'0'
END is_loss,
ddt.LossLoanOccurTime loss_date,
@num :=
IF (
@KEY = ddt.ASSETCODE,
@num + 1,
1
) rn,
@KEY := ddt.ASSETCODE assetcode
FROM
Asset_Disposal_fb2107202348 ddt,
(SELECT @KEY := '', @num := 0) t1
WHERE
ddt.productcode = 'XMDM202107202348'
AND ddt.datadate <= to_date (
'2021-11-25 0:00:00',
'yyyy-MM-dd hh24:mi:ss'
)
AND NOT EXISTS (
SELECT
ard.assetcode,
'01' rdm_repo_status,
ar.redemptiontype rdm_repo_type,
ar.backdate rdm_repo_date,
ar.dealdate rdm_repo_deal_date,
IFNULL(ard.remainamount, 0) + IFNULL(ard.cancellationamount, 0)
rdm_repo_prin_bal,
IFNULL(ard.receivedpiamount, 0) rdm_repo_price,
NULL rdm_reason
FROM
asset_redem ar,
asset_redemDetail_fb2107202348 ard
WHERE
ar.tradeid = ard.tradeid
AND ar.productcode = 'XMDM202107202348'
AND ar.backdate <= to_date (
'2021-11-25 0:00:00',
'yyyy-MM-dd hh24:mi:ss'
)
AND ar.effectflag = 'E'
AND ddt.assetcode = ard.assetcode
AND ddt.datadate > ar.DealDate
)
AND NOT EXISTS (
SELECT
crd.assetcode,
'02' rdm_repo_status,
NULL rdm_repo_type,
cr.backdate rdm_repo_date,
cr.dealdate rdm_repo_deal_date,
IFNULL(crd.receivedpiamount, 0) rdm_repo_prin_bal,
IFNULL(crd.receivedpiamount, 0) rdm_repo_price,
NULL rdm_reason
FROM
Asset_repo cr,
Asset_repoDetail_fb2107202348 crd
WHERE
cr.tradeid = crd.tradeid
AND cr.productcode = 'XMDM202107202348'
AND cr.backdate <= to_date (
'2021-11-25 0:00:00',
'yyyy-MM-dd hh24:mi:ss'
)
AND cr.effectflag = 'E'
AND ddt.assetcode = crd.assetcode
AND ddt.datadate > cr.DealDate
)
ORDER BY
ddt.assetcode,
ddt.datadate DESC
) tad ON s.assetcode = tad.assetcode
AND tad.rn = 1
LEFT JOIN (
SELECT
ddt.assetcode,
IFNULL(
SUM(
CASE
WHEN ddt.datadate >= to_date (
'2021-10-26 0:00:00',
'yyyy-MM-dd hh24:mi:ss'
) THEN
ddt.RecoveryPrincipal
ELSE
0
END
),
0
) cur_default_rcvr_prin,
IFNULL(
SUM(
CASE
WHEN ddt.datadate >= to_date (
'2021-10-26 0:00:00',
'yyyy-MM-dd hh24:mi:ss'
) THEN
ddt.RecoveryInterest
ELSE
0
END
),
0
) cur_default_rcvr_int,
IFNULL(
SUM(
CASE
WHEN ddt.datadate >= to_date (
'2021-10-26 0:00:00',
'yyyy-MM-dd hh24:mi:ss'
) THEN
IFNULL(ddt.RecoveryPrincipal, 0) +
IFNULL(ddt.RecoveryInterest, 0)
ELSE
0
END
),
0
) cur_default_rcvr_amt,
IFNULL(
SUM(ddt.RecoveryPrincipal),
0
) default_cuml_rcvr_prin,
IFNULL(
SUM(ddt.RecoveryInterest),
0
) default_cuml_rcvr_int,
IFNULL(
SUM(
IFNULL(ddt.RecoveryPrincipal, 0) +
IFNULL(ddt.RecoveryInterest, 0)
),
0
) close_default_rcvr_amt,
IFNULL(
SUM(
CASE
WHEN ddt.datadate >= to_date (
'2021-10-26 0:00:00',
'yyyy-MM-dd hh24:mi:ss'
) THEN
ddt.planexecutivecost
ELSE
0
END
),
0
) cur_disp_perf_cost,
IFNULL(
SUM(ddt.planexecutivecost),
0
) close_disp_perf_cost,
IFNULL(
SUM(
CASE
WHEN ddt.datadate >= to_date (
'2021-10-26 0:00:00',
'yyyy-MM-dd hh24:mi:ss'
) THEN
ddt.ExecutiveCost
ELSE
0
END
),
0
) cur_act_deduct_perf_cost,
IFNULL(SUM(ddt.ExecutiveCost), 0) close_act_deduct_perf_cost
FROM
Asset_Disposal_fb2107202348 ddt
WHERE
ddt.productcode = 'XMDM202107202348'
AND ddt.datadate <= to_date (
'2021-11-25 0:00:00',
'yyyy-MM-dd hh24:mi:ss'
)
AND NOT EXISTS (
SELECT
ard.assetcode,
'01' rdm_repo_status,
ar.redemptiontype rdm_repo_type,
ar.backdate rdm_repo_date,
ar.dealdate rdm_repo_deal_date,
IFNULL(ard.remainamount, 0) +
IFNULL(ard.cancellationamount, 0) rdm_repo_prin_bal,
IFNULL(ard.receivedpiamount, 0) rdm_repo_price,
NULL rdm_reason
FROM
asset_redem ar,
asset_redemDetail_fb2107202348 ard
WHERE
ar.tradeid = ard.tradeid
AND ar.productcode = 'XMDM202107202348'
AND ar.backdate <= to_date (
'2021-11-25 0:00:00',
'yyyy-MM-dd hh24:mi:ss'
)
AND ar.effectflag = 'E'
AND ddt.assetcode = ard.assetcode
AND ddt.datadate > ar.DealDate
)
AND NOT EXISTS (
SELECT
crd.assetcode,
'02' rdm_repo_status,
NULL rdm_repo_type,
cr.backdate rdm_repo_date,
cr.dealdate rdm_repo_deal_date,
IFNULL(crd.receivedpiamount, 0) rdm_repo_prin_bal,
IFNULL(crd.receivedpiamount, 0) rdm_repo_price,
NULL rdm_reason
FROM
Asset_repo cr,
Asset_repoDetail_fb2107202348 crd
WHERE
cr.tradeid = crd.tradeid
AND cr.productcode = 'XMDM202107202348'
AND cr.backdate <= to_date (
'2021-11-25 0:00:00',
'yyyy-MM-dd hh24:mi:ss'
)
AND cr.effectflag = 'E'
AND ddt.assetcode = crd.assetcode
AND ddt.datadate > cr.DealDate
)
GROUP BY
ddt.assetcode
) tadd ON s.assetcode = tadd.assetcode
LEFT JOIN (
SELECT
ar.assetcode,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'N' THEN
1
ELSE
0
END
),
0
) norm_rcvr_cnt,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'A' THEN
1
ELSE
0
END
),
0
) part_prior_rcvr_cnt,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'C' THEN
1
ELSE
0
END
),
0
) prior_settl_cnt,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'P' THEN
1
ELSE
0
END
),
0
) arrears_rcvr_cnt,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'O' THEN
1
ELSE
0
END
),
0
) default_rcvr_cnt,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'S' THEN
1
ELSE
0
END
),
0
) deposit_retn_cnt,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType IN ('N', 'A', 'C', 'P',
'O', 'S') THEN
1
ELSE
0
END
),
0
) cnt_sub_total,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'N' THEN
ar.ActPrincipal
ELSE
0
END
),
0
) norm_rcvr_prin,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'A' THEN
ar.ActPrincipal
ELSE
0
END
),
0
) part_prior_rcvr_prin,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'C' THEN
ar.ActPrincipal
ELSE
0
END
),
0
) prior_settl_prin,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'P' THEN
ar.ActPrincipal
ELSE
0
END
),
0
) arrears_rcvr_prin,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'O' THEN
ar.ActPrincipal
ELSE
0
END
),
0
) default_rcvr_prin,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'S' THEN
ar.ActPrincipal
ELSE
0
END
),
0
) deposit_retn_prin,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType IN ('N', 'A', 'C', 'P',
'O', 'S') THEN
ar.ActPrincipal
ELSE
0
END
),
0
) prin_sub_total,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'N' THEN
ar.ActInterest
ELSE
0
END
),
0
) norm_rcvr_int,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'A' THEN
ar.ActInterest
ELSE
0
END
),
0
) part_prior_rcvr_int,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'C' THEN
ar.ActInterest
ELSE
0
END
),
0
) prior_settl_int,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'P' THEN
ar.ActInterest
ELSE
0
END
),
0
) arrears_rcvr_int,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'O' THEN
ar.ActInterest
ELSE
0
END
),
0
) default_rcvr_int,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'S' THEN
ar.ActInterest
ELSE
0
END
),
0
) deposit_retn_int,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType IN ('N', 'A', 'C', 'P',
'O', 'S') THEN
ar.ActInterest
ELSE
0
END
),
0
) int_sub_total,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'N' THEN
ar.PaypenaltyAmt
ELSE
0
END
),
0
) norm_rcvr_penalty,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'A' THEN
ar.PaypenaltyAmt
ELSE
0
END
),
0
) part_prior_rcvr_penalty,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'C' THEN
ar.PaypenaltyAmt
ELSE
0
END
),
0
) prior_settl_penalty,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'P' THEN
ar.PaypenaltyAmt
ELSE
0
END
),
0
) arrears_rcvr_penalty,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'O' THEN
ar.PaypenaltyAmt
ELSE
0
END
),
0
) default_rcvr_penalty,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'S' THEN
ar.PaypenaltyAmt
ELSE
0
END
),
0
) deposit_retn_penalty,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType IN ('N', 'A', 'C', 'P',
'O', 'S') THEN
ar.PaypenaltyAmt
ELSE
0
END
),
0
) penalty_sub_total,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'N' THEN
ar.PayfineAmt
ELSE
0
END
),
0
) norm_rcvr_default_int,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'A' THEN
ar.PayfineAmt
ELSE
0
END
),
0
) part_prior_rcvr_default_int,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'C' THEN
ar.PayfineAmt
ELSE
0
END
),
0
) prior_settl_default_int,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'P' THEN
ar.PayfineAmt
ELSE
0
END
),
0
) arrears_rcvr_default_int,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'O' THEN
ar.PayfineAmt
ELSE
0
END
),
0
) default_rcvr_default_int,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'S' THEN
ar.PayfineAmt
ELSE
0
END
),
0
) deposit_retn_default_int,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType IN ('N', 'A', 'C', 'P',
'O', 'S') THEN
ar.PayfineAmt
ELSE
0
END
),
0
) default_int_sub_total,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'N' THEN
ar.actcompinterestamt
ELSE
0
END
),
0
) norm_rcvr_compound_int,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'A' THEN
ar.actcompinterestamt
ELSE
0
END
),
0
) part_prior_rcvr_compound_int,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'C' THEN
ar.actcompinterestamt
ELSE
0
END
),
0
) prior_settl_compound_int,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'P' THEN
ar.actcompinterestamt
ELSE
0
END
),
0
) arrears_rcvr_compound_int,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'O' THEN
ar.actcompinterestamt
ELSE
0
END
),
0
) default_rcvr_compound_int,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'S' THEN
ar.actcompinterestamt
ELSE
0
END
),
0
) deposit_retn_compound_int,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType IN ('N', 'A', 'C', 'P',
'O', 'S') THEN
ar.actcompinterestamt
ELSE
0
END
),
0
) compound_int_sub_total,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'N' THEN
ar.FeeAmount
ELSE
0
END
),
0
) norm_rcvr_mgmt_fee,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'A' THEN
ar.FeeAmount
ELSE
0
END
),
0
) part_prior_rcvr_mgmt_fee,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'C' THEN
ar.FeeAmount
ELSE
0
END
),
0
) prior_settl_mgmt_fee,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'P' THEN
ar.FeeAmount
ELSE
0
END
),
0
) arrears_rcvr_mgmt_fee,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'O' THEN
ar.FeeAmount
ELSE
0
END
),
0
) default_rcvr_mgmt_fee,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'S' THEN
ar.FeeAmount
ELSE
0
END
),
0
) deposit_retn_mgmt_fee,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType IN ('N', 'A', 'C', 'P',
'O', 'S') THEN
ar.FeeAmount
ELSE
0
END
),
0
) mgmt_fee_sub_total,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'N' THEN
ar.PayotherAmt
ELSE
0
END
),
0
) norm_rcvr_other_amt,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'A' THEN
ar.PayotherAmt
ELSE
0
END
),
0
) part_prior_rcvr_other_amt,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'C' THEN
ar.PayotherAmt
ELSE
0
END
),
0
) prior_settl_other_amt,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'P' THEN
ar.PayotherAmt
ELSE
0
END
),
0
) arrears_rcvr_other_amt,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'O' THEN
ar.PayotherAmt
ELSE
0
END
),
0
) default_rcvr_other_amt,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType = 'S' THEN
ar.PayotherAmt
ELSE
0
END
),
0
) deposit_retn_other_amt,
IFNULL(
SUM(
CASE
WHEN ar.RepaymentType IN ('N', 'A', 'C', 'P',
'O', 'S') THEN
ar.PayotherAmt
ELSE
0
END
),
0
) other_amt_sub_total
FROM
assetrecovery_fb2107202348 ar
WHERE
ar.productcode = 'XMDM202107202348'
AND ar.returndate >= to_date (
'2021-10-26 0:00:00',
'yyyy-MM-dd hh24:mi:ss'
)
AND ar.returndate <= to_date (
'2021-11-25 0:00:00',
'yyyy-MM-dd hh24:mi:ss'
)
AND NOT EXISTS (
SELECT
ard.assetcode,
'01' rdm_repo_status,
ar.redemptiontype rdm_repo_type,
ar.backdate rdm_repo_date,
ar.dealdate rdm_repo_deal_date,
IFNULL(ard.remainamount, 0) +
IFNULL(ard.cancellationamount, 0) rdm_repo_prin_bal,
IFNULL(ard.receivedpiamount, 0) rdm_repo_price,
NULL rdm_reason
FROM
asset_redem ar,
asset_redemDetail_fb2107202348 ard
WHERE
ar.tradeid = ard.tradeid
AND ar.productcode = 'XMDM202107202348'
AND ar.backdate <= to_date (
'2021-11-25 0:00:00',
'yyyy-MM-dd hh24:mi:ss'
)
AND ar.effectflag = 'E'
AND ar.assetcode = ard.assetcode
AND ar.returndate > ar.DealDate
)
AND NOT EXISTS (
SELECT
crd.assetcode,
'02' rdm_repo_status,
NULL rdm_repo_type,
cr.backdate rdm_repo_date,
cr.dealdate rdm_repo_deal_date,
IFNULL(crd.receivedpiamount, 0) rdm_repo_prin_bal,
IFNULL(crd.receivedpiamount, 0) rdm_repo_price,
NULL rdm_reason
FROM
Asset_repo cr,
Asset_repoDetail_fb2107202348 crd
WHERE
cr.tradeid = crd.tradeid
AND cr.productcode = 'XMDM202107202348'
AND cr.backdate <= to_date (
'2021-11-25 0:00:00',
'yyyy-MM-dd hh24:mi:ss'
)
AND cr.effectflag = 'E'
AND ar.assetcode = crd.assetcode
AND ar.returndate > cr.DealDate
)
GROUP BY
ar.assetcode
) tar ON s.assetcode = tar.assetcode
LEFT JOIN (
SELECT
ard.assetcode,
'01' rdm_repo_status,
ar.redemptiontype rdm_repo_type,
ar.backdate rdm_repo_date,
ar.dealdate rdm_repo_deal_date,
IFNULL(ard.remainamount, 0) + IFNULL(ard.cancellationamount, 0)
rdm_repo_prin_bal,
IFNULL(ard.receivedpiamount, 0) rdm_repo_price,
NULL rdm_reason
FROM
asset_redem ar,
asset_redemDetail_fb2107202348 ard
WHERE
ar.tradeid = ard.tradeid
AND ar.productcode = 'XMDM202107202348'
AND ar.backdate <= to_date (
'2021-11-25 0:00:00',
'yyyy-MM-dd hh24:mi:ss'
)
AND ar.effectflag = 'E'
UNION ALL
SELECT
crd.assetcode,
'02' rdm_repo_status,
NULL rdm_repo_type,
cr.backdate rdm_repo_date,
cr.dealdate rdm_repo_deal_date,
IFNULL(crd.receivedpiamount, 0) rdm_repo_prin_bal,
IFNULL(crd.receivedpiamount, 0) rdm_repo_price,
NULL rdm_reason
FROM
Asset_repo cr,
Asset_repoDetail_fb2107202348 crd
WHERE
cr.tradeid = crd.tradeid
AND cr.productcode = 'XMDM202107202348'
AND cr.backdate <= to_date (
'2021-11-25 0:00:00',
'yyyy-MM-dd hh24:mi:ss'
)
AND cr.effectflag = 'E'
) trr ON s.assetcode = trr.assetcode
WHERE
s.projectcode = 'XMDM202107202348'
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
