<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: Calvin 
    $Date:  2018年03月21日14:16:46
    $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.area AND
                        td.district_desc = '区域代码'
                    ), '其他') area_n,
                    t1.*
                FROM
                    (SELECT
                        SUBSTR(regexp_substr(t.placestransamtpre, '[^;]+', 1, rownum), 1, instr(regexp_substr(t.placestransamtpre, '[^;]+', 1, rownum), '_', -1) - 1) area,
                        SUBSTR(regexp_substr(t.placestransamtpre, '[^;]+', 1, rownum), instr(regexp_substr(t.placestransamtpre, '[^;]+', 1, rownum), '_', -1) + 1) placestransamtpre,
                        REPLACE(SUBSTR(regexp_substr(t.placestransamtpre, '[^;]+', 1, rownum), instr(regexp_substr(t.placestransamtpre, '[^;]+', 1, rownum), '_', -1) + 1),'%','') placestransamtpre_n,
                        SUBSTR(regexp_substr(t.placestranscntpre, '[^;]+', 1, rownum), instr(regexp_substr(t.placestranscntpre, '[^;]+', 1, rownum), '_', -1) + 1) placestranscntpre,
                        REPLACE(SUBSTR(regexp_substr(t.placestranscntpre, '[^;]+', 1, rownum), instr(regexp_substr(t.placestranscntpre, '[^;]+', 1, rownum), '_', -1) + 1), '%', '') placestranscntpre_n
                    FROM
                        (SELECT
                            *
                        FROM
                            ccx_indexconsumecities ci
                        WHERE
                            ci.chk_list_id     = ${@chk_list_id}
                        ) t CONNECT BY rownum <=LENGTHB(t.placestransamtpre)-LENGTHB(REPLACE(t.placestransamtpre,';','')) +1
                    ) t1
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
