A.o rder_method, a.c us_type, 'as sales_type,' as sales_channel,
COUNT (case when Order_Amount & gt; 0 then a.t rans_no end) as trans_cnt,
SUM (is null (Amy polumbo eople_number, 0)) as people_number,
SUM (is null (a.o rder_amount, 0)) as order_amount,
SUM (is null (A.S ales_Amount, 0)) as sales_amount,
SUM (is null (a. d. iscount_amount, 0)) as discount_amount,
SUM (IS NULL (e.a mt, 0)) as amt,
SUM (IS NULL (e.a mt_vld, 0)) as amt_vld,
SUM (IS NULL (e.a mt_uvld, 0)) as amt_uvld,
SUM (IS NULL (e.a mt_vld, 0)) as amt_all
The from (
The select a. IZ_DATE, A.s tore_code, a. d. istrict_code,
A.o rder_method, a.c us_type, a.t rans_no,
Case when the SUM (is null (A.S ales_Amount, 0))=0, then 0 else SUM (is null (Amy polumbo eople_number, 0)) end as people_number,
SUM (is null (a.o rder_amount, 0)) as order_amount,
SUM (is null (A.S ales_Amount, 0)) as sales_amount,
SUM (is null (a. d. iscount_amount, 0)) as discount_amount
The FROM BI005 A
Where a. iz_date & gt;
='2019-08-27 00:00:00'And a. iz_date & lt;
='2019-08-21 00:00:00'Group by a. IZ_DATE, A.s tore_code, a. d. istrict_code,
A.o rder_method, a.c us_type, a.t rans_no) a
Left the join
(select b.t rans_no, b.s tore_code, b.b iz_date, b.d istrict_code,
SUM (is null (case when d.f lag='1' then b.a mount end, 0)) amt_vld,
SUM (is null (case when d.f lag='0' then b.a mount end, 0)) amt_uvld,
SUM (is null (b.a mount, 0)) amt
The from BI007 b
Left the join
(select * from dhbi_pccode_all) d
On b.s tore_code=d.s tore_code
And b.t ender_code=which ender_code
Where b.b iz_date & gt;
='2019-08-27 00:00:00'And b.b iz_date & lt;
='2019-08-21 00:00:00'Group by b.t rans_no, b.s tore_code, b.b iz_date, b.d istrict_code
) e
On a.t rans_no=e. rans_no
And a.s tore_code=e.s tore_code
And a. iz_date=e.b iz_date
And a. d. istrict_code=e.d istrict_code
GROUP BY a. IZ_DATE, A.s tore_code, a. d. istrict_code, a.o rder_method, a.c us_type
CodePudding user response:
SUM (is null (Amy polumbo eople_number, 0)) as people_number,Switch to
The SUM (NVL (Amy polumbo eople_number, 0)) as people_number,
CodePudding user response:
The SELECT a. IZ_DATE, 2 as db_id, A.s tore_code, a. d. istrict_code,A.o rder_method, a.c us_type, 'as sales_type,' as sales_channel,
COUNT (case when Order_Amount & gt; 0 then a.t rans_no end) as trans_cnt,
The SUM (NVL (Amy polumbo eople_number, 0)) as people_number,
The SUM (NVL (a.o rder_amount, 0)) as order_amount,
The SUM (NVL (A.S ales_Amount, 0)) as sales_amount,
The SUM (NVL (a. d. iscount_amount, 0)) as discount_amount,
The SUM (NVL (e.a mt, 0)) as amt,
The SUM (NVL (e.a mt_vld, 0)) as amt_vld,
The SUM (NVL (e.a mt_uvld, 0)) as amt_uvld,
The SUM (NVL (e.a mt_vld, 0)) as amt_all
The from (
The select a. IZ_DATE, A.s tore_code, a. d. istrict_code,
A.o rder_method, a.c us_type, a.t rans_no,
Case when the SUM (NVL (A.S ales_Amount, 0))=0, then 0 else SUM (NVL (Amy polumbo eople_number, 0)) end as people_number,
The SUM (NVL (a.o rder_amount, 0)) as order_amount,
The SUM (NVL (A.S ales_Amount, 0)) as sales_amount,
The SUM (NVL (a. d. iscount_amount, 0)) as discount_amount
The FROM BI005 A
Where a. iz_date & gt;
='2019-08-27 00:00:00'And a. iz_date & lt;
='2019-08-21 00:00:00'Group by a. IZ_DATE, A.s tore_code, a. d. istrict_code,
A.o rder_method, a.c us_type, a.t rans_no) a
Left the join
(select b.t rans_no, b.s tore_code, b.b iz_date, b.d istrict_code,
The SUM (NVL (case when d.f lag='1' then b.a mount end, 0)) amt_vld,
The SUM (NVL (case when d.f lag='0' then b.a mount end, 0)) amt_uvld,
The SUM (NVL (b.a mount, 0)) amt
The from BI007 b
Left the join
(select * from dhbi_pccode_all) d
On b.s tore_code=d.s tore_code
And b.t ender_code=which ender_code
Where b.b iz_date & gt;
='2019-08-27 00:00:00'And b.b iz_date & lt;
='2019-08-21 00:00:00'Group by b.t rans_no, b.s tore_code, b.b iz_date, b.d istrict_code
) e
On a.t rans_no=e. rans_no
And a.s tore_code=e.s tore_code
And a. iz_date=e.b iz_date
And a. d. istrict_code=e.d istrict_code
GROUP BY a. IZ_DATE, A.s tore_code, a. d. istrict_code, a.o rder_method, a.c us_type