Home » SQL & PL/SQL » SQL & PL/SQL » Batch Id from child statuses (Oracle 11g)
Batch Id from child statuses [message #580764] Wed, 27 March 2013 15:38 Go to next message
renjivij
Messages: 17
Registered: October 2008
Junior Member
Hi Im looking for a query which returns the batch for which all the child should either be in 'A_STATUS','B_STATUS' or 'C_STATUS'.
In this query im expecting a query which returns batch 2,3 and 4.
Could someone help me on this.

create table batch (batchid number);
insert into batch values(1);
insert into batch values(2);
insert into batch values(3);
insert into batch values(4);


create table items (batchid number,itemid varchar2(10),status varchar2(10));

insert into items values(1,'item1','A_STATUS');
insert into items values(1,'item2','B_STATUS');
insert into items values(1,'item3','C_STATUS');
insert into items values(1,'item4','D_STATUS');

insert into items values(2,'item5','A_STATUS');
insert into items values(2,'item6','B_STATUS');
insert into items values(2,'item7','C_STATUS');
insert into items values(2,'item8','C_STATUS');

insert into items values(3,'item9','A_STATUS');
insert into items values(3,'item10','B_STATUS');
insert into items values(3,'item11','C_STATUS');
insert into items values(3,'item12','B_STATUS');

insert into items values(4,'item13','A_STATUS');
insert into items values(4,'item14','B_STATUS');


Re: Batch Id from child statuses [message #580769 is a reply to message #580764] Wed, 27 March 2013 18:37 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Hi,


The tables are not linked, there is no foreign key from the items table referring to the batch table so the batchid in the items can actually be anything.

Please read Normalization.


SELECT batchid
FROM items
MINUS
SELECT batchid
FROM items
WHERE status NOT IN ('A_STATUS', 'B_STATUS', 'C_STATUS');



Please read MINUS operator.


Regards,
Dariyoosh

[Updated on: Wed, 27 March 2013 18:42]

Report message to a moderator

Re: Batch Id from child statuses [message #580777 is a reply to message #580764] Thu, 28 March 2013 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
or which all the child should either be in


Child? You mean items?

Regards
Michel
Re: Batch Id from child statuses [message #580846 is a reply to message #580777] Thu, 28 March 2013 10:33 Go to previous messageGo to next message
renjivij
Messages: 17
Registered: October 2008
Junior Member
Yes I meant items.
Re: Batch Id from child statuses [message #581027 is a reply to message #580769] Sun, 31 March 2013 21:57 Go to previous messageGo to next message
renjivij
Messages: 17
Registered: October 2008
Junior Member
Dariyoosh,

Tables are normalized. I have just added few sample to get an idea so that I can use this in actual query.

When I think of the actual case ,there is little difference in the way tables should be.
New strucure given below
create table batch (batchid number,flag varchar2(2));
insert into batch values(1,'N');
insert into batch values(2,'N');
insert into batch values(3,'N');
insert into batch values(4,'Y');
insert into batch values(5,'Y');
insert into batch values(6,'N');


create table items (batchid number,itemid varchar2(10),status1 varchar2(10),status2 varchar2(10));

insert into items values(1,'item1','A_STATUS','COMPLETED');
insert into items values(1,'item2','B_STATUS','COMPLETED');
insert into items values(1,'item3','C_STATUS','COMPLETED');
insert into items values(1,'item4','D_STATUS','COMPLETED');

insert into items values(2,'item5','A_STATUS','COMPLETED');
insert into items values(2,'item6','B_STATUS','TASK_COMPLETED');
insert into items values(2,'item7','C_STATUS','COMPLETED');
insert into items values(2,'item8','C_STATUS','COMPLETED');

insert into items values(3,'item9','A_STATUS','COMPLETED');
insert into items values(3,'item10','B_STATUS','COMPLETED');
insert into items values(3,'item11','C_STATUS','COMPLETED');
insert into items values(3,'item12','B_STATUS','COMPLETED');

insert into items values(4,'item13','A_STATUS','COMPLETED');
insert into items values(4,'item14','B_STATUS','COMPLETED');

insert into items values(5,'item14','A_STATUS','COMPLETED');
insert into items values(5,'item15','B_STATUS','CANCELLED');

insert into items values(6,'item16','A_STATUS','COMPLETED');
insert into items values(6,'item17','B_STATUS','COMPLETED');
insert into items values(6,'item18','D_STATUS','DELETED');


Here I need to get the batch and item for which batch flag should be N and all items belonging to the batch should have status1
either A_STATUS,B_STATUS or C_STATUS.
items with status2 'DELETED' and 'CANCELLED' can be ignored from the batch.
So the result im expecting is batch 2 and 3 and all items in batch2,3 within batch with status2 not as 'CANCELLED' or 'DELETED'

Re: Batch Id from child statuses [message #581031 is a reply to message #581027] Mon, 01 April 2013 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select batchid from batch where flag='N'
  2  minus
  3  select batchid 
  4  from items
  5  where status1 not in ('A_STATUS', 'B_STATUS', 'C_STATUS')
  6     or status2 in ('DELETED','CANCELLED')
  7  /
   BATCHID
----------
         2
         3

This gives you the result you said
But your statement "items with status2 'DELETED' and 'CANCELLED' can be ignored from the batch." is ambiguous.
Does it mean "items with status1 A_STATUS,B_STATUS or C_STATUS and status2 'DELETED' or 'CANCELLED' can be ignored" or "any item with status2 'DELETED' or 'CANCELLED' can be ignored".
In this latter option 6 should also be returned (and the query should be changed).

Regards
Michel

[Updated on: Mon, 01 April 2013 01:45]

Report message to a moderator

Re: Batch Id from child statuses [message #581074 is a reply to message #581031] Mon, 01 April 2013 09:10 Go to previous messageGo to next message
renjivij
Messages: 17
Registered: October 2008
Junior Member
Michel,

Sorry for the wrong answer list i mentioned.
Im expecting batch 6 also in the result as D_STATUS item is in DELETED.

So result should be 2,3 and 6.

Could you please help me.
Re: Batch Id from child statuses [message #581080 is a reply to message #581074] Mon, 01 April 2013 10:56 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You just have to change the WHERE conditions in the second query.
Please try to do it and if you can't come back with your tries, we'll help you to go further.
Hint: the condition must select the rows you take care about but don't want.
So you don't want the rows that have ... but (and) don't care about the rows that have ...

Regards
Michel
icon14.gif  Re: Batch Id from child statuses [message #581087 is a reply to message #581080] Mon, 01 April 2013 20:03 Go to previous messageGo to next message
renjivij
Messages: 17
Registered: October 2008
Junior Member
Thanks Michel.You pointed me to right direction.
It was little confusing for me using minus operator initially.
Re: Batch Id from child statuses [message #581092 is a reply to message #581087] Tue, 02 April 2013 00:25 Go to previous message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So final solution is:
SQL> select batchid from batch where flag='N'
  2  minus
  3  select batchid 
  4  from items
  5  where status1 not in ('A_STATUS', 'B_STATUS', 'C_STATUS')
  6    and status2 not in ('DELETED','CANCELLED')
  7  /
   BATCHID
----------
         2
         3
         6

Regards
Michel
Previous Topic: create a local report
Next Topic: Reg Job Sheduler
Goto Forum:
  


Current Time: Fri Aug 22 23:07:34 CDT 2014

Total time taken to generate the page: 0.09519 seconds