这段语句“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
以下是整段语句:
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
解决方案 »
- 如何在数据库中搜索给定的任意关键词
- oracle中转换函数问题
- 在Linux8.0的X下运行dbassist没有反应,是什么原因?
- SQL触发器问题(数据库 使用sqlplus这个软件)
- VB6+SP6的在WIN98下怎样连接ORACLE?
- Oracle 6i Forms Builder工具栏图标在哪里?
- 一段SQL触发器代码变成oracle触发器的代码,请大家帮助
- 关于oracle exp/imp的问题
- 菜鸟问题,请问专家(在线等!!!!!)
- winnt 上oracle8.1.6数据库不能自动启动,如何解决?
- oracle数据库图形化工具sqldeveloper的怎么配置智能提示
- oracle异常,急急急!
[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]
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
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.交接状态
---case when lh.status='Closed' then 'Closed' else '1' end