This section of the statement "case when (select lh. Status from wh2. Load_detail ld, wh2. Load_header lh where ld. Load_id=lh. Load_id and o.o rder_id=ld. Order_id and sc. Container_id=ld. Container_id)='Closed' then 'handover complete' else 'not handover' end as transition state" led to the error, but I don't know how to change, a great god help
The following is a whole section of the statement:
The select o.o rder_date as order date,
O.r equested_ship_date as required delivery date,
O.a ctual_ship_date as the actual delivery date,
Sc. Container_id as case number, o.o rder_id as invoice number,
O. chua onsignee_id as shop code, o. chua _company as shops,
The sum (sc) total_qty) as the number, using the
Case the when sc. Status='Closed' then 'has sealing' else 'not sealing end as sealing state,
Case when (select lh. Status from wh2. Load_detail ld, wh2. Load_header lh where ld. Load_id=lh. Load_id and o.o rder_id=ld. Order_id and sc. Container_id=ld. Container_id)='Closed' then 'handover complete' else 'not handover' end as transition state
The from wh2 orders o, wh2. Shipment_container sc
Where o.o rder_id=sc. Order_id
And o.a ctual_ship_date between '2017-10-01' and '2017-10-15 s'
And sc. Total_qty & gt; 0
Group by o.o rder_date, o.r equested_ship_date, o.a ctual_ship_date, o.o rder_id, o. chua onsignee_id, o. chua _company, sc. Container_id, sc. The status
The order by sc. Container_id

CodePudding user response:

Lh. Status have more than one value, it is suggested that add a rownum=1
[code=SQL]
The select o.o rder_date as order date,
O.r equested_ship_date as required delivery date,
O.a ctual_ship_date as the actual delivery date,
Sc. Container_id as case number, o.o rder_id as invoice number,
O. chua onsignee_id as shop code, o. chua _company as shops,
The sum (sc) total_qty) as the number, using the
Case the when sc. Status='Closed' then 'has sealing' else 'not sealing end as sealing state,
Case the when (select lh. Status
The from wh2 load_detail ld, wh2. Load_header lh
Where ld. Load_id=lh. Load_id
And o.o rder_id=ld. Order_id
And sc. Container_id=ld. Container_id
And rownum=1
)='Closed' then 'handover complete'
The else 'not handover'
End as transition state
The from wh2 orders o, wh2. Shipment_container sc
Where o.o rder_id=sc. Order_id
And o.a ctual_ship_date between '2017-10-01' and '2017-10-15 s'
And sc. Total_qty & gt; 0
Group by o.o rder_date, o.r equested_ship_date, o.a ctual_ship_date, o.o rder_id, o. chua onsignee_id, o. chua _company, sc. Container_id, sc. The status
The order by sc. Container_id
[code]

CodePudding user response:

Lh. Status have more than one value, it is suggested that add a rownum=1

 
The select o.o rder_date as order date, 
O.r equested_ship_date as required delivery date, 
O.a ctual_ship_date as the actual delivery date, 
Sc. Container_id as case number, o.o rder_id as invoice number, 
O. chua onsignee_id as shop code, o. chua _company as shops, 
The sum (sc) total_qty) as the number, using the 
Case the when sc. Status='Closed' then 'has sealing' else 'not sealing end as sealing state, 
Case the when (select lh. Status 
The from wh2 load_detail ld, wh2. Load_header lh 
Where ld. Load_id=lh. Load_id 
And o.o rder_id=ld. Order_id 
And sc. Container_id=ld. Container_id 
And rownum=1 
)='Closed' then 'handover complete' 
The else 'not handover' 
End as transition state 
The from wh2 orders o, wh2. Shipment_container sc 
Where o.o rder_id=sc. Order_id 
And o.a ctual_ship_date between '2017-10-01' and '2017-10-15 s' 
And sc. Total_qty & gt; 0 
Group by o.o rder_date, o.r equested_ship_date, o.a ctual_ship_date, o.o rder_id, o. chua onsignee_id, o. chua _company, sc. Container_id, sc. The status 
The order by sc. Container_id 

CodePudding user response:

This want to combine business state, first determine why there are multiple duplicate records, and the one to take, and see that kind of priority, and your this is OLAP system? Your query will be very slow, it is suggested that write associated subquery behind the from

CodePudding user response:

Try the Max???

CodePudding user response:

Blind wrote down, not running, try the original poster
The select a.o rder_date as order date,
A.r equested_ship_date as required delivery date,
A.a ctual_ship_date as the actual delivery date,
A.c ontainer_id as case number,
A.o rder_id as invoice number,
A.c onsignee_id as shop code,
A.c _company as shops,
The sum (a.t otal_qty) as the number, using the
A. sealing state as sealing,
A. transition state as transition state
The from
((select load_id,
Order_id,
Ld container_id from wh2. Load_detail)
Inner join
(select order_date,
Requested_ship_date,
Actual_ship_date,
Consignee_id,
Order_id,
C_company
The from wh2. The orders
Where actual_ship_date between '2017-10-01' and '2017-10-15')
oOn ld. Order_id=o.o rder_id
Inner join
(select container_id,
Total_qty,
Case when the status='Closed' then 'has sealing' else 'not sealing end as sealing state
The from wh2. Shipment_container
Where total_qty & gt;
0) scOn ld. Container_id=sc. Container_id
Inner join
(select load_id, case status when the 'Closed' then 'handover complete' else 'not handover' end as state transition from wh2. Load_header) lh
On the Id. Load_id=lh. Load_id) a
Group by a.o rder_date,
A.r equested_ship_date,
A.a ctual_ship_date,
A.c ontainer_id,
A.o rder_id,
A.c onsignee_id,
A.c _company.
A. sealing state,
A. the transition state

CodePudding user response:

(select lh. Status from wh2. Load_detail ld, wh2. Load_header lh where ld. Load_id=lh. Load_id and o.o rder_id=ld. Order_id and sc. Container_id=ld. Container_id)='Closed'
nullnullnullnullnullnullnullnullnull