這段陳述句“case when (select lh.status from wh2.load_detail ld,wh2.load_header lh where ld.load_id=lh.load_id and o.order_id=ld.order_id and sc.container_id=ld.container_id ) ='Closed' then '交接完成' else '未交接' end as 交接狀態”導致了報錯，但是不知道怎么改，求大神幫忙
以下是整段陳述句：
select o.order_date as 訂單日期,
o.requested_ship_date as 要求發貨日期,
o.actual_ship_date as 實際發貨日期,
sc.container_id as 箱號,o.order_id as 發貨單號,
o.consignee_id as 店鋪代碼,o.c_company as 店鋪,
sum(sc.total_qty) as 裝箱數量,
case when sc.status = 'Closed' then '已封箱' else '未封箱' end as 封箱狀態,
case when (select lh.status from wh2.load_detail ld,wh2.load_header lh where ld.load_id=lh.load_id and o.order_id=ld.order_id and sc.container_id=ld.container_id ) ='Closed' then '交接完成' else '未交接' end as 交接狀態
from wh2.orders o, wh2.shipment_container sc
where o.order_id=sc.order_id
and o.actual_ship_date between '2017-10-01' and '2017-10-15'
and sc.total_qty>0
group by o.order_date,o.requested_ship_date,o.actual_ship_date,o.order_id,o.consignee_id,o.c_company,sc.container_id,sc.status
order by sc.container_id
uj5u.com熱心網友回復：
lh.status有多個值，建議加個rownum=1[code=sql]
select o.order_date as 訂單日期,
o.requested_ship_date as 要求發貨日期,
o.actual_ship_date as 實際發貨日期,
sc.container_id as 箱號,o.order_id as 發貨單號,
o.consignee_id as 店鋪代碼,o.c_company as 店鋪,
sum(sc.total_qty) as 裝箱數量,
case when sc.status = 'Closed' then '已封箱' else '未封箱' end as 封箱狀態,
case when (select lh.status
from wh2.load_detail ld,wh2.load_header lh
where ld.load_id=lh.load_id
and o.order_id=ld.order_id
and sc.container_id=ld.container_id
and rownum = 1
) ='Closed' then '交接完成'
else '未交接'
end as 交接狀態
from wh2.orders o, wh2.shipment_container sc
where o.order_id=sc.order_id
and o.actual_ship_date between '2017-10-01' and '2017-10-15'
and sc.total_qty > 0
group by o.order_date,o.requested_ship_date,o.actual_ship_date,o.order_id,o.consignee_id,o.c_company,sc.container_id,sc.status
order by sc.container_id
[code]
uj5u.com熱心網友回復：
lh.status有多個值，建議加個rownum=1
select o.order_date as 訂單日期,
o.requested_ship_date as 要求發貨日期,
o.actual_ship_date as 實際發貨日期,
sc.container_id as 箱號,o.order_id as 發貨單號,
o.consignee_id as 店鋪代碼,o.c_company as 店鋪,
sum(sc.total_qty) as 裝箱數量,
case when sc.status = 'Closed' then '已封箱' else '未封箱' end as 封箱狀態,
case when (select lh.status
from wh2.load_detail ld,wh2.load_header lh
where ld.load_id=lh.load_id
and o.order_id=ld.order_id
and sc.container_id=ld.container_id
and rownum = 1
) ='Closed' then '交接完成'
else '未交接'
end as 交接狀態
from wh2.orders o, wh2.shipment_container sc
where o.order_id=sc.order_id
and o.actual_ship_date between '2017-10-01' and '2017-10-15'
and sc.total_qty > 0
group by o.order_date,o.requested_ship_date,o.actual_ship_date,o.order_id,o.consignee_id,o.c_company,sc.container_id,sc.status
order by sc.container_id
uj5u.com熱心網友回復：
這個要結合業務狀態，首先確定為什么存在多條重復記錄，而且要取那一條，看那種優先，還有你這種是OLAP系統的吧？你這樣查詢會很慢的，建議把關聯子查詢寫在from后面uj5u.com熱心網友回復：
用max試試???uj5u.com熱心網友回復：
瞎寫了下，沒有運行，樓主試試看
select a.order_date as 訂單日期,
a.requested_ship_date as 要求發貨日期,
a.actual_ship_date as 實際發貨日期,
a.container_id as 箱號,
a.order_id as 發貨單號,
a.consignee_id as 店鋪代碼,
a.c_company as 店鋪,
sum(a.total_qty) as 裝箱數量,
a.封箱狀態 as 封箱狀態,
a.交接狀態 as 交接狀態
from
((select load_id,
order_id,
container_id from wh2.load_detail)ld
inner join
(select order_date,
requested_ship_date,
actual_ship_date,
consignee_id,
order_id,
c_company
from wh2.orders
where actual_ship_date between '2017-10-01' and '2017-10-15')o
on ld.order_id=o.order_id
inner join
(select container_id,
total_qty,
case when status='Closed' then '已封箱' else '未封箱'end as 封箱狀態
from wh2.shipment_container
where total_qty>0)sc
on ld.container_id=sc.container_id
inner join
(select load_id,case status when 'Closed' then '交接完成' else '未交接' end as 交接狀態 from wh2.load_header)lh
on Id.load_id=lh.load_id)a
group by a.order_date,
a.requested_ship_date,
a.actual_ship_date,
a.container_id,
a.order_id,
a.consignee_id,
a.c_company,
a.封箱狀態,
a.交接狀態
uj5u.com熱心網友回復：
(select lh.status from wh2.load_detail ld,wh2.load_header lh where ld.load_id=lh.load_id and o.order_id=ld.order_id and sc.container_id=ld.container_id ) ='Closed'---case when lh.status='Closed' then 'Closed' else '1' end
uj5u.com熱心網友回復：
對lh.status 加個case when 可以試試
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/77978.html
標籤：基礎和管理