select *
from (select AREA_NO,
SECOND_LEVEL_NAME,
DEALER_ID,
CHANNEL_NAME,
KHJL_NAME,
NVL(SUM(DEV_USER_JR), 0) DEV_USER_JR,
case
when SUM(DEV_USER_TOTAL_JR) = 0 then
'--'
else
to_char(SUM(DEV_USER_TOTAL_JR))
end DEV_USER_TOTAL_JR,
to_char(decode(SUM(DEV_USER_TOTAL_JR_LM),
0,
0,
(SUM(DEV_USER_TOTAL_JR) -
SUM(DEV_USER_TOTAL_JR_LM)) /
SUM(DEV_USER_TOTAL_JR_LM)) * 100,
'FM999,999,999,999,990.00') || '%' HB_JR,
NVL(SUM(DEV_USER_JR_LX), 0) DEV_USER_JR_LX,
case
when SUM(DEV_USER_TOTAL_JR_LX) = 0 then
'--'
else
to_char(SUM(DEV_USER_TOTAL_JR_LX))
end DEV_USER_TOTAL_JR_LX,
to_char(decode(SUM(DEV_USER_TOTAL_JR_LX_LM),
0,
0,
(SUM(DEV_USER_TOTAL_JR_LX) -
SUM(DEV_USER_TOTAL_JR_LX_LM)) /
SUM(DEV_USER_TOTAL_JR_LX_LM)) * 100,
'FM999,999,999,999,990.00') || '%' HB_JR_LX,
NVL(SUM(DEV_USER_WX), 0) DEV_USER_WX,
case
when SUM(DEV_USER_TOTAL_WX) = 0 then
'--'
else
to_char(SUM(DEV_USER_TOTAL_WX))
end DEV_USER_TOTAL_WX,
to_char(decode(SUM(DEV_USER_TOTAL_WX_LM),
0,
0,
(SUM(DEV_USER_TOTAL_WX) -
SUM(DEV_USER_TOTAL_WX_LM)) /
SUM(DEV_USER_TOTAL_WX_LM)) * 100,
'FM999,999,999,999,990.00') || '%' HB_WX,
NVL(SUM(DEV_USER_WX_LX), 0) DEV_USER_WX_LX,
case
when SUM(DEV_USER_TOTAL_WX_LX) = 0 then
'--'
else
to_char(SUM(DEV_USER_TOTAL_WX_LX))
end DEV_USER_TOTAL_WX_LX,
to_char(decode(SUM(DEV_USER_TOTAL_WX_LX_LM),
0,
0,
(SUM(DEV_USER_TOTAL_WX_LX) -
SUM(DEV_USER_TOTAL_WX_LX_LM)) /
SUM(DEV_USER_TOTAL_WX_LX_LM)) * 100,
'FM999,999,999,999,990.00') || '%' HB_WX_LX,
to_char(decode(SUM(DEV_USER_JR_LX_LM),
0,
0,
(SUM(DEV_USER_JR_LX) - SUM(DEV_USER_JR_LX_LM)) /
SUM(DEV_USER_JR_LX_LM)) * 100,
'FM999,999,999,999,990.00') || '%' RHB_JR_LX,
to_char(decode(SUM(DEV_USER_WX_LX_LM),
0,
0,
(SUM(DEV_USER_WX_LX) - SUM(DEV_USER_WX_LX_LM)) /
SUM(DEV_USER_WX_LX_LM)) * 100,
'FM999,999,999,999,990.00') || '%' RHB_WX_LX
from (
select *
from (
select AREA_NO,
DEALER_ID,
user_name KHJL_NAME,
ORG_ID,
SUM(DEV_USER_JR) DEV_USER_JR,
SUM(DEV_USER_JR_TOTAL) DEV_USER_TOTAL_JR,
0 DEV_USER_TOTAL_JR_LM,
0 DEV_USER_JR_LX,
case
when PRODUCT_TYPE = '2' then
NVL(SUM(DEV_USER_TOTAL_LX), 0)
else
0
end DEV_USER_TOTAL_JR_LX,
0 DEV_USER_JR_LX_LM,
0 DEV_USER_TOTAL_JR_LX_LM,
SUM(DEV_USER_WX) DEV_USER_WX,
SUM(DEV_USER_WX_TOTAL) DEV_USER_TOTAL_WX,
0 DEV_USER_TOTAL_WX_LM,
0 DEV_USER_WX_LX,
case
when PRODUCT_TYPE = '1' then
NVL(SUM(DEV_USER_TOTAL_LX), 0)
else
0
end DEV_USER_TOTAL_WX_LX,
0 DEV_USER_WX_LX_LM,
0 DEV_USER_TOTAL_WX_LX_LM
from kpi.KPI_D_SJCX_RDG_STATS_ORG_REP_QD a,
--from kpi.KPI_D_SJCX_RDG_STATS_ORG_REP_QD a
--(select * from kpi.DMCODE_CALC_OBJ where DAY_ID=#{acctDate,jdbcType=VARCHAR}) b
(select *
from kpi.DMCODE_CALC_OBJ
where DAY_ID = '20221023') b
--left join (select * from kpi.DMCODE_CALC_OBJ where DAY_ID='20221023') b
where a.dealer_id = b.data_area(+)
--ON a.dealer_id = b.data_area
-- and a.DAY_ID = #{acctDate,jdbcType=VARCHAR}
and a.DAY_ID = '20221023'
/*<if test="channel!=null">
<if test='channel!="-1"'>
and CHANNEL_DESC= #{channel,jdbcType=VARCHAR}
</if>
</if>*/
group by AREA_NO,
PRODUCT_TYPE,
DEALER_ID,
user_name,
ORG_ID)
where DEALER_ID = '84a0241'
and ORG_ID = '00610410013'
union all
select AREA_NO,
DEALER_ID,
user_name KHJL_NAME,
ORG_ID,
0 DEV_USER_JR,
0 DEV_USER_TOTAL_JR,
0 DEV_USER_TOTAL_JR_LM,
case
when PRODUCT_TYPE = '2' then
NVL(SUM(DEV_USER), 0)
else
0
end DEV_USER_JR_LX,
0 DEV_USER_TOTAL_JR_LX,
0 DEV_USER_JR_LX_LM,
0 DEV_USER_TOTAL_JR_LX_LM,
0 DEV_USER_WX,
0 DEV_USER_TOTAL_WX,
0 DEV_USER_TOTAL_WX_LM,
case
when PRODUCT_TYPE = '1' then
NVL(SUM(DEV_USER), 0)
else
0
end DEV_USER_WX_LX,
0 DEV_USER_TOTAL_WX_LX,
0 DEV_USER_WX_LX_LM,
0 DEV_USER_TOTAL_WX_LX_LM
-- from kpi.KPI_D_SJCX_RDG_STATS_ORG_REP_QD a,(select * from kpi.DMCODE_CALC_OBJ where DAY_ID=#{acctDate,jdbcType=VARCHAR}) b
from kpi.KPI_D_SJCX_RDG_STATS_ORG_REP_QD a,
--from kpi.KPI_D_SJCX_RDG_STATS_ORG_REP_QD a
(select *
from kpi.DMCODE_CALC_OBJ
where DAY_ID = '20221023') b
/* left join (select *
from kpi.DMCODE_CALC_OBJ
where DAY_ID = '20221023') b*/
where a.dealer_id = b.data_area(+)
--ON a.dealer_id = b.data_area
--and a.DAY_ID = #{acctDate,jdbcType=VARCHAR}
and a.DAY_ID = '20221023'
and DEV_USER != 0
/*<if test="channel!=null">
<if test='channel!="-1"'>
and CHANNEL_DESC= #{channel,jdbcType=VARCHAR}
</if>
</if>*/
group by AREA_NO, PRODUCT_TYPE, DEALER_ID, user_name, ORG_ID
union all
select AREA_NO,
DEALER_ID,
user_name KHJL_NAME,
ORG_ID,
0 DEV_USER_JR,
0 DEV_USER_TOTAL_JR,
NVL(SUM(DEV_USER_JR_TOTAL), 0) DEV_USER_TOTAL_JR_LM,
0 DEV_USER_JR_LX,
0 DEV_USER_TOTAL_JR_LX,
0 DEV_USER_JR_LX_LM,
case
when PRODUCT_TYPE = '2' then
NVL(SUM(DEV_USER_TOTAL_LX), 0)
else
0
end DEV_USER_TOTAL_JR_LX_LM,
0 DEV_USER_WX,
0 DEV_USER_TOTAL_WX,
NVL(SUM(DEV_USER_WX_TOTAL), 0) DEV_USER_TOTAL_WX_LM,
0 DEV_USER_WX_LX,
0 DEV_USER_TOTAL_WX_LX,
0 DEV_USER_WX_LX_LM,
case
when PRODUCT_TYPE = '1' then
NVL(SUM(DEV_USER_TOTAL_LX), 0)
else
0
end DEV_USER_TOTAL_WX_LX_LM
--from kpi.KPI_D_SJCX_RDG_STATS_ORG_REP_QD a,(select * from kpi.DMCODE_CALC_OBJ where DAY_ID=#{acctDate,jdbcType=VARCHAR}) b
--from kpi.KPI_D_SJCX_RDG_STATS_ORG_REP_QD a,
from kpi.KPI_D_SJCX_RDG_STATS_ORG_REP_QD a
/*(select *
from kpi.DMCODE_CALC_OBJ
where DAY_ID = '20221023') b
where a.dealer_id = b.data_area(+)*/
left join (select *
from kpi.DMCODE_CALC_OBJ
where DAY_ID = '20221023') b
--where a.dealer_id = b.data_area
ON a.dealer_id = b.data_area
--and a.DAY_ID =to_char(add_months(trunc(to_date(#{acctDate,jdbcType=VARCHAR},'yyyymmdd')),-1),'yyyymmdd')
and a.DAY_ID =
to_char(add_months(trunc(to_date('20221023', 'yyyymmdd')),
-1),
'yyyymmdd')
/*<if test="channel!=null">
<if test='channel!="-1"'>
and CHANNEL_DESC= #{channel,jdbcType=VARCHAR}
</if>
</if>*/
group by AREA_NO, PRODUCT_TYPE, DEALER_ID, user_name, ORG_ID
union all
select AREA_NO,
DEALER_ID,
user_name KHJL_NAME,
ORG_ID,
0 DEV_USER_JR,
0 DEV_USER_TOTAL_JR,
0 DEV_USER_TOTAL_JR_LM,
0 DEV_USER_JR_LX,
0 DEV_USER_TOTAL_JR_LX,
case
when PRODUCT_TYPE = '2' then
NVL(SUM(DEV_USER), 0)
else
0
end DEV_USER_JR_LX_LM,
0 DEV_USER_TOTAL_JR_LX_LM,
0 DEV_USER_WX,
0 DEV_USER_TOTAL_WX,
0 DEV_USER_TOTAL_WX_LM,
0 DEV_USER_WX_LX,
0 DEV_USER_TOTAL_WX_LX,
case
when PRODUCT_TYPE = '1' then
NVL(SUM(DEV_USER), 0)
else
0
end DEV_USER_WX_LX_LM,
0 DEV_USER_TOTAL_WX_LX_LM
--from kpi.KPI_D_SJCX_RDG_STATS_ORG_REP_QD a,(select * from kpi.DMCODE_CALC_OBJ where DAY_ID=#{acctDate,jdbcType=VARCHAR}) b
--from kpi.KPI_D_SJCX_RDG_STATS_ORG_REP_QD a,
from kpi.KPI_D_SJCX_RDG_STATS_ORG_REP_QD a
/*(select *
from kpi.DMCODE_CALC_OBJ
where DAY_ID = '20221023') b*/
left join (select *
from kpi.DMCODE_CALC_OBJ
where DAY_ID = '20221023') b
--where a.dealer_id = b.data_area(+)
ON a.dealer_id = b.data_area
--and a.DAY_ID =to_char(TRUNC(to_date(#{acctDate,jdbcType=VARCHAR},'yyyymmdd')-1),'yyyymmdd')
and a.DAY_ID = '20221023'
and DEV_USER != 0
/*<if test="channel!=null">
<if test='channel!="-1"'>
and CHANNEL_DESC= #{channel,jdbcType=VARCHAR}
</if>
</if>*/
group by AREA_NO, PRODUCT_TYPE, DEALER_ID, user_name, ORG_ID) a,
kpicode.Ods_Cb_Organ_Grid_Level_Second b,
--kpicode.Ods_Cb_Organ_Grid_Level_Second b
(SELECT CHANNEL_ID, CHANNEL_NAME
FROM KPICODE.ODS_CB_ORGAN_GRID_LEVEL_EIGHT
WHERE CHANNEL_TYPE = '1'
UNION
SELECT CHNL_CODE, CHANNEL_NAME
FROM KPICODE.ODS_CB_ORGAN_GRID_LEVEL_EIGHT
WHERE CHANNEL_TYPE = '1') D
/* left join (SELECT CHANNEL_ID, CHANNEL_NAME
FROM KPICODE.ODS_CB_ORGAN_GRID_LEVEL_EIGHT
WHERE CHANNEL_TYPE = '1'
UNION
SELECT CHNL_CODE, CHANNEL_NAME
FROM KPICODE.ODS_CB_ORGAN_GRID_LEVEL_EIGHT
WHERE CHANNEL_TYPE = '1') D*/
where a.AREA_NO = b.second_level
--where a.AREA_NO = b.second_level
and a.DEALER_ID = d.CHANNEL_ID(+)
--ON a.DEALER_ID = d.CHANNEL_ID
/*<if test="orgId!=null">
<if test="orgId!=-1">
and AREA_NO= #{orgId,jdbcType=VARCHAR}
</if>
</if>
<if test="wgId!=null">
<if test="wgId!=-1">
and ORG_ID= #{wgId,jdbcType=VARCHAR}
</if>
</if>*/
group by AREA_NO,
SECOND_LEVEL_NAME,
b.remaek1,
DEALER_ID,
CHANNEL_NAME,
KHJL_NAME
order by b.remaek1)
union all
select '084' AREA_NO,
'ȫʡ' SECOND_LEVEL_NAME,
'--' DEALER_ID,
'--' CHANNEL_NAME,
'--' KHJL_NAME,
NVL(SUM(DEV_USER_JR), 0) DEV_USER_JR,
case
when SUM(DEV_USER_TOTAL_JR) = 0 then
'--'
else
to_char(SUM(DEV_USER_TOTAL_JR))
end DEV_USER_TOTAL_JR,
to_char(decode(SUM(DEV_USER_TOTAL_JR_LM),
0,
0,
(SUM(DEV_USER_TOTAL_JR) - SUM(DEV_USER_TOTAL_JR_LM)) /
SUM(DEV_USER_TOTAL_JR_LM)) * 100,
'FM999,999,999,999,990.00') || '%' HB_JR,
NVL(SUM(DEV_USER_JR_LX), 0) DEV_USER_JR_LX,
case
when SUM(DEV_USER_TOTAL_JR_LX) = 0 then
'--'
else
to_char(SUM(DEV_USER_TOTAL_JR_LX))
end DEV_USER_TOTAL_JR_LX,
to_char(decode(SUM(DEV_USER_TOTAL_JR_LX_LM),
0,
0,
(SUM(DEV_USER_TOTAL_JR_LX) -
SUM(DEV_USER_TOTAL_JR_LX_LM)) /
SUM(DEV_USER_TOTAL_JR_LX_LM)) * 100,
'FM999,999,999,999,990.00') || '%' HB_JR_LX,
NVL(SUM(DEV_USER_WX), 0) DEV_USER_WX,
case
when SUM(DEV_USER_TOTAL_WX) = 0 then
'--'
else
to_char(SUM(DEV_USER_TOTAL_WX))
end DEV_USER_TOTAL_WX,
to_char(decode(SUM(DEV_USER_TOTAL_WX_LM),
0,
0,
(SUM(DEV_USER_TOTAL_WX) - SUM(DEV_USER_TOTAL_WX_LM)) /
SUM(DEV_USER_TOTAL_WX_LM)) * 100,
'FM999,999,999,999,990.00') || '%' HB_WX,
NVL(SUM(DEV_USER_WX_LX), 0) DEV_USER_WX_LX,
case
when SUM(DEV_USER_TOTAL_WX_LX) = 0 then
'--'
else
to_char(SUM(DEV_USER_TOTAL_WX_LX))
end DEV_USER_TOTAL_WX_LX,
to_char(decode(SUM(DEV_USER_TOTAL_WX_LX_LM),
0,
0,
(SUM(DEV_USER_TOTAL_WX_LX) -
SUM(DEV_USER_TOTAL_WX_LX_LM)) /
SUM(DEV_USER_TOTAL_WX_LX_LM)) * 100,
'FM999,999,999,999,990.00') || '%' HB_WX_LX,
to_char(decode(SUM(DEV_USER_JR_LX_LM),
0,
0,
(SUM(DEV_USER_JR_LX) - SUM(DEV_USER_JR_LX_LM)) /
SUM(DEV_USER_JR_LX_LM)) * 100,
'FM999,999,999,999,990.00') || '%' RHB_JR_LX,
to_char(decode(SUM(DEV_USER_WX_LX_LM),
0,
0,
(SUM(DEV_USER_WX_LX) - SUM(DEV_USER_WX_LX_LM)) /
SUM(DEV_USER_WX_LX_LM)) * 100,
'FM999,999,999,999,990.00') || '%' RHB_WX_LX
from (select '084' AREA_NO,
SUM(DEV_USER_JR) DEV_USER_JR,
SUM(DEV_USER_JR_TOTAL) DEV_USER_TOTAL_JR,
0 DEV_USER_TOTAL_JR_LM,
0 DEV_USER_JR_LX,
case
when PRODUCT_TYPE = '2' then
NVL(SUM(DEV_USER_TOTAL_LX), 0)
else
0
end DEV_USER_TOTAL_JR_LX,
0 DEV_USER_JR_LX_LM,
0 DEV_USER_TOTAL_JR_LX_LM,
SUM(DEV_USER_WX) DEV_USER_WX,
SUM(DEV_USER_WX_TOTAL) DEV_USER_TOTAL_WX,
0 DEV_USER_TOTAL_WX_LM,
0 DEV_USER_WX_LX,
case
when PRODUCT_TYPE = '1' then
NVL(SUM(DEV_USER_TOTAL_LX), 0)
else
0
end DEV_USER_TOTAL_WX_LX,
0 DEV_USER_WX_LX_LM,
0 DEV_USER_TOTAL_WX_LX_LM
--from kpi.KPI_D_SJCX_RDG_STATS_ORG_REP_QD a,(select * from kpi.DMCODE_CALC_OBJ where DAY_ID=#{acctDate,jdbcType=VARCHAR}) b
--from kpi.KPI_D_SJCX_RDG_STATS_ORG_REP_QD a,
from kpi.KPI_D_SJCX_RDG_STATS_ORG_REP_QD a
-- (select * from kpi.DMCODE_CALC_OBJ where DAY_ID = '20221023') b
left join (select * from kpi.DMCODE_CALC_OBJ where DAY_ID = '20221023') b
--where a.dealer_id = b.data_area(+)
ON a.dealer_id = b.data_area
--and a.DAY_ID = #{acctDate,jdbcType=VARCHAR}
and a.DAY_ID = '2022102 3'
/*<if test="channel!=null">
<if test='channel!="-1"'>
and CHANNEL_DESC= #{channel,jdbcType=VARCHAR}
</if>
</if>
<if test="orgId!=null">
<if test="orgId!=-1">
and AREA_NO= #{orgId,jdbcType=VARCHAR}
</if>
</if>
<if test="wgId!=null">
<if test="wgId!=-1">
and ORG_ID= #{wgId,jdbcType=VARCHAR}
</if>
</if>*/
group by PRODUCT_TYPE
union all
select '084' AREA_NO,
0 DEV_USER_JR,
0 DEV_USER_TOTAL_JR,
0 DEV_USER_TOTAL_JR_LM,
case
when PRODUCT_TYPE = '2' then
NVL(SUM(DEV_USER), 0)
else
0
end DEV_USER_JR_LX,
0 DEV_USER_TOTAL_JR_LX,
0 DEV_USER_JR_LX_LM,
0 DEV_USER_TOTAL_JR_LX_LM,
0 DEV_USER_WX,
0 DEV_USER_TOTAL_WX,
0 DEV_USER_TOTAL_WX_LM,
case
when PRODUCT_TYPE = '1' then
NVL(SUM(DEV_USER), 0)
else
0
end DEV_USER_WX_LX,
0 DEV_USER_TOTAL_WX_LX,
0 DEV_USER_WX_LX_LM,
0 DEV_USER_TOTAL_WX_LX_LM
--from kpi.KPI_D_SJCX_RDG_STATS_ORG_REP_QD a,(select * from kpi.DMCODE_CALC_OBJ where DAY_ID=#{acctDate,jdbcType=VARCHAR}) b
--from kpi.KPI_D_SJCX_RDG_STATS_ORG_REP_QD a,
from kpi.KPI_D_SJCX_RDG_STATS_ORG_REP_QD a
-- (select * from kpi.DMCODE_CALC_OBJ where DAY_ID = '20221023') b
left join (select * from kpi.DMCODE_CALC_OBJ where DAY_ID = '20221023') b
--where a.dealer_id = b.data_area(+)
ON a.dealer_id = b.data_area
--and a.DAY_ID = #{acctDate,jdbcType=VARCHAR}
and a.DAY_ID = '20221023'
and DEV_USER != 0
/*<if test="channel!=null">
<if test='channel!="-1"'>
and CHANNEL_DESC= #{channel,jdbcType=VARCHAR}
</if>
</if>
<if test="orgId!=null">
<if test="orgId!=-1">
and AREA_NO= #{orgId,jdbcType=VARCHAR}
</if>
</if>
<if test="wgId!=null">
<if test="wgId!=-1">
and ORG_ID= #{wgId,jdbcType=VARCHAR}
</if>
</if>*/
group by PRODUCT_TYPE
union all
select '084' AREA_NO,
0 DEV_USER_JR,
0 DEV_USER_TOTAL_JR,
SUM(DEV_USER_JR_TOTAL) DEV_USER_TOTAL_JR_LM,
0 DEV_USER_JR_LX,
0 DEV_USER_TOTAL_JR_LX,
0 DEV_USER_JR_LX_LM,
case
when PRODUCT_TYPE = '2' then
NVL(SUM(DEV_USER_TOTAL_LX), 0)
else
0
end DEV_USER_TOTAL_JR_LX_LM,
0 DEV_USER_WX,
0 DEV_USER_TOTAL_WX,
SUM(DEV_USER_WX_TOTAL) DEV_USER_TOTAL_WX_LM,
0 DEV_USER_WX_LX,
0 DEV_USER_TOTAL_WX_LX,
0 DEV_USER_WX_LX_LM,
case
when PRODUCT_TYPE = '1' then
NVL(SUM(DEV_USER_TOTAL_LX), 0)
else
0
end DEV_USER_TOTAL_WX_LX_LM
--from kpi.KPI_D_SJCX_RDG_STATS_ORG_REP_QD a,(select * from kpi.DMCODE_CALC_OBJ where DAY_ID=#{acctDate,jdbcType=VARCHAR}) b
--from kpi.KPI_D_SJCX_RDG_STATS_ORG_REP_QD a,
from kpi.KPI_D_SJCX_RDG_STATS_ORG_REP_QD a
-- (select * from kpi.DMCODE_CALC_OBJ where DAY_ID = '20221023') b
left join (select * from kpi.DMCODE_CALC_OBJ where DAY_ID = '20221023') b
--where a.dealer_id = b.data_area(+)
ON a.dealer_id = b.data_area
--and a.DAY_ID = to_char(add_months(trunc(to_date(#{acctDate,jdbcType=VARCHAR},'yyyymmdd')),-1),'yyyymmdd')
and a.DAY_ID = '20221023'
/*<if test="channel!=null">
<if test='channel!="-1"'>
and CHANNEL_DESC= #{channel,jdbcType=VARCHAR}
</if>
</if>
<if test="orgId!=null">
<if test="orgId!=-1">
and AREA_NO= #{orgId,jdbcType=VARCHAR}
</if>
</if>
<if test="wgId!=null">
<if test="wgId!=-1">
and ORG_ID= #{wgId,jdbcType=VARCHAR}
</if>
</if>*/
group by PRODUCT_TYPE
union all
select '084' AREA_NO,
0 DEV_USER_JR,
0 DEV_USER_TOTAL_JR,
0 DEV_USER_TOTAL_JR_LM,
0 DEV_USER_JR_LX,
0 DEV_USER_TOTAL_JR_LX,
case
when PRODUCT_TYPE = '2' then
NVL(SUM(DEV_USER), 0)
else
0
end DEV_USER_JR_LX_LM,
0 DEV_USER_TOTAL_JR_LX_LM,
0 DEV_USER_WX,
0 DEV_USER_TOTAL_WX,
0 DEV_USER_TOTAL_WX_LM,
0 DEV_USER_WX_LX,
0 DEV_USER_TOTAL_WX_LX,
case
when PRODUCT_TYPE = '1' then
NVL(SUM(DEV_USER), 0)
else
0
end DEV_USER_WX_LX_LM,
0 DEV_USER_TOTAL_WX_LX_LM
--from kpi.KPI_D_SJCX_RDG_STATS_ORG_REP_QD a,(select * from kpi.DMCODE_CALC_OBJ where DAY_ID=#{acctDate,jdbcType=VARCHAR}) b
--from kpi.KPI_D_SJCX_RDG_STATS_ORG_REP_QD a,
from kpi.KPI_D_SJCX_RDG_STATS_ORG_REP_QD a
--(select * from kpi.DMCODE_CALC_OBJ where DAY_ID = '20221023') b
left join (select * from kpi.DMCODE_CALC_OBJ where DAY_ID = '20221023') b
-- where a.dealer_id = b.data_area(+)
ON a.dealer_id = b.data_area
--and a.DAY_ID =to_char(TRUNC(to_date(#{acctDate,jdbcType=VARCHAR},'yyyymmdd')-1),'yyyymmdd')
and a.DAY_ID = '20221023'
and DEV_USER != 0
/*<if test="channel!=null">
<if test='channel!="-1"'>
and CHANNEL_DESC= #{channel,jdbcType=VARCHAR}
</if>
</if>
<if test="orgId!=null">
<if test="orgId!=-1">
and AREA_NO= #{orgId,jdbcType=VARCHAR}
</if>
</if>
<if test="wgId!=null">
<if test="wgId!=-1">
and ORG_ID= #{wgId,jdbcType=VARCHAR}
</if>
</if>*/
group by PRODUCT_TYPE)
需求:在oracle数据库上执行的不是oracle数据库语法,所以只能改成标准sql,mysql的很多语法在plsql上识别不了。