Home » SQL & PL/SQL » SQL & PL/SQL » Operations within rows returned by query
Operations within rows returned by query [message #394962] Mon, 30 March 2009 22:35
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Is the any best possible way i can achieve the following

create table inv (
  inv_item_id number ,
  org   number,
  desc1 varchar2(10))
  
create table lookup (
  inv_item_id number ,
  org   number,
  status varchar2(10),
  desc1 varchar2(10))
  

insert into inv(inv_item_id,org) values (100,104);
insert into inv(inv_item_id,org) values (100,113);

insert into inv(inv_item_id,org) values (101,104);
insert into inv(inv_item_id,org) values (101,113);

insert into inv(inv_item_id,org) values (102,103);
insert into inv(inv_item_id,org) values (102,113);

insert into lookup values(100,104,'Restricted','chip');
insert into lookup values(100,113,'Normal','chip');

insert into lookup values(101,104,'Other','hd');
insert into lookup values(101,113,'Restricted','hd');

insert into lookup values(102,103,'Other','mb');
insert into lookup values(102,113,'Restricted','mb');

commit;




inv_item_id, org, desc1
100,	104,	  null
100,	113, 	  null
101,	104,	  hd
101,	113,	  hd
102,	103,	  mb
102,	113,	  mb


Im trying to do this.

1) Join condition would be inv_item_id, org from both tables
2) One is base table, and another one is lookup
3) If the specific inv_item_id,org exists in lookup,and org = 104 then get the desc1 column. Logic should be
decode(b.status,'Restricted',null,b.desc1) desc1

Now the tricky part comes in picture. from the query returned above, i need to

1) Check if any rows returned has org 104 and desc1(derived using formula above) is null, if there is,
all other desc1 with the same inv_item_id should be initialized to null as well

I manage to do it using query below, seems to work, is there any better way to write this query

select  a.inv_item_id, a.org, 
first_value(case a.org when 104 then (decode(b.status,'Restricted',null,b.desc1)) else  b.desc1 end) 
  OVER (PARTITION BY a.inv_item_id ORDER BY a.inv_item_id,DECODE (a.org, 104, ' ', a.org))
  from inv a, 
  lookup b
where a.inv_item_id = b.inv_item_id
and a.org = b.org 
)

[Updated on: Tue, 31 March 2009 01:55] by Moderator

Report message to a moderator

Previous Topic: Converting long datatype to its equivalent
Next Topic: query
Goto Forum:
  


Current Time: Thu Dec 08 08:08:45 CST 2016

Total time taken to generate the page: 0.10677 seconds