SQL如下
explain analyze
SELECT
*
FROM
(
SELECT
( CASE WHEN E.c_busin_flag = '137' THEN E.vc_trade_acco ELSE A1.VC_TRADE_ACCO END ) AS tradeAcco,
A1.vc_fund_acco AS productAcco,
( CASE WHEN E.c_busin_flag = '137' THEN E.vc_product_code ELSE A1.vc_product_code END ) AS productCode,
A1.c_busin_flag AS businFlag,
A1.vc_request_no AS requestNo,
A1.vc_request_date AS requestDate,
E.vc_net_no AS netNo,
A1.vc_ta_code AS taCode,
E.c_share_type AS shareType,
E.vc_confirm_date AS confirmDate,
E.c_money_type AS moneyType,
E.en_confirm_share AS confirmShare,
E.en_confirm_bala AS confirmBala,
E.en_nav AS enNav,
E.en_fare AS fare,
E.en_agency_fare AS agencyFare,
E.en_stamp_tax AS stampTax,
( CASE WHEN E.c_busin_flag = '137' THEN E.vc_other_product_code ELSE A1.vc_other_product_code END ) AS otherProductCode,
( CASE WHEN E.c_busin_flag = '137' THEN E.c_other_share_type ELSE A1.c_other_share_type END ) AS otherShareType,
A1.vc_other_acco AS otherAcco,
A1.vc_other_agency_no AS otherAgencyNo,
E.vc_other_net_no AS otherNetNo,
E.vc_error_code AS errorCode,
E.vc_error_cause AS errorCause,
A1.c_frozen_cause AS frozenCause,
E.vc_origin_no AS originNo,
E.en_income AS income,
E.en_invest_income AS investIncome,
A2.en_invest_share AS investShare,
A2.vc_origin_date AS originDate,
E.en_after_fare AS afterFare,
E.en_origin_fare AS originFare,
A1.c_confirm_flag AS confirmFlag,
E.en_deal_fare AS dealFare,
E.c_confirm_status AS confirmStatus,
E.en_ta_fare AS taFare,
A1.c_capital_mode AS capitalMode,
A1.c_fix_busin_flag AS fixBusinFlag,
A1.c_trade_busin_type AS tradeBusinType,
A2.c_exp_type AS expType,
A2.l_section_schema_no AS sectionSchemaNo,
A1.en_balance AS balance,
A1.en_share AS enshare,
A2.vc_sub_acco_auditno AS subAccoAuditn,
A2.vc_comb_request_no AS combRequestNo,
A2.vc_comb_code AS combCode,
E.c_bank_no AS bankNo,
C.vc_bank_acco AS bankAcco,
A1.c_sub_capital_mode AS subCapitalMode,
A2.vc_trade_contact AS tradeContact,
A2.vc_broker AS broker,
A2.c_redeem_use_flag AS redeemUseFlag,
E.en_interest AS interest,
E.c_finish_flag AS finishFlag,
substr( A1.vc_accept_date, 1, 8 ) acceptDate,
substr( A1.vc_accept_time, 9, 6 ) acceptTime,
A1.vc_sub_acco_no AS subAccoNo,
A1.vc_come_from AS comeFrom,
A1.vc_cust_no AS custNo,
A1.c_dividend_method AS dividendMethod,
A1.C_CUST_CONFIRM AS custcomFirm,
A1.VC_BATCH_REQUEST_NO AS batchRequestNo,
A1.VC_BATCH_NO AS batchNo,
A1.VC_OTHER_SERIAL_NO AS otherSerialNo,
A1.VC_CONFIRM_SETTLE_DATE AS comfirmSettleDate,
D1.C_PRODUCT_CATEGORY AS productCategory,
A1.c_trust AS trustFlag,
A2.VC_VOUCHER_NO AS voucher_no,
A2.vc_protocol AS protocol,
( CASE WHEN A1.c_Busin_Flag IN ( '024', '025', '036' ) THEN '1' ELSE'0' END ) AS redeemReqFlag,
( CASE WHEN A1.c_Busin_Flag IN ( '022', '023', '039' ) THEN '1' ELSE'0' END ) AS allotReqFlag,
F.vc_need_date AS needDate,
D1.C_PRODUCT_SUB_TYPE AS productSubType,
E.VC_CONFIRM_NO AS confirmNo,
E.C_BUSIN_FLAG AS confirmBusinFlag,
B.C_USER_TYPE AS userType,
CFG.VC_MAIN_PRODUCT_CODE AS mainProductCode,
CFG.C_SIDE_BAG_FLAG AS sideBagFlag,
CFG.VC_SIDE_BAG_START_DATE AS sideBagStartDate,
cust.VC_IDENTITY_NO AS identityNo,
cust.C_IDENTITY_TYPE AS identityType,
cde.C_CUST_KIND AS custKind,
E.vc_bank_name AS bankName,
E.en_discount AS discount,
A1.vc_hope_date AS hopeDate,
a2.VC_REMIT_DEAD_DATE AS remitDeadDate,
a2.VC_REMIT_DEAD_TIME AS remitDeadTime,
A2.C_CUST_TYPE AS custType,
CUST.vc_custom_name AS customName,
D1.C_PRODUCT_TYPE AS productType,
D1.VC_PRODUCT_NAME AS productName,
A1.c_trust AS trust,
A2.C_PROMOTION AS promotion,
cde.VC_PHONE AS phone,
cde.VC_ADDRESS AS address,
cde.VC_ZIP AS zip,
cde.VC_MOBILE_NO AS mobileNo,
cde.VC_FAX_NO AS faxNo,
cde.VC_EMAIL AS email,
cap.C_PUB_PROFESSION_FLAG AS pubProfessionFlag,
cap.C_PUB_PROFESSION_SUB_TYPE AS pubProfessionSubType,
cap.C_PUB_HG_FLAG AS pubHgFlag,
cap.VC_PUB_PROFESSION_VALID_DATE AS pubProfessionValidDate,
D2.C_PRODUCT_RISK_LEVEL AS productRiskLevel,
tk.c_cust_risk_type AS custRiskType,
e.EN_OTHER_FEE1 AS otherFee1,
e.EN_OTHER_FEE2 AS otherFee2,
ta.vc_ta_name AS taName,
a1.vc_other_trade_acco AS otherTradeAcco
FROM
(
SELECT C
.vc_tenant_id AS VC_TENANT_ID,
C.vc_request_no AS VC_REQUEST_NO,
C.vc_fund_acco AS VC_FUND_ACCO,
C.vc_trade_acco AS VC_TRADE_ACCO,
C.vc_request_date AS VC_REQUEST_DATE,
C.vc_request_time AS VC_REQUEST_TIME,
C.c_busin_flag AS C_BUSIN_FLAG,
C.vc_product_code AS VC_PRODUCT_CODE,
C.c_share_type AS C_SHARE_TYPE,
C.c_trust AS C_TRUST,
C.en_balance AS EN_BALANCE,
C.en_share AS EN_SHARE,
C.en_discount AS EN_DISCOUNT,
C.en_end_discount AS EN_END_DISCOUNT,
C.en_other_discount AS EN_OTHER_DISCOUNT,
C.c_send_state AS C_SEND_STATE,
C.c_trade_state AS C_TRADE_STATE,
C.c_confirm_flag AS C_CONFIRM_FLAG,
C.en_confirm_share AS EN_CONFIRM_SHARE,
C.en_confirm_bala AS EN_CONFIRM_BALA,
C.vc_hope_date AS VC_HOPE_DATE,
C.vc_other_acco AS VC_OTHER_ACCO,
C.vc_other_trade_acco AS VC_OTHER_TRADE_ACCO,
C.vc_other_product_code AS VC_OTHER_PRODUCT_CODE,
C.c_other_share_type AS C_OTHER_SHARE_TYPE,
C.vc_other_agency_no AS VC_OTHER_AGENCY_NO,
C.c_dividend_method AS C_DIVIDEND_METHOD,
C.c_frozen_flag AS C_FROZEN_FLAG,
C.c_frozen_cause AS C_FROZEN_CAUSE,
C.c_capital_mode AS C_CAPITAL_MODE,
C.c_sub_capital_mode AS C_SUB_CAPITAL_MODE,
C.c_interface_type AS C_INTERFACE_TYPE,
C.l_delay_day AS L_DELAY_DAY,
C.c_audit_flag AS C_AUDIT_FLAG,
C.vc_ta_code AS VC_TA_CODE,
C.vc_accept_date AS VC_ACCEPT_DATE,
C.vc_accept_time AS VC_ACCEPT_TIME,
C.c_check_flag AS C_CHECK_FLAG,
C.c_cust_confirm AS C_CUST_CONFIRM,
C.c_fix_busin_flag AS C_FIX_BUSIN_FLAG,
C.vc_sys_date AS VC_SYS_DATE,
C.vc_machine_date AS VC_MACHINE_DATE,
C.vc_machine_time AS VC_MACHINE_TIME,
C.vc_batch_no AS VC_BATCH_NO,
C.c_trade_busin_type AS C_TRADE_BUSIN_TYPE,
C.vc_come_from AS VC_COME_FROM,
C.vc_china_pay_serial_no AS VC_CHINA_PAY_SERIAL_NO,
C.c_auto_request AS C_AUTO_REQUEST,
C.vc_sub_acco_no AS VC_SUB_ACCO_NO,
C.vc_cust_no AS VC_CUST_NO,
C.vc_batch_request_no AS VC_BATCH_REQUEST_NO,
C.vc_confirm_settle_date AS VC_CONFIRM_SETTLE_DATE,
C.vc_request_settle_date AS VC_REQUEST_SETTLE_DATE,
C.vc_cap_acco AS VC_CAP_ACCO,
C.vc_function_no AS VC_FUNCTION_NO,
C.l_node AS L_NODE,
C.c_export_state AS C_EXPORT_STATE,
C.c_delete AS C_DELETE,
C.vc_timestamp AS VC_TIMESTAMP,
C.vc_other_serial_no AS VC_OTHER_SERIAL_NO,
C.c_send_liq_state AS C_SEND_LIQ_STATE,
C.c_record_state AS C_RECORD_STATE
FROM
tc_threquest C UNION ALL
SELECT A
.vc_tenant_id AS VC_TENANT_ID,
A.vc_request_no AS VC_REQUEST_NO,
A.vc_fund_acco AS VC_FUND_ACCO,
A.vc_trade_acco AS VC_TRADE_ACCO,
A.vc_request_date AS VC_REQUEST_DATE,
A.vc_request_time AS VC_REQUEST_TIME,
A.c_busin_flag AS C_BUSIN_FLAG,
A.vc_product_code AS VC_PRODUCT_CODE,
A.c_share_type AS C_SHARE_TYPE,
A.c_trust AS C_TRUST,
A.en_balance AS EN_BALANCE,
A.en_share AS EN_SHARE,
A.en_discount AS EN_DISCOUNT,
A.en_end_discount AS EN_END_DISCOUNT,
A.en_other_discount AS EN_OTHER_DISCOUNT,
A.c_send_state AS C_SEND_STATE,
A.c_trade_state AS C_TRADE_STATE,
CASE
WHEN b.c_confirm_flag IS NULL THEN
A.c_confirm_flag ELSE b.c_confirm_flag
END AS C_CONFIRM_FLAG,
CASE
WHEN b.en_confirm_share IS NULL THEN
A.en_confirm_share ELSE b.en_confirm_share
END AS EN_CONFIRM_SHARE,
CASE
WHEN b.en_confirm_bala IS NULL THEN
A.en_confirm_bala ELSE b.en_confirm_bala
END AS EN_CONFIRM_BALA,
A.vc_hope_date AS VC_HOPE_DATE,
A.vc_other_acco AS VC_OTHER_ACCO,
A.vc_other_trade_acco AS VC_OTHER_TRADE_ACCO,
A.vc_other_product_code AS VC_OTHER_PRODUCT_CODE,
A.c_other_share_type AS C_OTHER_SHARE_TYPE,
A.vc_other_agency_no AS VC_OTHER_AGENCY_NO,
A.c_dividend_method AS C_DIVIDEND_METHOD,
A.c_frozen_flag AS C_FROZEN_FLAG,
A.c_frozen_cause AS C_FROZEN_CAUSE,
A.c_capital_mode AS C_CAPITAL_MODE,
A.c_sub_capital_mode AS C_SUB_CAPITAL_MODE,
A.c_interface_type AS C_INTERFACE_TYPE,
A.l_delay_day AS L_DELAY_DAY,
A.c_audit_flag AS C_AUDIT_FLAG,
A.vc_ta_code AS VC_TA_CODE,
A.vc_accept_date AS VC_ACCEPT_DATE,
A.vc_accept_time AS VC_ACCEPT_TIME,
A.c_check_flag AS C_CHECK_FLAG,
A.c_cust_confirm AS C_CUST_CONFIRM,
A.c_fix_busin_flag AS C_FIX_BUSIN_FLAG,
A.vc_sys_date AS VC_SYS_DATE,
A.vc_machine_date AS VC_MACHINE_DATE,
A.vc_machine_time AS VC_MACHINE_TIME,
A.vc_batch_no AS VC_BATCH_NO,
A.c_trade_busin_type AS C_TRADE_BUSIN_TYPE,
A.vc_come_from AS VC_COME_FROM,
A.vc_china_pay_serial_no AS VC_CHINA_PAY_SERIAL_NO,
A.c_auto_request AS C_AUTO_REQUEST,
A.vc_sub_acco_no AS VC_SUB_ACCO_NO,
A.vc_cust_no AS VC_CUST_NO,
A.vc_batch_request_no AS VC_BATCH_REQUEST_NO,
A.vc_confirm_settle_date AS VC_CONFIRM_SETTLE_DATE,
A.vc_request_settle_date AS VC_REQUEST_SETTLE_DATE,
A.vc_cap_acco AS VC_CAP_ACCO,
A.vc_function_no AS VC_FUNCTION_NO,
A.l_node AS L_NODE,
A.c_export_state AS C_EXPORT_STATE,
A.c_delete AS C_DELETE,
A.vc_timestamp AS VC_TIMESTAMP,
A.vc_other_serial_no AS VC_OTHER_SERIAL_NO,
A.c_send_liq_state AS C_SEND_LIQ_STATE,
A.c_record_state AS C_RECORD_STATE
FROM
tc_trequest
A LEFT JOIN lc_trequest_liq b ON A.vc_tenant_id = b.vc_tenant_id
AND A.vc_request_no = b.vc_request_no
) A1
LEFT JOIN tc_tfundcurrent F ON A1.vc_tenant_id = F.vc_tenant_id
AND A1.vc_request_no = F.vc_request_no
AND F.c_cap_busin_type = '850'
AND F.c_fix_busin_flag IN ( '04', '06', '08', '12', '13', '22' )
LEFT JOIN TC_TCUSTAPPROPRIATENESSINFO cap ON a1.VC_CUST_NO = cap.VC_CUST_NO
AND a1.VC_TENANT_ID = cap.VC_TENANT_ID
LEFT JOIN tc_tcustriskinfo tk ON a1.vc_tenant_id = tk.vc_tenant_id
AND a1.vc_cust_no = tk.vc_cust_no
AND ( CASE WHEN cap.C_PUB_PROFESSION_FLAG = '1' THEN '3' ELSE'0' END ) = tk.c_question_kind,
(
SELECT A
.vc_tenant_id AS VC_TENANT_ID,
A.vc_request_no AS VC_REQUEST_NO,
A.vc_cust_no AS VC_CUST_NO,
A.c_cust_type AS C_CUST_TYPE,
A.vc_voucher_no AS VC_VOUCHER_NO,
A.vc_net_no AS VC_NET_NO,
A.vc_center_no AS VC_CENTER_NO,
A.c_trade_flag AS C_TRADE_FLAG,
A.c_re_allot_flag AS C_RE_ALLOT_FLAG,
A.c_trust AS C_TRUST,
A.c_money_type AS C_MONEY_TYPE,
A.vc_operator_no AS VC_OPERATOR_NO,
A.vc_check_no AS VC_CHECK_NO,
A.vc_broker AS VC_BROKER,
A.c_exceed_flag AS C_EXCEED_FLAG,
A.c_origin_flag AS C_ORIGIN_FLAG,
A.vc_origin_no AS VC_ORIGIN_NO,
A.vc_origin_date AS VC_ORIGIN_DATE,
A.vc_origin_confirm_no AS VC_ORIGIN_CONFIRM_NO,
A.vc_origin_confirm_date AS VC_ORIGIN_CONFIRM_DATE,
A.c_bank_no AS C_BANK_NO,
A.vc_bank_name AS VC_BANK_NAME,
A.vc_name_in_bank AS VC_NAME_IN_BANK,
A.vc_bank_acco AS VC_BANK_ACCO,
A.vc_change_type AS VC_CHANGE_TYPE,
A.vc_other_net_no AS VC_OTHER_NET_NO,
A.vc_explain AS VC_EXPLAIN,
A.vc_protocol AS VC_PROTOCOL,
A.l_permit_num AS L_PERMIT_NUM,
A.vc_end_date AS VC_END_DATE,
A.vc_first_trade_date AS VC_FIRST_TRADE_DATE,
A.vc_fix_day AS VC_FIX_DAY,
A.vc_book_no AS VC_BOOK_NO,
A.vc_cycle AS VC_CYCLE,
A.vc_reg_date AS VC_REG_DATE,
A.vc_city_no AS VC_CITY_NO,
A.vc_seller AS VC_SELLER,
A.vc_comb_code AS VC_COMB_CODE,
A.vc_comb_request_no AS VC_COMB_REQUEST_NO,
A.en_invest_scale AS EN_INVEST_SCALE,
A.vc_audit_no AS VC_AUDIT_NO,
A.en_invest_share AS EN_INVEST_SHARE,
A.c_promotion AS C_PROMOTION,
A.vc_exponent_code AS VC_EXPONENT_CODE,
A.c_exponent_flag AS C_EXPONENT_FLAG,
A.en_exponent_value AS EN_EXPONENT_VALUE,
A.en_fact_bala AS EN_FACT_BALA,
A.c_part_flag AS C_PART_FLAG,
A.en_due_fare AS EN_DUE_FARE,
A.c_cp_flag AS C_CP_FLAG,
A.c_invest_flag AS C_INVEST_FLAG,
A.en_transfer_fee AS EN_TRANSFER_FEE,
A.vc_bank_id AS VC_BANK_ID,
A.vc_cp_error_code AS VC_CP_ERROR_CODE,
A.vc_invest_content AS VC_INVEST_CONTENT,
A.vc_transfer_date AS VC_TRANSFER_DATE,
A.c_discount_flag AS C_DISCOUNT_FLAG,
A.vc_confirm_date AS VC_CONFIRM_DATE,
A.vc_bank_net_date AS VC_BANK_NET_DATE,
A.vc_trade_contact AS VC_TRADE_CONTACT,
A.vc_securities_account AS VC_SECURITIES_ACCOUNT,
A.c_risk_flag AS C_RISK_FLAG,
A.vc_protocol_name AS VC_PROTOCOL_NAME,
A.c_cycle_unit AS C_CYCLE_UNIT,
A.c_exp_type AS C_EXP_TYPE,
A.l_max_success_num AS L_MAX_SUCCESS_NUM,
A.en_max_value AS EN_MAX_VALUE,
A.en_min_scale1 AS EN_MIN_SCALE1,
A.en_max_scale1 AS EN_MAX_SCALE1,
A.en_scal_balance1 AS EN_SCAL_BALANCE1,
A.en_min_scale2 AS EN_MIN_SCALE2,
A.en_max_scale2 AS EN_MAX_SCALE2,
A.en_scal_balance2 AS EN_SCAL_BALANCE2,
A.en_min_scale3 AS EN_MIN_SCALE3,
A.en_max_scale3 AS EN_MAX_SCALE3,
A.en_scal_balance3 AS EN_SCAL_BALANCE3,
A.c_cust_risk_type AS C_CUST_RISK_TYPE,
A.c_product_risk_level AS C_PRODUCT_RISK_LEVEL,
A.c_aml_full_flag AS C_AML_FULL_FLAG,
A.c_allow_deduct AS C_ALLOW_DEDUCT,
A.c_avg_exponent_type AS C_AVG_EXPONENT_TYPE,
A.en_diff_scale AS EN_DIFF_SCALE,
A.vc_trade_purpose AS VC_TRADE_PURPOSE,
A.en_upper_limit AS EN_UPPER_LIMIT,
A.en_lower_limit AS EN_LOWER_LIMIT,
A.vc_link_deduct_no AS VC_LINK_DEDUCT_NO,
A.vc_recover_date AS VC_RECOVER_DATE,
A.vc_last_deduct_time AS VC_LAST_DEDUCT_TIME,
A.vc_comm_error_no AS VC_COMM_ERROR_NO,
A.vc_comm_error_msg AS VC_COMM_ERROR_MSG,
A.vc_cp_error_msg AS VC_CP_ERROR_MSG,
A.l_section_schema_no AS L_SECTION_SCHEMA_NO,
A.vc_section_schema_type AS VC_SECTION_SCHEMA_TYPE,
A.c_scan_flag AS C_SCAN_FLAG,
A.vc_fix_month AS VC_FIX_MONTH,
A.en_invenst_rate AS EN_INVENST_RATE,
A.vc_recommender AS VC_RECOMMENDER,
A.vc_recommender_phone AS VC_RECOMMENDER_PHONE,
A.vc_recommender_province AS VC_RECOMMENDER_PROVINCE,
A.vc_recommender_city_no AS VC_RECOMMENDER_CITY_NO,
A.vc_source_ip_addr AS VC_SOURCE_IP_ADDR,
A.vc_deduct_product_code AS VC_DEDUCT_PRODUCT_CODE,
A.vc_sub_acco_auditno AS VC_SUB_ACCO_AUDITNO,
A.l_special_liq_day AS L_SPECIAL_LIQ_DAY,
A.en_max_value_once AS EN_MAX_VALUE_ONCE,
A.en_min_retained_bala AS EN_MIN_RETAINED_BALA,
A.en_lower_deduct_bala AS EN_LOWER_DEDUCT_BALA,
A.c_fix_comb_type AS C_FIX_COMB_TYPE,
A.vc_hedge_product_code AS VC_HEDGE_PRODUCT_CODE,
A.c_hedge_share_type AS C_HEDGE_SHARE_TYPE,
A.c_short_avg_exponent_type AS C_SHORT_AVG_EXPONENT_TYPE,
A.c_medium_avg_exponent_type AS C_MEDIUM_AVG_EXPONENT_TYPE,
A.c_long_avg_exponent_type AS C_LONG_AVG_EXPONENT_TYPE,
A.vc_hedge_protocol AS VC_HEDGE_PROTOCOL,
A.c_break_flag AS C_BREAK_FLAG,
A.vc_self_comb_code AS VC_SELF_COMB_CODE,
A.vc_match_no AS VC_MATCH_NO,
A.vc_match_audit_no AS VC_MATCH_AUDIT_NO,
A.vc_contract_no AS VC_CONTRACT_NO,
A.c_redeem_use_flag AS C_REDEEM_USE_FLAG,
A.c_recreate_batch_no AS C_RECREATE_BATCH_NO,
A.vc_contract_serial_no AS VC_CONTRACT_SERIAL_NO,
A.c_profit_class AS C_PROFIT_CLASS,
A.vc_reject_reason AS VC_REJECT_REASON,
A.vc_ext_comb_code AS VC_EXT_COMB_CODE,
A.c_non_work_day_delay AS C_NON_WORK_DAY_DELAY,
A.vc_payment_acco_no AS VC_PAYMENT_ACCO_NO,
A.vc_payment_date AS VC_PAYMENT_DATE,
A.vc_payment_type AS VC_PAYMENT_TYPE,
A.vc_cd_card AS VC_CD_CARD,
A.c_payment_bala_type AS C_PAYMENT_BALA_TYPE,
A.en_income AS EN_INCOME,
A.c_trade_deal_flag AS C_TRADE_DEAL_FLAG,
A.c_loan_flag AS C_LOAN_FLAG,
A.c_syn_state AS C_SYN_STATE,
A.c_need_deduct AS C_NEED_DEDUCT,
A.c_need_transfer AS C_NEED_TRANSFER,
A.c_change_direct AS C_CHANGE_DIRECT,
A.c_hk_capital_mode AS C_HK_CAPITAL_MODE,
A.vc_bill_serial_no AS VC_BILL_SERIAL_NO,
A.vc_use_content AS VC_USE_CONTENT,
A.c_ft_redeem_mode AS C_FT_REDEEM_MODE,
A.vc_bank_net_time AS VC_BANK_NET_TIME,
A.vc_book_id AS VC_BOOK_ID,
A.vc_unfrozen_date AS VC_UNFROZEN_DATE,
A.vc_invest_date AS VC_INVEST_DATE,
A.vc_need_date AS VC_NEED_DATE,
A.vc_bank_province_code AS VC_BANK_PROVINCE_CODE,
A.vc_bank_city_no AS VC_BANK_CITY_NO,
A.vc_acco_id AS VC_ACCO_ID,
A.vc_confirm_benefit_serial_no AS VC_CONFIRM_BENEFIT_SERIAL_NO,
A.c_ft_rdm_trans_mode AS C_FT_RDM_TRANS_MODE,
A.c_capin_flag AS C_CAPIN_FLAG,
A.c_need_frozen_share AS C_NEED_FROZEN_SHARE,
A.c_identity_type_in_bank AS C_IDENTITY_TYPE_IN_BANK,
A.vc_identity_no_in_bank AS VC_IDENTITY_NO_IN_BANK,
A.vc_liquidate_date AS VC_LIQUIDATE_DATE,
A.en_coupon AS EN_COUPON,
A.en_nav AS EN_NAV,
A.vc_fuse_oridate AS VC_FUSE_ORIDATE,
A.vc_share_expire_date AS VC_SHARE_EXPIRE_DATE,
A.vc_out_requestno AS VC_OUT_REQUESTNO,
A.en_gram_num AS EN_GRAM_NUM,
A.vc_resub_date AS VC_RESUB_DATE,
A.vc_calm_time AS VC_CALM_TIME,
A.vc_register_serialno AS VC_REGISTER_SERIALNO,
A.vc_ori_operator_no AS VC_ORI_OPERATOR_NO,
A.c_cust_fund_time_limit_match AS C_CUST_FUND_TIME_LIMIT_MATCH,
A.c_invest_varieties_match_flag AS C_INVEST_VARIETIES_MATCH_FLAG,
A.c_cust_investment_varieties AS C_CUST_INVESTMENT_VARIETIES,
A.c_fund_investment_varieties AS C_FUND_INVESTMENT_VARIETIES,
A.en_cust_time_limit AS EN_CUST_TIME_LIMIT,
A.en_fund_time_limit AS EN_FUND_TIME_LIMIT,
A.vc_cool_start_time AS VC_COOL_START_TIME,
A.c_return_flag AS C_RETURN_FLAG,
A.l_node AS L_NODE,
A.c_deal_flag AS C_DEAL_FLAG,
A.c_stop_profit AS C_STOP_PROFIT,
A.vc_timestamp AS VC_TIMESTAMP,
A.vc_investor_system_id AS VC_INVESTOR_SYSTEM_ID,
A.vc_ip_address AS VC_IP_ADDRESS,
A.vc_mac_address AS VC_MAC_ADDRESS,
A.vc_imei AS VC_IMEI,
A.vc_uuid AS VC_UUID,
A.c_voucher_flag AS C_VOUCHER_FLAG,
A.vc_hedge_trade_acco AS VC_HEDGE_TRADE_ACCO,
A.vc_hedge_ta_code AS VC_HEDGE_TA_CODE,
A.vc_freeze_share_request_no AS VC_FREEZE_SHARE_REQUEST_NO,
A.vc_fix_busin_flag_list AS VC_FIX_BUSIN_FLAG_LIST,
A.vc_pay_grant_org AS VC_PAY_GRANT_ORG,
A.vc_strategy_type AS VC_STRATEGY_TYPE,
A.c_ia_notify_flag AS C_IA_NOTIFY_FLAG,
A.c_sum_deduct_flag AS C_SUM_DEDUCT_FLAG,
A.vc_specify_request_date AS VC_SPECIFY_REQUEST_DATE,
A.vc_ins_busin_flag AS VC_INS_BUSIN_FLAG,
A.vc_json_content AS VC_JSON_CONTENT,
A.c_share_expire_redeem_mode AS C_SHARE_EXPIRE_REDEEM_MODE,
A.vc_plan_no AS VC_PLAN_NO,
A.VC_REMIT_DEAD_DATE AS VC_REMIT_DEAD_DATE,
A.VC_REMIT_DEAD_TIME AS VC_REMIT_DEAD_TIME
FROM
tc_trequestdetail A UNION ALL
SELECT
b.vc_tenant_id AS VC_TENANT_ID,
b.vc_request_no AS VC_REQUEST_NO,
b.vc_cust_no AS VC_CUST_NO,
b.c_cust_type AS C_CUST_TYPE,
b.vc_voucher_no AS VC_VOUCHER_NO,
b.vc_net_no AS VC_NET_NO,
b.vc_center_no AS VC_CENTER_NO,
b.c_trade_flag AS C_TRADE_FLAG,
b.c_re_allot_flag AS C_RE_ALLOT_FLAG,
b.c_trust AS C_TRUST,
b.c_money_type AS C_MONEY_TYPE,
b.vc_operator_no AS VC_OPERATOR_NO,
b.vc_check_no AS VC_CHECK_NO,
b.vc_broker AS VC_BROKER,
b.c_exceed_flag AS C_EXCEED_FLAG,
b.c_origin_flag AS C_ORIGIN_FLAG,
b.vc_origin_no AS VC_ORIGIN_NO,
b.vc_origin_date AS VC_ORIGIN_DATE,
b.vc_origin_confirm_no AS VC_ORIGIN_CONFIRM_NO,
b.vc_origin_confirm_date AS VC_ORIGIN_CONFIRM_DATE,
b.c_bank_no AS C_BANK_NO,
b.vc_bank_name AS VC_BANK_NAME,
b.vc_name_in_bank AS VC_NAME_IN_BANK,
b.vc_bank_acco AS VC_BANK_ACCO,
b.vc_change_type AS VC_CHANGE_TYPE,
b.vc_other_net_no AS VC_OTHER_NET_NO,
b.vc_explain AS VC_EXPLAIN,
b.vc_protocol AS VC_PROTOCOL,
b.l_permit_num AS L_PERMIT_NUM,
b.vc_end_date AS VC_END_DATE,
b.vc_first_trade_date AS VC_FIRST_TRADE_DATE,
b.vc_fix_day AS VC_FIX_DAY,
b.vc_book_no AS VC_BOOK_NO,
b.vc_cycle AS VC_CYCLE,
b.vc_reg_date AS VC_REG_DATE,
b.vc_city_no AS VC_CITY_NO,
b.vc_seller AS VC_SELLER,
b.vc_comb_code AS VC_COMB_CODE,
b.vc_comb_request_no AS VC_COMB_REQUEST_NO,
b.en_invest_scale AS EN_INVEST_SCALE,
b.vc_audit_no AS VC_AUDIT_NO,
b.en_invest_share AS EN_INVEST_SHARE,
b.c_promotion AS C_PROMOTION,
b.vc_exponent_code AS VC_EXPONENT_CODE,
b.c_exponent_flag AS C_EXPONENT_FLAG,
b.en_exponent_value AS EN_EXPONENT_VALUE,
b.en_fact_bala AS EN_FACT_BALA,
b.c_part_flag AS C_PART_FLAG,
b.en_due_fare AS EN_DUE_FARE,
b.c_cp_flag AS C_CP_FLAG,
b.c_invest_flag AS C_INVEST_FLAG,
b.en_transfer_fee AS EN_TRANSFER_FEE,
b.vc_bank_id AS VC_BANK_ID,
b.vc_cp_error_code AS VC_CP_ERROR_CODE,
b.vc_invest_content AS VC_INVEST_CONTENT,
b.vc_transfer_date AS VC_TRANSFER_DATE,
b.c_discount_flag AS C_DISCOUNT_FLAG,
b.vc_confirm_date AS VC_CONFIRM_DATE,
b.vc_bank_net_date AS VC_BANK_NET_DATE,
b.vc_trade_contact AS VC_TRADE_CONTACT,
b.vc_securities_account AS VC_SECURITIES_ACCOUNT,
b.c_risk_flag AS C_RISK_FLAG,
b.vc_protocol_name AS VC_PROTOCOL_NAME,
b.c_cycle_unit AS C_CYCLE_UNIT,
b.c_exp_type AS C_EXP_TYPE,
b.l_max_success_num AS L_MAX_SUCCESS_NUM,
b.en_max_value AS EN_MAX_VALUE,
b.en_min_scale1 AS EN_MIN_SCALE1,
b.en_max_scale1 AS EN_MAX_SCALE1,
b.en_scal_balance1 AS EN_SCAL_BALANCE1,
b.en_min_scale2 AS EN_MIN_SCALE2,
b.en_max_scale2 AS EN_MAX_SCALE2,
b.en_scal_balance2 AS EN_SCAL_BALANCE2,
b.en_min_scale3 AS EN_MIN_SCALE3,
b.en_max_scale3 AS EN_MAX_SCALE3,
b.en_scal_balance3 AS EN_SCAL_BALANCE3,
b.c_cust_risk_type AS C_CUST_RISK_TYPE,
b.c_product_risk_level AS C_PRODUCT_RISK_LEVEL,
b.c_aml_full_flag AS C_AML_FULL_FLAG,
b.c_allow_deduct AS C_ALLOW_DEDUCT,
b.c_avg_exponent_type AS C_AVG_EXPONENT_TYPE,
b.en_diff_scale AS EN_DIFF_SCALE,
b.vc_trade_purpose AS VC_TRADE_PURPOSE,
b.en_upper_limit AS EN_UPPER_LIMIT,
b.en_lower_limit AS EN_LOWER_LIMIT,
b.vc_link_deduct_no AS VC_LINK_DEDUCT_NO,
b.vc_recover_date AS VC_RECOVER_DATE,
b.vc_last_deduct_time AS VC_LAST_DEDUCT_TIME,
b.vc_comm_error_no AS VC_COMM_ERROR_NO,
b.vc_comm_error_msg AS VC_COMM_ERROR_MSG,
b.vc_cp_error_msg AS VC_CP_ERROR_MSG,
b.l_section_schema_no AS L_SECTION_SCHEMA_NO,
b.vc_section_schema_type AS VC_SECTION_SCHEMA_TYPE,
b.c_scan_flag AS C_SCAN_FLAG,
b.vc_fix_month AS VC_FIX_MONTH,
b.en_invenst_rate AS EN_INVENST_RATE,
b.vc_recommender AS VC_RECOMMENDER,
b.vc_recommender_phone AS VC_RECOMMENDER_PHONE,
b.vc_recommender_province AS VC_RECOMMENDER_PROVINCE,
b.vc_recommender_city_no AS VC_RECOMMENDER_CITY_NO,
b.vc_source_ip_addr AS VC_SOURCE_IP_ADDR,
b.vc_deduct_product_code AS VC_DEDUCT_PRODUCT_CODE,
b.vc_sub_acco_auditno AS VC_SUB_ACCO_AUDITNO,
b.l_special_liq_day AS L_SPECIAL_LIQ_DAY,
b.en_max_value_once AS EN_MAX_VALUE_ONCE,
b.en_min_retained_bala AS EN_MIN_RETAINED_BALA,
b.en_lower_deduct_bala AS EN_LOWER_DEDUCT_BALA,
b.c_fix_comb_type AS C_FIX_COMB_TYPE,
b.vc_hedge_product_code AS VC_HEDGE_PRODUCT_CODE,
b.c_hedge_share_type AS C_HEDGE_SHARE_TYPE,
b.c_short_avg_exponent_type AS C_SHORT_AVG_EXPONENT_TYPE,
b.c_medium_avg_exponent_type AS C_MEDIUM_AVG_EXPONENT_TYPE,
b.c_long_avg_exponent_type AS C_LONG_AVG_EXPONENT_TYPE,
b.vc_hedge_protocol AS VC_HEDGE_PROTOCOL,
b.c_break_flag AS C_BREAK_FLAG,
b.vc_self_comb_code AS VC_SELF_COMB_CODE,
b.vc_match_no AS VC_MATCH_NO,
b.vc_match_audit_no AS VC_MATCH_AUDIT_NO,
b.vc_contract_no AS VC_CONTRACT_NO,
b.c_redeem_use_flag AS C_REDEEM_USE_FLAG,
b.c_recreate_batch_no AS C_RECREATE_BATCH_NO,
b.vc_contract_serial_no AS VC_CONTRACT_SERIAL_NO,
b.c_profit_class AS C_PROFIT_CLASS,
b.vc_reject_reason AS VC_REJECT_REASON,
b.vc_ext_comb_code AS VC_EXT_COMB_CODE,
b.c_non_work_day_delay AS C_NON_WORK_DAY_DELAY,
b.vc_payment_acco_no AS VC_PAYMENT_ACCO_NO,
b.vc_payment_date AS VC_PAYMENT_DATE,
b.vc_payment_type AS VC_PAYMENT_TYPE,
b.vc_cd_card AS VC_CD_CARD,
b.c_payment_bala_type AS C_PAYMENT_BALA_TYPE,
b.en_income AS EN_INCOME,
b.c_trade_deal_flag AS C_TRADE_DEAL_FLAG,
b.c_loan_flag AS C_LOAN_FLAG,
b.c_syn_state AS C_SYN_STATE,
b.c_need_deduct AS C_NEED_DEDUCT,
b.c_need_transfer AS C_NEED_TRANSFER,
b.c_change_direct AS C_CHANGE_DIRECT,
b.c_hk_capital_mode AS C_HK_CAPITAL_MODE,
b.vc_bill_serial_no AS VC_BILL_SERIAL_NO,
b.vc_use_content AS VC_USE_CONTENT,
b.c_ft_redeem_mode AS C_FT_REDEEM_MODE,
b.vc_bank_net_time AS VC_BANK_NET_TIME,
b.vc_book_id AS VC_BOOK_ID,
b.vc_unfrozen_date AS VC_UNFROZEN_DATE,
b.vc_invest_date AS VC_INVEST_DATE,
b.vc_need_date AS VC_NEED_DATE,
b.vc_bank_province_code AS VC_BANK_PROVINCE_CODE,
b.vc_bank_city_no AS VC_BANK_CITY_NO,
b.vc_acco_id AS VC_ACCO_ID,
b.vc_confirm_benefit_serial_no AS VC_CONFIRM_BENEFIT_SERIAL_NO,
b.c_ft_rdm_trans_mode AS C_FT_RDM_TRANS_MODE,
b.c_capin_flag AS C_CAPIN_FLAG,
b.c_need_frozen_share AS C_NEED_FROZEN_SHARE,
b.c_identity_type_in_bank AS C_IDENTITY_TYPE_IN_BANK,
b.vc_identity_no_in_bank AS VC_IDENTITY_NO_IN_BANK,
b.vc_liquidate_date AS VC_LIQUIDATE_DATE,
b.en_coupon AS EN_COUPON,
b.en_nav AS EN_NAV,
b.vc_fuse_oridate AS VC_FUSE_ORIDATE,
b.vc_share_expire_date AS VC_SHARE_EXPIRE_DATE,
b.vc_out_requestno AS VC_OUT_REQUESTNO,
b.en_gram_num AS EN_GRAM_NUM,
b.vc_resub_date AS VC_RESUB_DATE,
b.vc_calm_time AS VC_CALM_TIME,
b.vc_register_serialno AS VC_REGISTER_SERIALNO,
b.vc_ori_operator_no AS VC_ORI_OPERATOR_NO,
b.c_cust_fund_time_limit_match AS C_CUST_FUND_TIME_LIMIT_MATCH,
b.c_invest_varieties_match_flag AS C_INVEST_VARIETIES_MATCH_FLAG,
b.c_cust_investment_varieties AS C_CUST_INVESTMENT_VARIETIES,
b.c_fund_investment_varieties AS C_FUND_INVESTMENT_VARIETIES,
b.en_cust_time_limit AS EN_CUST_TIME_LIMIT,
b.en_fund_time_limit AS EN_FUND_TIME_LIMIT,
b.vc_cool_start_time AS VC_COOL_START_TIME,
b.c_return_flag AS C_RETURN_FLAG,
b.l_node AS L_NODE,
b.c_deal_flag AS C_DEAL_FLAG,
b.c_stop_profit AS C_STOP_PROFIT,
b.vc_timestamp AS VC_TIMESTAMP,
b.vc_investor_system_id AS VC_INVESTOR_SYSTEM_ID,
b.vc_ip_address AS VC_IP_ADDRESS,
b.vc_mac_address AS VC_MAC_ADDRESS,
b.vc_imei AS VC_IMEI,
b.vc_uuid AS VC_UUID,
b.c_voucher_flag AS C_VOUCHER_FLAG,
b.vc_hedge_trade_acco AS VC_HEDGE_TRADE_ACCO,
b.vc_hedge_ta_code AS VC_HEDGE_TA_CODE,
b.vc_freeze_share_request_no AS VC_FREEZE_SHARE_REQUEST_NO,
b.vc_fix_busin_flag_list AS VC_FIX_BUSIN_FLAG_LIST,
b.vc_pay_grant_org AS VC_PAY_GRANT_ORG,
b.vc_strategy_type AS VC_STRATEGY_TYPE,
b.c_ia_notify_flag AS C_IA_NOTIFY_FLAG,
b.c_sum_deduct_flag AS C_SUM_DEDUCT_FLAG,
b.vc_specify_request_date AS VC_SPECIFY_REQUEST_DATE,
b.vc_ins_busin_flag AS VC_INS_BUSIN_FLAG,
b.vc_json_content AS VC_JSON_CONTENT,
b.c_share_expire_redeem_mode AS C_SHARE_EXPIRE_REDEEM_MODE,
b.vc_plan_no AS VC_PLAN_NO,
b.VC_REMIT_DEAD_DATE AS VC_REMIT_DEAD_DATE,
b.VC_REMIT_DEAD_TIME AS VC_REMIT_DEAD_TIME
FROM
tc_threquestdetail b
) A2,
TC_TACCOINFO B,
TC_TACCOBANK C,
TC_TPRODUCTINFO D1,
TC_TPRODUCTINFODETAIL D2,
TC_TPRODUCTCFG CFG,
LC_TCONFIRM E,
TC_TCUSTINFO cust,
TC_TCUSTINFODETAIL cde,
TC_TTAINFO ta
WHERE
A1.VC_TENANT_ID = A2.VC_TENANT_ID
AND A1.VC_TENANT_ID = B.VC_TENANT_ID
AND A1.VC_TENANT_ID = C.VC_TENANT_ID
AND A1.VC_TENANT_ID = D1.VC_TENANT_ID
AND A1.VC_TENANT_ID = D2.VC_TENANT_ID
AND A1.VC_TENANT_ID = E.VC_TENANT_ID
AND A1.VC_TENANT_ID = cust.VC_TENANT_ID
AND A1.VC_TENANT_ID = cde.VC_TENANT_ID
AND A1.VC_TENANT_ID = ta.VC_TENANT_ID
AND d1.vc_ta_code = ta.vc_ta_code
AND A1.VC_TENANT_ID = CFG.VC_TENANT_ID
AND D1.VC_PRODUCT_CODE = D2.VC_PRODUCT_CODE
AND D1.VC_PRODUCT_CODE = E.VC_PRODUCT_CODE
AND A1.VC_REQUEST_NO = A2.VC_REQUEST_NO
AND A1.VC_REQUEST_NO = E.VC_REQUEST_NO
AND A1.VC_TRADE_ACCO = B.VC_TRADE_ACCO
AND B.VC_BANK_CARD_NO = C.VC_BANK_CARD_NO
AND A1.VC_PRODUCT_CODE = CFG.VC_PRODUCT_CODE
AND A1.VC_PRODUCT_CODE = D1.VC_PRODUCT_CODE
AND A1.VC_CUST_NO = cust.VC_CUST_NO
AND A1.VC_CUST_NO = cde.VC_CUST_NO
AND A1.VC_TENANT_ID = '10000'
AND A1.VC_CUST_NO = '004098'
AND A2.VC_CUST_NO = '004098'
AND cap.VC_CUST_NO = '004098' UNION ALL
SELECT
E.vc_trade_acco AS tradeAcco,
E.vc_fund_acco AS productAcco,
E.vc_product_code AS productCode,
E.c_busin_flag AS businFlag,
E.vc_request_no AS requestNo,
E.vc_request_date AS requestDate,
E.vc_net_no AS netNo,
E.vc_ta_code AS taCode,
E.c_share_type AS shareType,
E.vc_confirm_date AS confirmDate,
E.c_money_type AS moneyType,
E.en_confirm_share AS confirmShare,
E.en_confirm_bala AS confirmBala,
E.en_nav AS enNav,
E.en_fare AS fare,
E.en_agency_fare AS agencyFare,
E.en_stamp_tax AS stampTax,
E.vc_other_product_code AS otherProductCode,
E.c_other_share_type AS otherShareType,
E.vc_other_acco AS otherAcco,
E.vc_other_agency_no AS otherAgencyNo,
E.vc_other_net_no AS otherNetNo,
E.vc_error_code AS errorCode,
E.vc_error_cause AS errorCause,
E.c_frozen_cause AS frozenCause,
E.vc_origin_no AS originNo,
E.en_income AS income,
E.en_invest_income AS investIncome,
E.en_invest_share AS investShare,
'' AS originDate,
E.en_after_fare AS afterFare,
E.en_origin_fare AS originFare,
E.c_confirm_flag AS confirmFlag,
E.en_deal_fare AS dealFare,
E.c_confirm_status AS confirmStatus,
E.en_ta_fare AS taFare,
E.c_capital_mode AS capitalMode,
E.c_fix_busin_flag AS fixBusinFlag,
E.c_trade_busin_type AS tradeBusinType,
E.c_exp_type AS expType,
E.l_section_schema_no AS sectionSchemaNo,
E.en_balance AS balance,
E.en_share AS enshare,
'' AS subAccoAuditn,
E.vc_comb_request_no AS combRequestNo,
E.vc_comb_code AS combCode,
E.c_bank_no AS bankNo,
C.vc_bank_acco AS bankAcco,
E.c_sub_capital_mode AS subCapitalMode,
'' AS tradeContact,
E.vc_broker AS broker,
'' AS redeemUseFlag,
E.en_interest AS interest,
E.c_finish_flag AS finishFlag,
'' AS acceptDate,
'' AS acceptTime,
'' AS subAccoNo,
E.vc_come_from AS comeFrom,
E.vc_cust_no AS custNo,
E.c_dividend_method AS dividendMethod,
'' AS custcomFirm,
'' AS batchRequestNo,
'' AS batchNo,
'' AS otherSerialNo,
E.VC_CONFIRM_SETTLE_DATE AS comfirmSettleDate,
D1.C_PRODUCT_CATEGORY AS productCategory,
E.c_trust AS trustFlag,
E.VC_VOUCHER_NO AS voucher_no,
'' AS protocol,
'0' AS redeemReqFlag,
'0' AS allotReqFlag,
'' AS needDate,
D1.C_PRODUCT_SUB_TYPE AS productSubType,
E.VC_CONFIRM_NO AS confirmNo,
E.C_BUSIN_FLAG AS confirmBusinFlag,
B.C_USER_TYPE AS userType,
CFG.VC_MAIN_PRODUCT_CODE AS mainProductCode,
CFG.C_SIDE_BAG_FLAG AS sideBagFlag,
CFG.VC_SIDE_BAG_START_DATE AS sideBagStartDate,
cust.VC_IDENTITY_NO AS identityNo,
cust.C_IDENTITY_TYPE AS identityType,
cde.C_CUST_KIND AS custKind,
E.vc_bank_name AS bankName,
E.en_discount AS discount,
'' AS hopeDate,
'' AS remitDeadDate,
'' AS remitDeadTime,
cust.C_CUST_TYPE AS custType,
CUST.vc_custom_name AS customName,
D1.C_PRODUCT_TYPE AS productType,
D1.VC_PRODUCT_NAME AS productName,
NULL AS trust,
NULL AS promotion,
cde.VC_PHONE AS phone,
cde.VC_ADDRESS AS address,
cde.VC_ZIP AS zip,
cde.VC_MOBILE_NO AS mobileNo,
cde.VC_FAX_NO AS faxNo,
cde.VC_EMAIL AS email,
cap.C_PUB_PROFESSION_FLAG AS pubProfessionFlag,
cap.C_PUB_PROFESSION_SUB_TYPE AS pubProfessionSubType,
cap.C_PUB_HG_FLAG AS pubHgFlag,
cap.VC_PUB_PROFESSION_VALID_DATE AS pubProfessionValidDate,
D2.C_PRODUCT_RISK_LEVEL AS productRiskLevel,
tk.c_cust_risk_type AS custRiskType,
e.EN_OTHER_FEE1 AS otherFee1,
e.EN_OTHER_FEE2 AS otherFee2,
ta.vc_ta_name AS taName,
e.vc_other_trade_acco AS otherTradeAcco
FROM
LC_TCONFIRM E
LEFT JOIN TC_TCUSTAPPROPRIATENESSINFO cap ON e.VC_CUST_NO = cap.VC_CUST_NO
AND e.VC_TENANT_ID = cap.VC_TENANT_ID
LEFT JOIN tc_tcustriskinfo tk ON e.vc_tenant_id = tk.vc_tenant_id
AND e.vc_cust_no = tk.vc_cust_no
AND ( CASE WHEN cap.C_PUB_PROFESSION_FLAG = '1' THEN '3' ELSE'0' END ) = tk.c_question_kind,
TC_TACCOINFO B,
TC_TACCOBANK C,
TC_TPRODUCTINFO D1,
TC_TPRODUCTINFODETAIL D2,
TC_TPRODUCTCFG CFG,
TC_TCUSTINFO cust,
TC_TCUSTINFODETAIL cde,
TC_TTAINFO ta
WHERE
E.VC_TENANT_ID = B.VC_TENANT_ID
AND E.VC_TENANT_ID = C.VC_TENANT_ID
AND E.VC_TENANT_ID = D1.VC_TENANT_ID
AND E.VC_TENANT_ID = D2.VC_TENANT_ID
AND E.VC_TENANT_ID = CFG.VC_TENANT_ID
AND E.VC_TENANT_ID = cust.VC_TENANT_ID
AND E.VC_TENANT_ID = cde.VC_TENANT_ID
AND e.VC_TENANT_ID = ta.VC_TENANT_ID
AND D1.vc_ta_code = ta.vc_ta_code
AND E.VC_PRODUCT_CODE = CFG.VC_PRODUCT_CODE
AND D1.VC_PRODUCT_CODE = D2.VC_PRODUCT_CODE
AND E.VC_PRODUCT_CODE = D1.VC_PRODUCT_CODE
AND E.VC_CUST_NO = cust.VC_CUST_NO
AND E.VC_CUST_NO = cde.VC_CUST_NO
AND E.VC_TRADE_ACCO = B.VC_TRADE_ACCO
AND B.VC_BANK_CARD_NO = C.VC_BANK_CARD_NO
AND E.c_busin_flag = '142'
AND E.VC_TENANT_ID = '10000'
AND E.VC_CUST_NO = '004098'
AND cap.VC_CUST_NO = '004098'
AND B.VC_CUST_NO = '004098'
AND CUST.VC_CUST_NO = '004098'
AND CDE.VC_CUST_NO = '004098'
) AT
ORDER BY
AT.requestNo DESC
LIMIT 10
处理
SELECT
*
FROM
(
SELECT
( CASE WHEN E.c_busin_flag = '137' THEN E.vc_trade_acco ELSE A1.VC_TRADE_ACCO END ) AS tradeAcco,
A1.vc_fund_acco AS productAcco,
( CASE WHEN E.c_busin_flag = '137' THEN E.vc_product_code ELSE A1.vc_product_code END ) AS productCode,
A1.c_busin_flag AS businFlag,
A1.vc_request_no AS requestNo,
A1.vc_request_date AS requestDate,
E.vc_net_no AS netNo,
A1.vc_ta_code AS taCode,
E.c_share_type AS shareType,
E.vc_confirm_date AS confirmDate,
E.c_money_type AS moneyType,
E.en_confirm_share AS confirmShare,
E.en_confirm_bala AS confirmBala,
E.en_nav AS enNav,
E.en_fare AS fare,
E.en_agency_fare AS agencyFare,
E.en_stamp_tax AS stampTax,
( CASE WHEN E.c_busin_flag = '137' THEN E.vc_other_product_code ELSE A1.vc_other_product_code END ) AS otherProductCode,
( CASE WHEN E.c_busin_flag = '137' THEN E.c_other_share_type ELSE A1.c_other_share_type END ) AS otherShareType,
A1.vc_other_acco AS otherAcco,
A1.vc_other_agency_no AS otherAgencyNo,
E.vc_other_net_no AS otherNetNo,
E.vc_error_code AS errorCode,
E.vc_error_cause AS errorCause,
A1.c_frozen_cause AS frozenCause,
E.vc_origin_no AS originNo,
E.en_income AS income,
E.en_invest_income AS investIncome,
A2.en_invest_share AS investShare,
A2.vc_origin_date AS originDate,
E.en_after_fare AS afterFare,
E.en_origin_fare AS originFare,
A1.c_confirm_flag AS confirmFlag,
E.en_deal_fare AS dealFare,
E.c_confirm_status AS confirmStatus,
E.en_ta_fare AS taFare,
A1.c_capital_mode AS capitalMode,
A1.c_fix_busin_flag AS fixBusinFlag,
A1.c_trade_busin_type AS tradeBusinType,
A2.c_exp_type AS expType,
A2.l_section_schema_no AS sectionSchemaNo,
A1.en_balance AS balance,
A1.en_share AS enshare,
A2.vc_sub_acco_auditno AS subAccoAuditn,
A2.vc_comb_request_no AS combRequestNo,
A2.vc_comb_code AS combCode,
E.c_bank_no AS bankNo,
C.vc_bank_acco AS bankAcco,
A1.c_sub_capital_mode AS subCapitalMode,
A2.vc_trade_contact AS tradeContact,
A2.vc_broker AS broker,
A2.c_redeem_use_flag AS redeemUseFlag,
E.en_interest AS interest,
E.c_finish_flag AS finishFlag,
substr( A1.vc_accept_date, 1, 8 ) acceptDate,
substr( A1.vc_accept_time, 9, 6 ) acceptTime,
A1.vc_sub_acco_no AS subAccoNo,
A1.vc_come_from AS comeFrom,
A1.vc_cust_no AS custNo,
A1.c_dividend_method AS dividendMethod,
A1.C_CUST_CONFIRM AS custcomFirm,
A1.VC_BATCH_REQUEST_NO AS batchRequestNo,
A1.VC_BATCH_NO AS batchNo,
A1.VC_OTHER_SERIAL_NO AS otherSerialNo,
A1.VC_CONFIRM_SETTLE_DATE AS comfirmSettleDate,
D1.C_PRODUCT_CATEGORY AS productCategory,
A1.c_trust AS trustFlag,
A2.VC_VOUCHER_NO AS voucher_no,
A2.vc_protocol AS protocol,
( CASE WHEN A1.c_Busin_Flag IN ( '024', '025', '036' ) THEN '1' ELSE'0' END ) AS redeemReqFlag,
( CASE WHEN A1.c_Busin_Flag IN ( '022', '023', '039' ) THEN '1' ELSE'0' END ) AS allotReqFlag,
F.vc_need_date AS needDate,
D1.C_PRODUCT_SUB_TYPE AS productSubType,
E.VC_CONFIRM_NO AS confirmNo,
E.C_BUSIN_FLAG AS confirmBusinFlag,
B.C_USER_TYPE AS userType,
CFG.VC_MAIN_PRODUCT_CODE AS mainProductCode,
CFG.C_SIDE_BAG_FLAG AS sideBagFlag,
CFG.VC_SIDE_BAG_START_DATE AS sideBagStartDate,
cust.VC_IDENTITY_NO AS identityNo,
cust.C_IDENTITY_TYPE AS identityType,
cde.C_CUST_KIND AS custKind,
E.vc_bank_name AS bankName,
E.en_discount AS discount,
A1.vc_hope_date AS hopeDate,
a2.VC_REMIT_DEAD_DATE AS remitDeadDate,
a2.VC_REMIT_DEAD_TIME AS remitDeadTime,
A2.C_CUST_TYPE AS custType,
CUST.vc_custom_name AS customName,
D1.C_PRODUCT_TYPE AS productType,
D1.VC_PRODUCT_NAME AS productName,
A1.c_trust AS trust,
A2.C_PROMOTION AS promotion,
cde.VC_PHONE AS phone,
cde.VC_ADDRESS AS address,
cde.VC_ZIP AS zip,
cde.VC_MOBILE_NO AS mobileNo,
cde.VC_FAX_NO AS faxNo,
cde.VC_EMAIL AS email,
cap.C_PUB_PROFESSION_FLAG AS pubProfessionFlag,
cap.C_PUB_PROFESSION_SUB_TYPE AS pubProfessionSubType,
cap.C_PUB_HG_FLAG AS pubHgFlag,
cap.VC_PUB_PROFESSION_VALID_DATE AS pubProfessionValidDate,
D2.C_PRODUCT_RISK_LEVEL AS productRiskLevel,
tk.c_cust_risk_type AS custRiskType,
e.EN_OTHER_FEE1 AS otherFee1,
e.EN_OTHER_FEE2 AS otherFee2,
ta.vc_ta_name AS taName,
a1.vc_other_trade_acco AS otherTradeAcco
FROM
(
SELECT C
.vc_tenant_id AS VC_TENANT_ID,
C.vc_request_no AS VC_REQUEST_NO,
C.vc_fund_acco AS VC_FUND_ACCO,
C.vc_trade_acco AS VC_TRADE_ACCO,
C.vc_request_date AS VC_REQUEST_DATE,
C.vc_request_time AS VC_REQUEST_TIME,
C.c_busin_flag AS C_BUSIN_FLAG,
C.vc_product_code AS VC_PRODUCT_CODE,
C.c_share_type AS C_SHARE_TYPE,
C.c_trust AS C_TRUST,
C.en_balance AS EN_BALANCE,
C.en_share AS EN_SHARE,
C.en_discount AS EN_DISCOUNT,
C.en_end_discount AS EN_END_DISCOUNT,
C.en_other_discount AS EN_OTHER_DISCOUNT,
C.c_send_state AS C_SEND_STATE,
C.c_trade_state AS C_TRADE_STATE,
C.c_confirm_flag AS C_CONFIRM_FLAG,
C.en_confirm_share AS EN_CONFIRM_SHARE,
C.en_confirm_bala AS EN_CONFIRM_BALA,
C.vc_hope_date AS VC_HOPE_DATE,
C.vc_other_acco AS VC_OTHER_ACCO,
C.vc_other_trade_acco AS VC_OTHER_TRADE_ACCO,
C.vc_other_product_code AS VC_OTHER_PRODUCT_CODE,
C.c_other_share_type AS C_OTHER_SHARE_TYPE,
C.vc_other_agency_no AS VC_OTHER_AGENCY_NO,
C.c_dividend_method AS C_DIVIDEND_METHOD,
C.c_frozen_flag AS C_FROZEN_FLAG,
C.c_frozen_cause AS C_FROZEN_CAUSE,
C.c_capital_mode AS C_CAPITAL_MODE,
C.c_sub_capital_mode AS C_SUB_CAPITAL_MODE,
C.c_interface_type AS C_INTERFACE_TYPE,
C.l_delay_day AS L_DELAY_DAY,
C.c_audit_flag AS C_AUDIT_FLAG,
C.vc_ta_code AS VC_TA_CODE,
C.vc_accept_date AS VC_ACCEPT_DATE,
C.vc_accept_time AS VC_ACCEPT_TIME,
C.c_check_flag AS C_CHECK_FLAG,
C.c_cust_confirm AS C_CUST_CONFIRM,
C.c_fix_busin_flag AS C_FIX_BUSIN_FLAG,
C.vc_sys_date AS VC_SYS_DATE,
C.vc_machine_date AS VC_MACHINE_DATE,
C.vc_machine_time AS VC_MACHINE_TIME,
C.vc_batch_no AS VC_BATCH_NO,
C.c_trade_busin_type AS C_TRADE_BUSIN_TYPE,
C.vc_come_from AS VC_COME_FROM,
C.vc_china_pay_serial_no AS VC_CHINA_PAY_SERIAL_NO,
C.c_auto_request AS C_AUTO_REQUEST,
C.vc_sub_acco_no AS VC_SUB_ACCO_NO,
C.vc_cust_no AS VC_CUST_NO,
C.vc_batch_request_no AS VC_BATCH_REQUEST_NO,
C.vc_confirm_settle_date AS VC_CONFIRM_SETTLE_DATE,
C.vc_request_settle_date AS VC_REQUEST_SETTLE_DATE,
C.vc_cap_acco AS VC_CAP_ACCO,
C.vc_function_no AS VC_FUNCTION_NO,
C.l_node AS L_NODE,
C.c_export_state AS C_EXPORT_STATE,
C.c_delete AS C_DELETE,
C.vc_timestamp AS VC_TIMESTAMP,
C.vc_other_serial_no AS VC_OTHER_SERIAL_NO,
C.c_send_liq_state AS C_SEND_LIQ_STATE,
C.c_record_state AS C_RECORD_STATE
FROM
tc_threquest C
UNION ALL
SELECT A
.vc_tenant_id AS VC_TENANT_ID,
A.vc_request_no AS VC_REQUEST_NO,
A.vc_fund_acco AS VC_FUND_ACCO,
A.vc_trade_acco AS VC_TRADE_ACCO,
A.vc_request_date AS VC_REQUEST_DATE,
A.vc_request_time AS VC_REQUEST_TIME,
A.c_busin_flag AS C_BUSIN_FLAG,
A.vc_product_code AS VC_PRODUCT_CODE,
A.c_share_type AS C_SHARE_TYPE,
A.c_trust AS C_TRUST,
A.en_balance AS EN_BALANCE,
A.en_share AS EN_SHARE,
A.en_discount AS EN_DISCOUNT,
A.en_end_discount AS EN_END_DISCOUNT,
A.en_other_discount AS EN_OTHER_DISCOUNT,
A.c_send_state AS C_SEND_STATE,
A.c_trade_state AS C_TRADE_STATE,
CASE
WHEN b.c_confirm_flag IS NULL THEN
A.c_confirm_flag ELSE b.c_confirm_flag
END AS C_CONFIRM_FLAG,
CASE
WHEN b.en_confirm_share IS NULL THEN
A.en_confirm_share ELSE b.en_confirm_share
END AS EN_CONFIRM_SHARE,
CASE
WHEN b.en_confirm_bala IS NULL THEN
A.en_confirm_bala ELSE b.en_confirm_bala
END AS EN_CONFIRM_BALA,
A.vc_hope_date AS VC_HOPE_DATE,
A.vc_other_acco AS VC_OTHER_ACCO,
A.vc_other_trade_acco AS VC_OTHER_TRADE_ACCO,
A.vc_other_product_code AS VC_OTHER_PRODUCT_CODE,
A.c_other_share_type AS C_OTHER_SHARE_TYPE,
A.vc_other_agency_no AS VC_OTHER_AGENCY_NO,
A.c_dividend_method AS C_DIVIDEND_METHOD,
A.c_frozen_flag AS C_FROZEN_FLAG,
A.c_frozen_cause AS C_FROZEN_CAUSE,
A.c_capital_mode AS C_CAPITAL_MODE,
A.c_sub_capital_mode AS C_SUB_CAPITAL_MODE,
A.c_interface_type AS C_INTERFACE_TYPE,
A.l_delay_day AS L_DELAY_DAY,
A.c_audit_flag AS C_AUDIT_FLAG,
A.vc_ta_code AS VC_TA_CODE,
A.vc_accept_date AS VC_ACCEPT_DATE,
A.vc_accept_time AS VC_ACCEPT_TIME,
A.c_check_flag AS C_CHECK_FLAG,
A.c_cust_confirm AS C_CUST_CONFIRM,
A.c_fix_busin_flag AS C_FIX_BUSIN_FLAG,
A.vc_sys_date AS VC_SYS_DATE,
A.vc_machine_date AS VC_MACHINE_DATE,
A.vc_machine_time AS VC_MACHINE_TIME,
A.vc_batch_no AS VC_BATCH_NO,
A.c_trade_busin_type AS C_TRADE_BUSIN_TYPE,
A.vc_come_from AS VC_COME_FROM,
A.vc_china_pay_serial_no AS VC_CHINA_PAY_SERIAL_NO,
A.c_auto_request AS C_AUTO_REQUEST,
A.vc_sub_acco_no AS VC_SUB_ACCO_NO,
A.vc_cust_no AS VC_CUST_NO,
A.vc_batch_request_no AS VC_BATCH_REQUEST_NO,
A.vc_confirm_settle_date AS VC_CONFIRM_SETTLE_DATE,
A.vc_request_settle_date AS VC_REQUEST_SETTLE_DATE,
A.vc_cap_acco AS VC_CAP_ACCO,
A.vc_function_no AS VC_FUNCTION_NO,
A.l_node AS L_NODE,
A.c_export_state AS C_EXPORT_STATE,
A.c_delete AS C_DELETE,
A.vc_timestamp AS VC_TIMESTAMP,
A.vc_other_serial_no AS VC_OTHER_SERIAL_NO,
A.c_send_liq_state AS C_SEND_LIQ_STATE,
A.c_record_state AS C_RECORD_STATE
FROM
tc_trequest
A LEFT JOIN lc_trequest_liq b ON A.vc_tenant_id = b.vc_tenant_id
AND A.vc_request_no = b.vc_request_no
) A1
INNER JOIN LC_TCONFIRM E ON A1.VC_TENANT_ID = E.VC_TENANT_ID AND A1.VC_REQUEST_NO = E.VC_REQUEST_NO
INNER JOIN (SELECT A.vc_tenant_id AS VC_TENANT_ID,
A.vc_request_no AS VC_REQUEST_NO,
A.vc_cust_no AS VC_CUST_NO,
A.c_cust_type AS C_CUST_TYPE,
A.vc_voucher_no AS VC_VOUCHER_NO,
A.vc_net_no AS VC_NET_NO,
A.vc_center_no AS VC_CENTER_NO,
A.c_trade_flag AS C_TRADE_FLAG,
A.c_re_allot_flag AS C_RE_ALLOT_FLAG,
A.c_trust AS C_TRUST,
A.c_money_type AS C_MONEY_TYPE,
A.vc_operator_no AS VC_OPERATOR_NO,
A.vc_check_no AS VC_CHECK_NO,
A.vc_broker AS VC_BROKER,
A.c_exceed_flag AS C_EXCEED_FLAG,
A.c_origin_flag AS C_ORIGIN_FLAG,
A.vc_origin_no AS VC_ORIGIN_NO,
A.vc_origin_date AS VC_ORIGIN_DATE,
A.vc_origin_confirm_no AS VC_ORIGIN_CONFIRM_NO,
A.vc_origin_confirm_date AS VC_ORIGIN_CONFIRM_DATE,
A.c_bank_no AS C_BANK_NO,
A.vc_bank_name AS VC_BANK_NAME,
A.vc_name_in_bank AS VC_NAME_IN_BANK,
A.vc_bank_acco AS VC_BANK_ACCO,
A.vc_change_type AS VC_CHANGE_TYPE,
A.vc_other_net_no AS VC_OTHER_NET_NO,
A.vc_explain AS VC_EXPLAIN,
A.vc_protocol AS VC_PROTOCOL,
A.l_permit_num AS L_PERMIT_NUM,
A.vc_end_date AS VC_END_DATE,
A.vc_first_trade_date AS VC_FIRST_TRADE_DATE,
A.vc_fix_day AS VC_FIX_DAY,
A.vc_book_no AS VC_BOOK_NO,
A.vc_cycle AS VC_CYCLE,
A.vc_reg_date AS VC_REG_DATE,
A.vc_city_no AS VC_CITY_NO,
A.vc_seller AS VC_SELLER,
A.vc_comb_code AS VC_COMB_CODE,
A.vc_comb_request_no AS VC_COMB_REQUEST_NO,
A.en_invest_scale AS EN_INVEST_SCALE,
A.vc_audit_no AS VC_AUDIT_NO,
A.en_invest_share AS EN_INVEST_SHARE,
A.c_promotion AS C_PROMOTION,
A.vc_exponent_code AS VC_EXPONENT_CODE,
A.c_exponent_flag AS C_EXPONENT_FLAG,
A.en_exponent_value AS EN_EXPONENT_VALUE,
A.en_fact_bala AS EN_FACT_BALA,
A.c_part_flag AS C_PART_FLAG,
A.en_due_fare AS EN_DUE_FARE,
A.c_cp_flag AS C_CP_FLAG,
A.c_invest_flag AS C_INVEST_FLAG,
A.en_transfer_fee AS EN_TRANSFER_FEE,
A.vc_bank_id AS VC_BANK_ID,
A.vc_cp_error_code AS VC_CP_ERROR_CODE,
A.vc_invest_content AS VC_INVEST_CONTENT,
A.vc_transfer_date AS VC_TRANSFER_DATE,
A.c_discount_flag AS C_DISCOUNT_FLAG,
A.vc_confirm_date AS VC_CONFIRM_DATE,
A.vc_bank_net_date AS VC_BANK_NET_DATE,
A.vc_trade_contact AS VC_TRADE_CONTACT,
A.vc_securities_account AS VC_SECURITIES_ACCOUNT,
A.c_risk_flag AS C_RISK_FLAG,
A.vc_protocol_name AS VC_PROTOCOL_NAME,
A.c_cycle_unit AS C_CYCLE_UNIT,
A.c_exp_type AS C_EXP_TYPE,
A.l_max_success_num AS L_MAX_SUCCESS_NUM,
A.en_max_value AS EN_MAX_VALUE,
A.en_min_scale1 AS EN_MIN_SCALE1,
A.en_max_scale1 AS EN_MAX_SCALE1,
A.en_scal_balance1 AS EN_SCAL_BALANCE1,
A.en_min_scale2 AS EN_MIN_SCALE2,
A.en_max_scale2 AS EN_MAX_SCALE2,
A.en_scal_balance2 AS EN_SCAL_BALANCE2,
A.en_min_scale3 AS EN_MIN_SCALE3,
A.en_max_scale3 AS EN_MAX_SCALE3,
A.en_scal_balance3 AS EN_SCAL_BALANCE3,
A.c_cust_risk_type AS C_CUST_RISK_TYPE,
A.c_product_risk_level AS C_PRODUCT_RISK_LEVEL,
A.c_aml_full_flag AS C_AML_FULL_FLAG,
A.c_allow_deduct AS C_ALLOW_DEDUCT,
A.c_avg_exponent_type AS C_AVG_EXPONENT_TYPE,
A.en_diff_scale AS EN_DIFF_SCALE,
A.vc_trade_purpose AS VC_TRADE_PURPOSE,
A.en_upper_limit AS EN_UPPER_LIMIT,
A.en_lower_limit AS EN_LOWER_LIMIT,
A.vc_link_deduct_no AS VC_LINK_DEDUCT_NO,
A.vc_recover_date AS VC_RECOVER_DATE,
A.vc_last_deduct_time AS VC_LAST_DEDUCT_TIME,
A.vc_comm_error_no AS VC_COMM_ERROR_NO,
A.vc_comm_error_msg AS VC_COMM_ERROR_MSG,
A.vc_cp_error_msg AS VC_CP_ERROR_MSG,
A.l_section_schema_no AS L_SECTION_SCHEMA_NO,
A.vc_section_schema_type AS VC_SECTION_SCHEMA_TYPE,
A.c_scan_flag AS C_SCAN_FLAG,
A.vc_fix_month AS VC_FIX_MONTH,
A.en_invenst_rate AS EN_INVENST_RATE,
A.vc_recommender AS VC_RECOMMENDER,
A.vc_recommender_phone AS VC_RECOMMENDER_PHONE,
A.vc_recommender_province AS VC_RECOMMENDER_PROVINCE,
A.vc_recommender_city_no AS VC_RECOMMENDER_CITY_NO,
A.vc_source_ip_addr AS VC_SOURCE_IP_ADDR,
A.vc_deduct_product_code AS VC_DEDUCT_PRODUCT_CODE,
A.vc_sub_acco_auditno AS VC_SUB_ACCO_AUDITNO,
A.l_special_liq_day AS L_SPECIAL_LIQ_DAY,
A.en_max_value_once AS EN_MAX_VALUE_ONCE,
A.en_min_retained_bala AS EN_MIN_RETAINED_BALA,
A.en_lower_deduct_bala AS EN_LOWER_DEDUCT_BALA,
A.c_fix_comb_type AS C_FIX_COMB_TYPE,
A.vc_hedge_product_code AS VC_HEDGE_PRODUCT_CODE,
A.c_hedge_share_type AS C_HEDGE_SHARE_TYPE,
A.c_short_avg_exponent_type AS C_SHORT_AVG_EXPONENT_TYPE,
A.c_medium_avg_exponent_type AS C_MEDIUM_AVG_EXPONENT_TYPE,
A.c_long_avg_exponent_type AS C_LONG_AVG_EXPONENT_TYPE,
A.vc_hedge_protocol AS VC_HEDGE_PROTOCOL,
A.c_break_flag AS C_BREAK_FLAG,
A.vc_self_comb_code AS VC_SELF_COMB_CODE,
A.vc_match_no AS VC_MATCH_NO,
A.vc_match_audit_no AS VC_MATCH_AUDIT_NO,
A.vc_contract_no AS VC_CONTRACT_NO,
A.c_redeem_use_flag AS C_REDEEM_USE_FLAG,
A.c_recreate_batch_no AS C_RECREATE_BATCH_NO,
A.vc_contract_serial_no AS VC_CONTRACT_SERIAL_NO,
A.c_profit_class AS C_PROFIT_CLASS,
A.vc_reject_reason AS VC_REJECT_REASON,
A.vc_ext_comb_code AS VC_EXT_COMB_CODE,
A.c_non_work_day_delay AS C_NON_WORK_DAY_DELAY,
A.vc_payment_acco_no AS VC_PAYMENT_ACCO_NO,
A.vc_payment_date AS VC_PAYMENT_DATE,
A.vc_payment_type AS VC_PAYMENT_TYPE,
A.vc_cd_card AS VC_CD_CARD,
A.c_payment_bala_type AS C_PAYMENT_BALA_TYPE,
A.en_income AS EN_INCOME,
A.c_trade_deal_flag AS C_TRADE_DEAL_FLAG,
A.c_loan_flag AS C_LOAN_FLAG,
A.c_syn_state AS C_SYN_STATE,
A.c_need_deduct AS C_NEED_DEDUCT,
A.c_need_transfer AS C_NEED_TRANSFER,
A.c_change_direct AS C_CHANGE_DIRECT,
A.c_hk_capital_mode AS C_HK_CAPITAL_MODE,
A.vc_bill_serial_no AS VC_BILL_SERIAL_NO,
A.vc_use_content AS VC_USE_CONTENT,
A.c_ft_redeem_mode AS C_FT_REDEEM_MODE,
A.vc_bank_net_time AS VC_BANK_NET_TIME,
A.vc_book_id AS VC_BOOK_ID,
A.vc_unfrozen_date AS VC_UNFROZEN_DATE,
A.vc_invest_date AS VC_INVEST_DATE,
A.vc_need_date AS VC_NEED_DATE,
A.vc_bank_province_code AS VC_BANK_PROVINCE_CODE,
A.vc_bank_city_no AS VC_BANK_CITY_NO,
A.vc_acco_id AS VC_ACCO_ID,
A.vc_confirm_benefit_serial_no AS VC_CONFIRM_BENEFIT_SERIAL_NO,
A.c_ft_rdm_trans_mode AS C_FT_RDM_TRANS_MODE,
A.c_capin_flag AS C_CAPIN_FLAG,
A.c_need_frozen_share AS C_NEED_FROZEN_SHARE,
A.c_identity_type_in_bank AS C_IDENTITY_TYPE_IN_BANK,
A.vc_identity_no_in_bank AS VC_IDENTITY_NO_IN_BANK,
A.vc_liquidate_date AS VC_LIQUIDATE_DATE,
A.en_coupon AS EN_COUPON,
A.en_nav AS EN_NAV,
A.vc_fuse_oridate AS VC_FUSE_ORIDATE,
A.vc_share_expire_date AS VC_SHARE_EXPIRE_DATE,
A.vc_out_requestno AS VC_OUT_REQUESTNO,
A.en_gram_num AS EN_GRAM_NUM,
A.vc_resub_date AS VC_RESUB_DATE,
A.vc_calm_time AS VC_CALM_TIME,
A.vc_register_serialno AS VC_REGISTER_SERIALNO,
A.vc_ori_operator_no AS VC_ORI_OPERATOR_NO,
A.c_cust_fund_time_limit_match AS C_CUST_FUND_TIME_LIMIT_MATCH,
A.c_invest_varieties_match_flag AS C_INVEST_VARIETIES_MATCH_FLAG,
A.c_cust_investment_varieties AS C_CUST_INVESTMENT_VARIETIES,
A.c_fund_investment_varieties AS C_FUND_INVESTMENT_VARIETIES,
A.en_cust_time_limit AS EN_CUST_TIME_LIMIT,
A.en_fund_time_limit AS EN_FUND_TIME_LIMIT,
A.vc_cool_start_time AS VC_COOL_START_TIME,
A.c_return_flag AS C_RETURN_FLAG,
A.l_node AS L_NODE,
A.c_deal_flag AS C_DEAL_FLAG,
A.c_stop_profit AS C_STOP_PROFIT,
A.vc_timestamp AS VC_TIMESTAMP,
A.vc_investor_system_id AS VC_INVESTOR_SYSTEM_ID,
A.vc_ip_address AS VC_IP_ADDRESS,
A.vc_mac_address AS VC_MAC_ADDRESS,
A.vc_imei AS VC_IMEI,
A.vc_uuid AS VC_UUID,
A.c_voucher_flag AS C_VOUCHER_FLAG,
A.vc_hedge_trade_acco AS VC_HEDGE_TRADE_ACCO,
A.vc_hedge_ta_code AS VC_HEDGE_TA_CODE,
A.vc_freeze_share_request_no AS VC_FREEZE_SHARE_REQUEST_NO,
A.vc_fix_busin_flag_list AS VC_FIX_BUSIN_FLAG_LIST,
A.vc_pay_grant_org AS VC_PAY_GRANT_ORG,
A.vc_strategy_type AS VC_STRATEGY_TYPE,
A.c_ia_notify_flag AS C_IA_NOTIFY_FLAG,
A.c_sum_deduct_flag AS C_SUM_DEDUCT_FLAG,
A.vc_specify_request_date AS VC_SPECIFY_REQUEST_DATE,
A.vc_ins_busin_flag AS VC_INS_BUSIN_FLAG,
A.vc_json_content AS VC_JSON_CONTENT,
A.c_share_expire_redeem_mode AS C_SHARE_EXPIRE_REDEEM_MODE,
A.vc_plan_no AS VC_PLAN_NO,
A.VC_REMIT_DEAD_DATE AS VC_REMIT_DEAD_DATE,
A.VC_REMIT_DEAD_TIME AS VC_REMIT_DEAD_TIME
FROM
tc_trequestdetail A UNION ALL
SELECT
b.vc_tenant_id AS VC_TENANT_ID,
b.vc_request_no AS VC_REQUEST_NO,
b.vc_cust_no AS VC_CUST_NO,
b.c_cust_type AS C_CUST_TYPE,
b.vc_voucher_no AS VC_VOUCHER_NO,
b.vc_net_no AS VC_NET_NO,
b.vc_center_no AS VC_CENTER_NO,
b.c_trade_flag AS C_TRADE_FLAG,
b.c_re_allot_flag AS C_RE_ALLOT_FLAG,
b.c_trust AS C_TRUST,
b.c_money_type AS C_MONEY_TYPE,
b.vc_operator_no AS VC_OPERATOR_NO,
b.vc_check_no AS VC_CHECK_NO,
b.vc_broker AS VC_BROKER,
b.c_exceed_flag AS C_EXCEED_FLAG,
b.c_origin_flag AS C_ORIGIN_FLAG,
b.vc_origin_no AS VC_ORIGIN_NO,
b.vc_origin_date AS VC_ORIGIN_DATE,
b.vc_origin_confirm_no AS VC_ORIGIN_CONFIRM_NO,
b.vc_origin_confirm_date AS VC_ORIGIN_CONFIRM_DATE,
b.c_bank_no AS C_BANK_NO,
b.vc_bank_name AS VC_BANK_NAME,
b.vc_name_in_bank AS VC_NAME_IN_BANK,
b.vc_bank_acco AS VC_BANK_ACCO,
b.vc_change_type AS VC_CHANGE_TYPE,
b.vc_other_net_no AS VC_OTHER_NET_NO,
b.vc_explain AS VC_EXPLAIN,
b.vc_protocol AS VC_PROTOCOL,
b.l_permit_num AS L_PERMIT_NUM,
b.vc_end_date AS VC_END_DATE,
b.vc_first_trade_date AS VC_FIRST_TRADE_DATE,
b.vc_fix_day AS VC_FIX_DAY,
b.vc_book_no AS VC_BOOK_NO,
b.vc_cycle AS VC_CYCLE,
b.vc_reg_date AS VC_REG_DATE,
b.vc_city_no AS VC_CITY_NO,
b.vc_seller AS VC_SELLER,
b.vc_comb_code AS VC_COMB_CODE,
b.vc_comb_request_no AS VC_COMB_REQUEST_NO,
b.en_invest_scale AS EN_INVEST_SCALE,
b.vc_audit_no AS VC_AUDIT_NO,
b.en_invest_share AS EN_INVEST_SHARE,
b.c_promotion AS C_PROMOTION,
b.vc_exponent_code AS VC_EXPONENT_CODE,
b.c_exponent_flag AS C_EXPONENT_FLAG,
b.en_exponent_value AS EN_EXPONENT_VALUE,
b.en_fact_bala AS EN_FACT_BALA,
b.c_part_flag AS C_PART_FLAG,
b.en_due_fare AS EN_DUE_FARE,
b.c_cp_flag AS C_CP_FLAG,
b.c_invest_flag AS C_INVEST_FLAG,
b.en_transfer_fee AS EN_TRANSFER_FEE,
b.vc_bank_id AS VC_BANK_ID,
b.vc_cp_error_code AS VC_CP_ERROR_CODE,
b.vc_invest_content AS VC_INVEST_CONTENT,
b.vc_transfer_date AS VC_TRANSFER_DATE,
b.c_discount_flag AS C_DISCOUNT_FLAG,
b.vc_confirm_date AS VC_CONFIRM_DATE,
b.vc_bank_net_date AS VC_BANK_NET_DATE,
b.vc_trade_contact AS VC_TRADE_CONTACT,
b.vc_securities_account AS VC_SECURITIES_ACCOUNT,
b.c_risk_flag AS C_RISK_FLAG,
b.vc_protocol_name AS VC_PROTOCOL_NAME,
b.c_cycle_unit AS C_CYCLE_UNIT,
b.c_exp_type AS C_EXP_TYPE,
b.l_max_success_num AS L_MAX_SUCCESS_NUM,
b.en_max_value AS EN_MAX_VALUE,
b.en_min_scale1 AS EN_MIN_SCALE1,
b.en_max_scale1 AS EN_MAX_SCALE1,
b.en_scal_balance1 AS EN_SCAL_BALANCE1,
b.en_min_scale2 AS EN_MIN_SCALE2,
b.en_max_scale2 AS EN_MAX_SCALE2,
b.en_scal_balance2 AS EN_SCAL_BALANCE2,
b.en_min_scale3 AS EN_MIN_SCALE3,
b.en_max_scale3 AS EN_MAX_SCALE3,
b.en_scal_balance3 AS EN_SCAL_BALANCE3,
b.c_cust_risk_type AS C_CUST_RISK_TYPE,
b.c_product_risk_level AS C_PRODUCT_RISK_LEVEL,
b.c_aml_full_flag AS C_AML_FULL_FLAG,
b.c_allow_deduct AS C_ALLOW_DEDUCT,
b.c_avg_exponent_type AS C_AVG_EXPONENT_TYPE,
b.en_diff_scale AS EN_DIFF_SCALE,
b.vc_trade_purpose AS VC_TRADE_PURPOSE,
b.en_upper_limit AS EN_UPPER_LIMIT,
b.en_lower_limit AS EN_LOWER_LIMIT,
b.vc_link_deduct_no AS VC_LINK_DEDUCT_NO,
b.vc_recover_date AS VC_RECOVER_DATE,
b.vc_last_deduct_time AS VC_LAST_DEDUCT_TIME,
b.vc_comm_error_no AS VC_COMM_ERROR_NO,
b.vc_comm_error_msg AS VC_COMM_ERROR_MSG,
b.vc_cp_error_msg AS VC_CP_ERROR_MSG,
b.l_section_schema_no AS L_SECTION_SCHEMA_NO,
b.vc_section_schema_type AS VC_SECTION_SCHEMA_TYPE,
b.c_scan_flag AS C_SCAN_FLAG,
b.vc_fix_month AS VC_FIX_MONTH,
b.en_invenst_rate AS EN_INVENST_RATE,
b.vc_recommender AS VC_RECOMMENDER,
b.vc_recommender_phone AS VC_RECOMMENDER_PHONE,
b.vc_recommender_province AS VC_RECOMMENDER_PROVINCE,
b.vc_recommender_city_no AS VC_RECOMMENDER_CITY_NO,
b.vc_source_ip_addr AS VC_SOURCE_IP_ADDR,
b.vc_deduct_product_code AS VC_DEDUCT_PRODUCT_CODE,
b.vc_sub_acco_auditno AS VC_SUB_ACCO_AUDITNO,
b.l_special_liq_day AS L_SPECIAL_LIQ_DAY,
b.en_max_value_once AS EN_MAX_VALUE_ONCE,
b.en_min_retained_bala AS EN_MIN_RETAINED_BALA,
b.en_lower_deduct_bala AS EN_LOWER_DEDUCT_BALA,
b.c_fix_comb_type AS C_FIX_COMB_TYPE,
b.vc_hedge_product_code AS VC_HEDGE_PRODUCT_CODE,
b.c_hedge_share_type AS C_HEDGE_SHARE_TYPE,
b.c_short_avg_exponent_type AS C_SHORT_AVG_EXPONENT_TYPE,
b.c_medium_avg_exponent_type AS C_MEDIUM_AVG_EXPONENT_TYPE,
b.c_long_avg_exponent_type AS C_LONG_AVG_EXPONENT_TYPE,
b.vc_hedge_protocol AS VC_HEDGE_PROTOCOL,
b.c_break_flag AS C_BREAK_FLAG,
b.vc_self_comb_code AS VC_SELF_COMB_CODE,
b.vc_match_no AS VC_MATCH_NO,
b.vc_match_audit_no AS VC_MATCH_AUDIT_NO,
b.vc_contract_no AS VC_CONTRACT_NO,
b.c_redeem_use_flag AS C_REDEEM_USE_FLAG,
b.c_recreate_batch_no AS C_RECREATE_BATCH_NO,
b.vc_contract_serial_no AS VC_CONTRACT_SERIAL_NO,
b.c_profit_class AS C_PROFIT_CLASS,
b.vc_reject_reason AS VC_REJECT_REASON,
b.vc_ext_comb_code AS VC_EXT_COMB_CODE,
b.c_non_work_day_delay AS C_NON_WORK_DAY_DELAY,
b.vc_payment_acco_no AS VC_PAYMENT_ACCO_NO,
b.vc_payment_date AS VC_PAYMENT_DATE,
b.vc_payment_type AS VC_PAYMENT_TYPE,
b.vc_cd_card AS VC_CD_CARD,
b.c_payment_bala_type AS C_PAYMENT_BALA_TYPE,
b.en_income AS EN_INCOME,
b.c_trade_deal_flag AS C_TRADE_DEAL_FLAG,
b.c_loan_flag AS C_LOAN_FLAG,
b.c_syn_state AS C_SYN_STATE,
b.c_need_deduct AS C_NEED_DEDUCT,
b.c_need_transfer AS C_NEED_TRANSFER,
b.c_change_direct AS C_CHANGE_DIRECT,
b.c_hk_capital_mode AS C_HK_CAPITAL_MODE,
b.vc_bill_serial_no AS VC_BILL_SERIAL_NO,
b.vc_use_content AS VC_USE_CONTENT,
b.c_ft_redeem_mode AS C_FT_REDEEM_MODE,
b.vc_bank_net_time AS VC_BANK_NET_TIME,
b.vc_book_id AS VC_BOOK_ID,
b.vc_unfrozen_date AS VC_UNFROZEN_DATE,
b.vc_invest_date AS VC_INVEST_DATE,
b.vc_need_date AS VC_NEED_DATE,
b.vc_bank_province_code AS VC_BANK_PROVINCE_CODE,
b.vc_bank_city_no AS VC_BANK_CITY_NO,
b.vc_acco_id AS VC_ACCO_ID,
b.vc_confirm_benefit_serial_no AS VC_CONFIRM_BENEFIT_SERIAL_NO,
b.c_ft_rdm_trans_mode AS C_FT_RDM_TRANS_MODE,
b.c_capin_flag AS C_CAPIN_FLAG,
b.c_need_frozen_share AS C_NEED_FROZEN_SHARE,
b.c_identity_type_in_bank AS C_IDENTITY_TYPE_IN_BANK,
b.vc_identity_no_in_bank AS VC_IDENTITY_NO_IN_BANK,
b.vc_liquidate_date AS VC_LIQUIDATE_DATE,
b.en_coupon AS EN_COUPON,
b.en_nav AS EN_NAV,
b.vc_fuse_oridate AS VC_FUSE_ORIDATE,
b.vc_share_expire_date AS VC_SHARE_EXPIRE_DATE,
b.vc_out_requestno AS VC_OUT_REQUESTNO,
b.en_gram_num AS EN_GRAM_NUM,
b.vc_resub_date AS VC_RESUB_DATE,
b.vc_calm_time AS VC_CALM_TIME,
b.vc_register_serialno AS VC_REGISTER_SERIALNO,
b.vc_ori_operator_no AS VC_ORI_OPERATOR_NO,
b.c_cust_fund_time_limit_match AS C_CUST_FUND_TIME_LIMIT_MATCH,
b.c_invest_varieties_match_flag AS C_INVEST_VARIETIES_MATCH_FLAG,
b.c_cust_investment_varieties AS C_CUST_INVESTMENT_VARIETIES,
b.c_fund_investment_varieties AS C_FUND_INVESTMENT_VARIETIES,
b.en_cust_time_limit AS EN_CUST_TIME_LIMIT,
b.en_fund_time_limit AS EN_FUND_TIME_LIMIT,
b.vc_cool_start_time AS VC_COOL_START_TIME,
b.c_return_flag AS C_RETURN_FLAG,
b.l_node AS L_NODE,
b.c_deal_flag AS C_DEAL_FLAG,
b.c_stop_profit AS C_STOP_PROFIT,
b.vc_timestamp AS VC_TIMESTAMP,
b.vc_investor_system_id AS VC_INVESTOR_SYSTEM_ID,
b.vc_ip_address AS VC_IP_ADDRESS,
b.vc_mac_address AS VC_MAC_ADDRESS,
b.vc_imei AS VC_IMEI,
b.vc_uuid AS VC_UUID,
b.c_voucher_flag AS C_VOUCHER_FLAG,
b.vc_hedge_trade_acco AS VC_HEDGE_TRADE_ACCO,
b.vc_hedge_ta_code AS VC_HEDGE_TA_CODE,
b.vc_freeze_share_request_no AS VC_FREEZE_SHARE_REQUEST_NO,
b.vc_fix_busin_flag_list AS VC_FIX_BUSIN_FLAG_LIST,
b.vc_pay_grant_org AS VC_PAY_GRANT_ORG,
b.vc_strategy_type AS VC_STRATEGY_TYPE,
b.c_ia_notify_flag AS C_IA_NOTIFY_FLAG,
b.c_sum_deduct_flag AS C_SUM_DEDUCT_FLAG,
b.vc_specify_request_date AS VC_SPECIFY_REQUEST_DATE,
b.vc_ins_busin_flag AS VC_INS_BUSIN_FLAG,
b.vc_json_content AS VC_JSON_CONTENT,
b.c_share_expire_redeem_mode AS C_SHARE_EXPIRE_REDEEM_MODE,
b.vc_plan_no AS VC_PLAN_NO,
b.VC_REMIT_DEAD_DATE AS VC_REMIT_DEAD_DATE,
b.VC_REMIT_DEAD_TIME AS VC_REMIT_DEAD_TIME
FROM
tc_threquestdetail b ) A2 ON A1.VC_REQUEST_NO = A2.VC_REQUEST_NO
INNER JOIN TC_TACCOINFO B ON A1.VC_TENANT_ID = B.VC_TENANT_ID AND A1.VC_TRADE_ACCO = B.VC_TRADE_ACCO
INNER JOIN TC_TACCOBANK C ON A1.VC_TENANT_ID = C.VC_TENANT_ID AND B.VC_BANK_CARD_NO = C.VC_BANK_CARD_NO
INNER JOIN TC_TPRODUCTINFO D1 ON A1.VC_TENANT_ID = D1.VC_TENANT_ID AND A1.VC_PRODUCT_CODE = D1.VC_PRODUCT_CODE
INNER JOIN TC_TPRODUCTINFODETAIL D2 ON A1.VC_TENANT_ID = D2.VC_TENANT_ID AND D1.VC_PRODUCT_CODE = D2.VC_PRODUCT_CODE
INNER JOIN TC_TPRODUCTCFG CFG ON A1.VC_TENANT_ID = CFG.VC_TENANT_ID AND A1.VC_PRODUCT_CODE = CFG.VC_PRODUCT_CODE
LEFT JOIN tc_tfundcurrent F ON A1.vc_tenant_id = F.vc_tenant_id AND A1.vc_request_no = F.vc_request_no AND F.c_cap_busin_type = '850' AND F.c_fix_busin_flag IN ('04', '06', '08', '12', '13', '22')
LEFT JOIN TC_TCUSTAPPROPRIATENESSINFO cap ON a1.VC_CUST_NO = cap.VC_CUST_NO AND a1.VC_TENANT_ID = cap.VC_TENANT_ID
LEFT JOIN tc_tcustriskinfo tk ON a1.vc_tenant_id = tk.vc_tenant_id AND a1.vc_cust_no = tk.vc_cust_no AND (CASE WHEN cap.C_PUB_PROFESSION_FLAG = '1' THEN '3' ELSE '0' END) = tk.c_question_kind
INNER JOIN TC_TCUSTINFO cust ON A1.VC_TENANT_ID = cust.VC_TENANT_ID AND A1.VC_CUST_NO = cust.VC_CUST_NO
INNER JOIN TC_TCUSTINFODETAIL cde ON A1.VC_TENANT_ID = cde.VC_TENANT_ID AND A1.VC_CUST_NO = cde.VC_CUST_NO
INNER JOIN TC_TTAINFO ta ON A1.VC_TENANT_ID = ta.VC_TENANT_ID AND d1.vc_ta_code = ta.vc_ta_code
WHERE
A1.VC_TENANT_ID = '10000'
AND A1.VC_CUST_NO = '004098'
AND A2.VC_CUST_NO = '004098'
AND cap.VC_CUST_NO = '004098'
UNION ALL
SELECT
E.vc_trade_acco AS tradeAcco,
E.vc_fund_acco AS productAcco,
E.vc_product_code AS productCode,
E.c_busin_flag AS businFlag,
E.vc_request_no AS requestNo,
E.vc_request_date AS requestDate,
E.vc_net_no AS netNo,
E.vc_ta_code AS taCode,
E.c_share_type AS shareType,
E.vc_confirm_date AS confirmDate,
E.c_money_type AS moneyType,
E.en_confirm_share AS confirmShare,
E.en_confirm_bala AS confirmBala,
E.en_nav AS enNav,
E.en_fare AS fare,
E.en_agency_fare AS agencyFare,
E.en_stamp_tax AS stampTax,
E.vc_other_product_code AS otherProductCode,
E.c_other_share_type AS otherShareType,
E.vc_other_acco AS otherAcco,
E.vc_other_agency_no AS otherAgencyNo,
E.vc_other_net_no AS otherNetNo,
E.vc_error_code AS errorCode,
E.vc_error_cause AS errorCause,
E.c_frozen_cause AS frozenCause,
E.vc_origin_no AS originNo,
E.en_income AS income,
E.en_invest_income AS investIncome,
E.en_invest_share AS investShare,
'' AS originDate,
E.en_after_fare AS afterFare,
E.en_origin_fare AS originFare,
E.c_confirm_flag AS confirmFlag,
E.en_deal_fare AS dealFare,
E.c_confirm_status AS confirmStatus,
E.en_ta_fare AS taFare,
E.c_capital_mode AS capitalMode,
E.c_fix_busin_flag AS fixBusinFlag,
E.c_trade_busin_type AS tradeBusinType,
E.c_exp_type AS expType,
E.l_section_schema_no AS sectionSchemaNo,
E.en_balance AS balance,
E.en_share AS enshare,
'' AS subAccoAuditn,
E.vc_comb_request_no AS combRequestNo,
E.vc_comb_code AS combCode,
E.c_bank_no AS bankNo,
C.vc_bank_acco AS bankAcco,
E.c_sub_capital_mode AS subCapitalMode,
'' AS tradeContact,
E.vc_broker AS broker,
'' AS redeemUseFlag,
E.en_interest AS interest,
E.c_finish_flag AS finishFlag,
'' AS acceptDate,
'' AS acceptTime,
'' AS subAccoNo,
E.vc_come_from AS comeFrom,
E.vc_cust_no AS custNo,
E.c_dividend_method AS dividendMethod,
'' AS custcomFirm,
'' AS batchRequestNo,
'' AS batchNo,
'' AS otherSerialNo,
E.VC_CONFIRM_SETTLE_DATE AS comfirmSettleDate,
D1.C_PRODUCT_CATEGORY AS productCategory,
E.c_trust AS trustFlag,
E.VC_VOUCHER_NO AS voucher_no,
'' AS protocol,
'0' AS redeemReqFlag,
'0' AS allotReqFlag,
'' AS needDate,
D1.C_PRODUCT_SUB_TYPE AS productSubType,
E.VC_CONFIRM_NO AS confirmNo,
E.C_BUSIN_FLAG AS confirmBusinFlag,
B.C_USER_TYPE AS userType,
CFG.VC_MAIN_PRODUCT_CODE AS mainProductCode,
CFG.C_SIDE_BAG_FLAG AS sideBagFlag,
CFG.VC_SIDE_BAG_START_DATE AS sideBagStartDate,
cust.VC_IDENTITY_NO AS identityNo,
cust.C_IDENTITY_TYPE AS identityType,
cde.C_CUST_KIND AS custKind,
E.vc_bank_name AS bankName,
E.en_discount AS discount,
'' AS hopeDate,
'' AS remitDeadDate,
'' AS remitDeadTime,
cust.C_CUST_TYPE AS custType,
CUST.vc_custom_name AS customName,
D1.C_PRODUCT_TYPE AS productType,
D1.VC_PRODUCT_NAME AS productName,
NULL AS trust,
NULL AS promotion,
cde.VC_PHONE AS phone,
cde.VC_ADDRESS AS address,
cde.VC_ZIP AS zip,
cde.VC_MOBILE_NO AS mobileNo,
cde.VC_FAX_NO AS faxNo,
cde.VC_EMAIL AS email,
cap.C_PUB_PROFESSION_FLAG AS pubProfessionFlag,
cap.C_PUB_PROFESSION_SUB_TYPE AS pubProfessionSubType,
cap.C_PUB_HG_FLAG AS pubHgFlag,
cap.VC_PUB_PROFESSION_VALID_DATE AS pubProfessionValidDate,
D2.C_PRODUCT_RISK_LEVEL AS productRiskLevel,
tk.c_cust_risk_type AS custRiskType,
e.EN_OTHER_FEE1 AS otherFee1,
e.EN_OTHER_FEE2 AS otherFee2,
ta.vc_ta_name AS taName,
e.vc_other_trade_acco AS otherTradeAcco
FROM
LC_TCONFIRM E
INNER JOIN TC_TCUSTAPPROPRIATENESSINFO cap ON e.VC_CUST_NO = cap.VC_CUST_NO AND e.VC_TENANT_ID = cap.VC_TENANT_ID
INNER JOIN tc_tcustriskinfo tk ON e.vc_tenant_id = tk.vc_tenant_id AND e.vc_cust_no = tk.vc_cust_no AND (CASE WHEN cap.C_PUB_PROFESSION_FLAG = '1' THEN '3' ELSE'0' END) = tk.c_question_kind
INNER JOIN TC_TACCOINFO B ON E.VC_TENANT_ID = B.VC_TENANT_ID AND E.VC_TRADE_ACCO = B.VC_TRADE_ACCO
INNER JOIN TC_TACCOBANK C ON E.VC_TENANT_ID = C.VC_TENANT_ID AND B.VC_BANK_CARD_NO = C.VC_BANK_CARD_NO
INNER JOIN TC_TPRODUCTINFO D1 ON E.VC_TENANT_ID = D1.VC_TENANT_ID AND E.VC_PRODUCT_CODE = D1.VC_PRODUCT_CODE
INNER JOIN TC_TPRODUCTINFODETAIL D2 ON E.VC_TENANT_ID = D2.VC_TENANT_ID AND D1.VC_PRODUCT_CODE = D2.VC_PRODUCT_CODE
INNER JOIN TC_TPRODUCTCFG CFG ON E.VC_TENANT_ID = CFG.VC_TENANT_ID AND E.VC_PRODUCT_CODE = CFG.VC_PRODUCT_CODE
INNER JOIN TC_TCUSTINFO cust ON E.VC_TENANT_ID = cust.VC_TENANT_ID AND E.VC_CUST_NO = cust.VC_CUST_NO
INNER JOIN TC_TCUSTINFODETAIL cde ON E.VC_TENANT_ID = cde.VC_TENANT_ID AND E.VC_CUST_NO = cde.VC_CUST_NO
INNER JOIN TC_TTAINFO ta ON E.VC_TENANT_ID = ta.VC_TENANT_ID AND D1.vc_ta_code = ta.vc_ta_code
WHERE
E.c_busin_flag = '142'
AND E.VC_TENANT_ID = '10000'
AND E.VC_CUST_NO = '004098'
AND cap.VC_CUST_NO = '004098'
AND B.VC_CUST_NO = '004098'
AND CUST.VC_CUST_NO = '004098'
AND CDE.VC_CUST_NO = '004098'
) AT
ORDER BY
AT.requestNo DESC
LIMIT 10;
结论
改写，逻辑left join和 where a,b where a.id = b.id 混合用，统一改写成left join和inner join，有点费脑细胞
SQL等价，数据一致，没毛病
参考：
http://www.light-pg.com/docs/lightdb-cn/current/explicit-joins.html
else if (list_length(leftjoinlist) + list_length(rightjoinlist) <=
join_collapse_limit)
{
/* OK to combine subproblems */
joinlist = list_concat(leftjoinlist, rightjoinlist);
}
else
{
/* can't combine, but needn't force join order above here */
Node *leftpart,
*rightpart;
/* avoid creating useless 1-element sublists */
if (list_length(leftjoinlist) == 1)
leftpart = (Node *) linitial(leftjoinlist);
else
leftpart = (Node *) leftjoinlist;
if (list_length(rightjoinlist) == 1)
rightpart = (Node *) linitial(rightjoinlist);
else
rightpart = (Node *) rightjoinlist;
joinlist = list_make2(leftpart, rightpart);
}
最后修改时间：2023-09-28 13:25:30
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
