17,382
社区成员
发帖
与我相关
我的任务
分享Oracle 缺少表达式
SELECT a.BIZ_DATE,2 as db_id,A.store_code,a.district_code,
a.order_method,a.cus_type,'' as sales_type,'' as sales_channel,
COUNT(case when Order_Amount>0 then a.trans_no end) as trans_cnt,
SUM(is null(a.people_number,0)) as people_number,
SUM(is null(a.order_amount,0)) as order_amount,
SUM(is null(A.Sales_Amount,0)) as sales_amount,
SUM(is null(a.discount_amount,0)) as discount_amount,
SUM(IS NULL(e.amt,0)) as amt,
SUM(IS NULL(e.amt_vld,0)) as amt_vld,
SUM(IS NULL(e.amt_uvld,0)) as amt_uvld,
SUM(IS NULL(e.amt_vld,0)) as amt_all
from (
select a.BIZ_DATE,A.store_code,a.district_code,
a.order_method,a.cus_type,a.trans_no,
case when SUM(is null(A.Sales_Amount,0)) =0 then 0 else SUM(is null(a.people_number,0)) end as people_number,
SUM(is null(a.order_amount,0)) as order_amount,
SUM(is null(A.Sales_Amount,0)) as sales_amount,
SUM(is null(a.discount_amount,0)) as discount_amount
FROM BI005 A
where a.biz_date>='2019-08-27 00:00:00'
and a.biz_date<='2019-08-21 00:00:00'
group by a.BIZ_DATE,A.store_code,a.district_code,
a.order_method,a.cus_type,a.trans_no)a
left join
(select b.trans_no,b.store_code,b.biz_date,b.district_code,
SUM(is null(case when d.flag='1' then b.amount end,0)) amt_vld,
SUM(is null(case when d.flag='0' then b.amount end,0)) amt_uvld,
SUM(is null(b.amount,0) ) amt
from BI007 b
left join
(select * from dhbi_pccode_all) d
on b.store_code = d.store_code
and b.tender_code = d.tender_code
where b.biz_date >='2019-08-27 00:00:00'
and b.biz_date<='2019-08-21 00:00:00'
group by b.trans_no,b.store_code,b.biz_date,b.district_code
) e
on a.trans_no = e.trans_no
and a.store_code = e.store_code
and a.biz_date = e.biz_date
and a.district_code = e.district_code
GROUP BY a.BIZ_DATE,A.store_code,a.district_code,a.order_method,a.cus_type
a.order_method,a.cus_type,'' as sales_type,'' as sales_channel,
COUNT(case when Order_Amount>0 then a.trans_no end) as trans_cnt,
SUM(is null(a.people_number,0)) as people_number,
SUM(is null(a.order_amount,0)) as order_amount,
SUM(is null(A.Sales_Amount,0)) as sales_amount,
SUM(is null(a.discount_amount,0)) as discount_amount,
SUM(IS NULL(e.amt,0)) as amt,
SUM(IS NULL(e.amt_vld,0)) as amt_vld,
SUM(IS NULL(e.amt_uvld,0)) as amt_uvld,
SUM(IS NULL(e.amt_vld,0)) as amt_all
from (
select a.BIZ_DATE,A.store_code,a.district_code,
a.order_method,a.cus_type,a.trans_no,
case when SUM(is null(A.Sales_Amount,0)) =0 then 0 else SUM(is null(a.people_number,0)) end as people_number,
SUM(is null(a.order_amount,0)) as order_amount,
SUM(is null(A.Sales_Amount,0)) as sales_amount,
SUM(is null(a.discount_amount,0)) as discount_amount
FROM BI005 A
where a.biz_date>='2019-08-27 00:00:00'
and a.biz_date<='2019-08-21 00:00:00'
group by a.BIZ_DATE,A.store_code,a.district_code,
a.order_method,a.cus_type,a.trans_no)a
left join
(select b.trans_no,b.store_code,b.biz_date,b.district_code,
SUM(is null(case when d.flag='1' then b.amount end,0)) amt_vld,
SUM(is null(case when d.flag='0' then b.amount end,0)) amt_uvld,
SUM(is null(b.amount,0) ) amt
from BI007 b
left join
(select * from dhbi_pccode_all) d
on b.store_code = d.store_code
and b.tender_code = d.tender_code
where b.biz_date >='2019-08-27 00:00:00'
and b.biz_date<='2019-08-21 00:00:00'
group by b.trans_no,b.store_code,b.biz_date,b.district_code
) e
on a.trans_no = e.trans_no
and a.store_code = e.store_code
and a.biz_date = e.biz_date
and a.district_code = e.district_code
GROUP BY a.BIZ_DATE,A.store_code,a.district_code,a.order_method,a.cus_type
...全文
请发表友善的回复…
发表回复
AHUA1001 2019-08-27
- 打赏
- 举报
SELECT a.BIZ_DATE,2 as db_id,A.store_code,a.district_code,
a.order_method,a.cus_type,'' as sales_type,'' as sales_channel,
COUNT(case when Order_Amount>0 then a.trans_no end) as trans_cnt,
SUM( nvl(a.people_number,0)) as people_number,
SUM( nvl(a.order_amount,0)) as order_amount,
SUM( nvl(A.Sales_Amount,0)) as sales_amount,
SUM( nvl(a.discount_amount,0)) as discount_amount,
SUM( nvl(e.amt,0)) as amt,
SUM( nvl(e.amt_vld,0)) as amt_vld,
SUM( nvl(e.amt_uvld,0)) as amt_uvld,
SUM( nvl(e.amt_vld,0)) as amt_all
from (
select a.BIZ_DATE,A.store_code,a.district_code,
a.order_method,a.cus_type,a.trans_no,
case when SUM( nvl(A.Sales_Amount,0)) =0 then 0 else SUM( nvl(a.people_number,0)) end as people_number,
SUM( nvl(a.order_amount,0)) as order_amount,
SUM( nvl(A.Sales_Amount,0)) as sales_amount,
SUM( nvl(a.discount_amount,0)) as discount_amount
FROM BI005 A
where a.biz_date>='2019-08-27 00:00:00'
and a.biz_date<='2019-08-21 00:00:00'
group by a.BIZ_DATE,A.store_code,a.district_code,
a.order_method,a.cus_type,a.trans_no)a
left join
(select b.trans_no,b.store_code,b.biz_date,b.district_code,
SUM( nvl(case when d.flag='1' then b.amount end,0)) amt_vld,
SUM( nvl(case when d.flag='0' then b.amount end,0)) amt_uvld,
SUM( nvl(b.amount,0) ) amt
from BI007 b
left join
(select * from dhbi_pccode_all) d
on b.store_code = d.store_code
and b.tender_code = d.tender_code
where b.biz_date >='2019-08-27 00:00:00'
and b.biz_date<='2019-08-21 00:00:00'
group by b.trans_no,b.store_code,b.biz_date,b.district_code
) e
on a.trans_no = e.trans_no
and a.store_code = e.store_code
and a.biz_date = e.biz_date
and a.district_code = e.district_code
GROUP BY a.BIZ_DATE,A.store_code,a.district_code,a.order_method,a.cus_type
卖水果的net 2019-08-27
- 打赏
- 举报
SUM(is null(a.people_number,0)) as people_number,
换成
SUM(NVL(a.people_number,0)) as people_number,
