Home » SQL & PL/SQL » SQL & PL/SQL » Determine Incomplete Orders (Oracle 11g)
Determine Incomplete Orders [message #644646] Wed, 11 November 2015 19:46 Go to next message
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 #644647 is a reply to message #644646] Wed, 11 November 2015 20:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Determine Incomplete Orders [message #644648 is a reply to message #644647] Wed, 11 November 2015 20:29 Go to previous messageGo to next message
stun66
Messages: 3
Registered: November 2015
Location: Dallas
Junior Member
Order | Event Code
123   | AB1
123   | BC2
456   | AB1
456   | BC2
456   | ZE3
789   | AB1
890   | AB1
Re: Determine Incomplete Orders [message #644649 is a reply to message #644648] Wed, 11 November 2015 20:43 Go to previous messageGo to next message
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 Go to previous message
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> 
Previous Topic: PLSQL Collection index by varchar
Next Topic: ORA-12015: cannot create a fast refresh materialized view from a complex query
Goto Forum:
  


Current Time: Tue Mar 19 02:49:32 CDT 2024