Determine Incomplete Orders [message #644646] |
Wed, 11 November 2015 19:46 |
|
stun66
Messages: 3 Registered: November 2015 Location: Dallas
|
Junior Member |
|
|
Trying to write a query to find all orders that don't have all the following events AB1, BC2, and ZE3 associated with an order.
Order | Event Code
123 | AB1
123 | BC2
456 | AB1
456 | BC2
456 | ZE3
789 | AB1
890 | AB1
In this scenario, I would be returned the following orders 123, 789 and 890 since these orders don't have all three events associated with them.
|
|
|
|
|
Re: Determine Incomplete Orders [message #644649 is a reply to message #644648] |
Wed, 11 November 2015 20:43 |
Solomon Yakobson
Messages: 3267 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
SQL> with sample as (
2 select 123 order_number,'AB1' event_code from dual union all
3 select 123,'BC2' from dual union all
4 select 456,'AB1' from dual union all
5 select 456,'BC2' from dual union all
6 select 456,'ZE3' from dual union all
7 select 789,'AB1' from dual union all
8 select 890,'AB1' from dual
9 )
10 select order_number
11 from sample
12 group by order_number
13 having sum(
14 distinct case event_code
15 when 'AB1' then 1
16 when 'BC2' then 2
17 when 'ZE3' then 3
18 else 0
19 end
20 ) != 6
21 /
ORDER_NUMBER
------------
123
789
890
SQL>
SY.
|
|
|
Re: Determine Incomplete Orders [message #644665 is a reply to message #644649] |
Thu, 12 November 2015 03:49 |
mnitu
Messages: 159 Registered: February 2008 Location: Reims
|
Senior Member |
|
|
Another approach using collections
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
SQL>
SQL> Create Or Replace Type varchar2_tt As Table Of Varchar2(10)
2 /
Type created
SQL> With Data As (
2 select 123 order_number, Cast('AB1' As Varchar2(3)) event_code from dual union all
3 select 123,'BC2' from dual union all
4 select 456,'AB1' from dual union all
5 select 456,'BC2' from dual union all
6 select 456,'ZE3' from dual union all
7 select 789,'AB1' from dual union all
8 select 890,'AB1' from dual
9 )
10 Select order_number
11 From Data
12 Group by order_number
13 Having Cast(Collect(event_code) As varchar2_tt) != (Select varchar2_tt('AB1','BC2','ZE3')
14 from dual
15 )
16 /
ORDER_NUMBER
------------
123
789
890
SQL>
|
|
|