<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: Calvin
    $Date:  2018年03月21日14:15:17
    $Revision: 1.0  
    $Purpose: 消费大类分布
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    NVL(
                    (SELECT
                        td.area_desc
                    FROM
                        T_SYS_DATA_DICTIONARY td
                    WHERE
                        td.area_code     = t1.insd AND
                        td.district_desc = '行业代码'
                    ), '其他') insd_n,
                    t1.*
                FROM
                    (SELECT
                        SUBSTR(regexp_substr(t.transamtpre, '[^;]+', 1, rownum), 1, instr(regexp_substr(t.transamtpre, '[^;]+', 1, rownum), '_', -1) - 1) insd,
                        SUBSTR(regexp_substr(t.transamtpre, '[^;]+', 1, rownum), instr(regexp_substr(t.transamtpre, '[^;]+', 1, rownum), '_', -1) + 1) transamtpre,
                        REPLACE(SUBSTR(regexp_substr(t.transamtpre, '[^;]+', 1, rownum), instr(regexp_substr(t.transamtpre, '[^;]+', 1, rownum), '_', -1) + 1), '%', '') transamtpre_n,
                        SUBSTR(regexp_substr(t.transcntpre, '[^;]+', 1, rownum), instr(regexp_substr(t.transcntpre, '[^;]+', 1, rownum), '_', -1) + 1) transcntpre,
                        REPLACE(SUBSTR(regexp_substr(t.transcntpre, '[^;]+', 1, rownum), instr(regexp_substr(t.transcntpre, '[^;]+', 1, rownum), '_', -1) + 1), '%', '') transcntpre_n
                    FROM
                        (SELECT
                            *
                        FROM
                            ccx_indexconsumecategories ci
                        WHERE
                            ci.chk_list_id     = ${@chk_list_id}
                        ) t CONNECT BY rownum <= LENGTHB(t.transamtpre)-LENGTHB(REPLACE(t.transamtpre,';','')) +1
                    ) t1
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
