Home » SQL & PL/SQL » SQL & PL/SQL » How to build Consolidate Results from multiple rows (Oracle 9i)
icon2.gif  How to build Consolidate Results from multiple rows [message #332773] Wed, 09 July 2008 11:42 Go to next message
Bonita
Messages: 32
Registered: June 2008
Member
Oracle 9i

Can Someone help me out ? Thank you very much in advance.


Assuming one table Pay_History

OBJID Status Issue_Date
2008-123 Invoice 6/25/1999
2008-123 Billed 2/23/2000


2008-456 Partial 12/21/1999
2008-456 Full 1/24/2000



2008-789 Partial 6/17/1997
2008-789 Partial 3/8/1998



2008-101 Billed 8/25/1999
2008-101 Full 9/23/1999


I'd like to build one object Pay_Indictor_Date that falls into following criteria

1). If Status <> Partial or <> Full, its value is null

2). If Statu only has one Partial, its value is Issue_Date of Partial

3). If Status has multiple Partial, its value is the last Issue_Date of Partial

4). If Status doesn't contain Partial but doe has Full, its value is Issue_Date of Full.

The results should like this

SELECT xx.OBJID, xx.Pay_Indicator_Date FROM xx ( may be derived table, or something else )

2008-123 --> null
2008-456 --> 12/21/1999
2008-789 --> 3/8/1998
2008-101 --> 9/23/1999

I'm newin PL/SQL, please drop your codes in details. Thank you very much again.
Re: How to build Consolidate Results from multiple rows [message #332774 is a reply to message #332773] Wed, 09 July 2008 11:45 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: How to build Consolidate Results from multiple rows [message #332782 is a reply to message #332773] Wed, 09 July 2008 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case: create table and insert statements along with the result you want with these data.

Also always post your Oracle version (4 decimals).

Regards
Michel
Re: How to build Consolidate Results from multiple rows [message #332786 is a reply to message #332773] Wed, 09 July 2008 12:49 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
This message is very beautiful.
Re: How to build Consolidate Results from multiple rows [message #332936 is a reply to message #332786] Thu, 10 July 2008 02:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It is a very pretty post.
So pretty, that I'll post a solution.
create table test_0044 (OBJID varchar2(30), Status varchar2(30), Issue_Date date); 

insert into test_0044 values ('2008-123','Invoice',to_date('6/25/1999','mm/dd/yyyy'));
insert into test_0044 values ('2008-123','Billed',to_date('2/23/2000','mm/dd/yyyy'));

insert into test_0044 values ('2008-456','Partial',to_date('12/21/1999','mm/dd/yyyy'));
insert into test_0044 values ('2008-456','Full',to_date('1/24/2000','mm/dd/yyyy'));

insert into test_0044 values ('2008-789','Partial',to_date('6/17/1997','mm/dd/yyyy'));
insert into test_0044 values ('2008-789','Partial',to_date('3/8/1998','mm/dd/yyyy'));

insert into test_0044 values ('2008-101','Billed',to_date('8/25/1999','mm/dd/yyyy'));
insert into test_0044 values ('2008-101','Full',to_date('9/23/1999 ','mm/dd/yyyy'));

select objid
      ,pay_indicator_date
from  (select objid
             ,first_value(case when status in ('Full','Partial') then issue_Date else null end) over (partition by objid order by decode(status,'Full',1,'Partial',2,3),issue_date desc) pay_indicator_date
             ,row_number() over (partition by objid order by decode(status,'Full',1,'Partial',2,3),issue_date desc) rnum
       from   test_0044)
where rnum = 1;
icon1.gif  Re: How to build Consolidate Results from multiple rows [message #333112 is a reply to message #332936] Thu, 10 July 2008 09:22 Go to previous messageGo to next message
Bonita
Messages: 32
Registered: June 2008
Member
Hi, JRowbottom.

Thank you very much for your time. It is really cool to see your code. Very , very useful. Believe that I need years to be able to write codes like this. You're best.

Thanks again.Laughing
Re: How to build Consolidate Results from multiple rows [message #333123 is a reply to message #333112] Thu, 10 July 2008 09:50 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Another way of doing it.
SQL> l
  1  select objid
  2        ,pay_indicator_date
  3  from  (select objid
  4               ,first_value(case when status in ('Full','Partial') then issue_Date else null end) 
over (partition by objid order by decode(status,'Full',1,'Partial',2,3),issue_date desc) pay_indicator_date
  5               ,row_number() over (partition by objid order by decode(status,'Full',1,'Partial',2,3),issue_date desc) rnum
  6         from   test_0044)
  7* where rnum = 1
SQL> /

OBJID                          PAY_INDICATOR_DATE
------------------------------ ------------------
2008-101                       23-SEP-99
2008-123
2008-456                       24-JAN-00
2008-789                       08-MAR-98

SQL> l
  1  select objid,
  2  max(case when status in ('Partial','Full') then issue_date end)
  3      keep(dense_rank first order by case when status = 'Full' then 1 when status = 'Partial' then 2 else 3 end) date_val
  4  from
  5  test_0044
  6* group by objid
SQL> /

OBJID                          DATE_VAL
------------------------------ ------------------
2008-101                       23-SEP-99
2008-123
2008-456                       24-JAN-00
2008-789                       08-MAR-98


Regards

Raj

P.S : @JRowbottom, thanks for the insert script.

[Updated on: Thu, 10 July 2008 09:51]

Report message to a moderator

Re: How to build Consolidate Results from multiple rows [message #333134 is a reply to message #333123] Thu, 10 July 2008 10:17 Go to previous messageGo to next message
Bonita
Messages: 32
Registered: June 2008
Member
Thanks again. I may didn't say it very clear. When Partial and Full both exist, pay_indicator_date is the last date of Partial. So I changed your code little to

select objid
,pay_indicator_date
from (select objid
,first_value(case when status in ('Full','Partial') then issue_Date else null end) over (partition by objid order by decode(status,'Partial',1,'Full',2,3),issue_date desc) pay_indicator_date
,row_number() over (partition by objid order by decode(status,'Partial',1,'Full',2,3),issue_date desc) rnum
from test_0044)
where rnum = 1;

It shows.

OBJID PAY_INDICATOR_DATE
2008-101 9/23/1999
2008-123
2008-456 12/21/1999
2008-789 3/8/1998

This is what I expected.

I implemented your prototype into multiple comparsion ( minimum, maximum value selection ... ) replacing Decode with Case When . It works great. Very useful in my reality

Once again, thank you very much.
Re: How to build Consolidate Results from multiple rows [message #333146 is a reply to message #333134] Thu, 10 July 2008 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I may didn't say it very clear.

This is one of the reasons why a test case is very useful.
Hope you will post it next time.

Regards
Michel
Re: How to build Consolidate Results from multiple rows [message #333152 is a reply to message #333146] Thu, 10 July 2008 10:41 Go to previous message
Bonita
Messages: 32
Registered: June 2008
Member
I surely will. Michel.

Many thanks for you all. This is great place !Shocked
Previous Topic: ORA-12838: cannot read/modify an object after modifying it in parallel
Next Topic: load data from one table to another table avoiding duplicate data
Goto Forum:
  


Current Time: Sat Dec 03 01:32:51 CST 2016

Total time taken to generate the page: 0.07957 seconds