Home » SQL & PL/SQL » SQL & PL/SQL » return records based on records
return records based on records [message #668861] Mon, 19 March 2018 11:42 Go to next message
mist598
Messages: 1191
Registered: February 2013
Location: Hyderabad
Senior Member
Hi All,

I have a data like below and need to return rows based on the cost_type.

select 'Item111' item1, 'Frozen' cost_type from dual
union all
select 'Item111' item1, 'Pending' cost_type from dual


select 'Item123' item1, 'Frozen' cost_type from dual
union all
select 'Item123' item1, 'Frozen'  from dual


If the Item111 cost_type is in Frozen and Pending then i need pending records.
If the Item123 cost_type is in Frozen only then return all the records.

Please let me know if any questions
Re: return records based on records [message #668862 is a reply to message #668861] Mon, 19 March 2018 12:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Is there any other possible "cost_type"?

Re: return records based on records [message #668863 is a reply to message #668861] Mon, 19 March 2018 12:55 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
Can there be a pending only?

If not, then based on your sample, the following will work.

where item1='Item111' and cost_type='Pending'

and of the other query

where item1='Item123'
Re: return records based on records [message #668865 is a reply to message #668861] Mon, 19 March 2018 17:49 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> -- test data:
SCOTT@orcl_12.1.0.2.0> with data as
  2    (select 'Item111' item1, 'Frozen' cost_type from dual
  3  	union all
  4  	select 'Item111' item1, 'Pending' cost_type from dual
  5  	union all
  6  	select 'Item123' item1, 'Frozen' cost_type from dual
  7  	union all
  8  	select 'Item123' item1, 'Frozen' cost_type from dual)
  9  -- query:
 10  select d1.*
 11  from   data d1
 12  where  d1.cost_type = 'Pending'
 13  or     not exists
 14  	      (select d2.*
 15  	       from   data d2
 16  	       where  d2.item1 = d1.item1
 17  	       and    d2.cost_type = 'Pending')
 18  /

ITEM1   COST_TY
------- -------
Item111 Pending
Item123 Frozen
Item123 Frozen

3 rows selected.
Re: return records based on records [message #668867 is a reply to message #668865] Tue, 20 March 2018 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 67648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Where does the condition "If the Item123 cost_type is in Frozen only then return all the records." come into play in your query?

Re: return records based on records [message #668890 is a reply to message #668867] Tue, 20 March 2018 14:20 Go to previous message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Michel Cadot wrote on Mon, 19 March 2018 23:00

Where does the condition "If the Item123 cost_type is in Frozen only then return all the records." come into play in your query?

I surmised that it meant all records for that item number, if there are only frozen records for that item, no pending records, assuming that frozen and pending are the only values, as in the sample data. It looks like the o.p. may be looking for each item, if there are any pending records, then only pending records for that item, but if there are no pending records for that item, then all of the records for that item. This assumes there are only pending and frozen values, as in the example data. My guesses may or may not be correct. I figured I would offer it along with the other suggestions and wait and see what the response is.

[Updated on: Tue, 20 March 2018 14:21]

Report message to a moderator

Previous Topic: Arranging row data in column
Next Topic: Comma Seprate
Goto Forum:
  


Current Time: Fri Jan 22 22:56:33 CST 2021