Home » SQL & PL/SQL » SQL & PL/SQL » first_value analytic function (9i)
first_value analytic function [message #386477] Sun, 15 February 2009 09:52 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi Guys,

Plan to achieve the following

1) Join two tables
2) Pick the first cost value before certain date
3) If not found, go beyond date and pick first cost value
create table product (
   id number,
   code number,
   desc1 varchar2(1));


create table expiry (
  code number,
  cost number,
  expiry date);

insert into product values (1,6,'a');
insert into product values (2,7,'b');
insert into product values (3,6,'c');
insert into product values (4,8,'d');

insert into expiry values (6,101,'2-Jan-2008');
insert into expiry values (6,100,'3-Jan-2008');
insert into expiry values (6,120,'5-Jan-2008');
insert into expiry values (7,120,'5-Jan-2008');


The output im looking for is

Id , Code , Cost
1 , 6 , 100
2 , 7 , 120
3 , 6 , 100


I have constructed the query using first_value

select distinct p.id, p.code, first_value (e.cost ) over (partition by e.code order by e.expiry desc)
from 
product p, expiry e
where p.code = e.code
and e.expiry <= '4-Jan-2008'


Now plan to do the same for record with
id: 2, Code: 7, since this record does not have a cost which is less than 4-Jan-2008, i want to pick the first cost which is more than 4-Jan-2008 order by expiry asc.

Appreciate any hints so i could incorporate it in the query above.

Report message to a moderator

Re: first_value analytic function [message #386478 is a reply to message #386477] Sun, 15 February 2009 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
First '4-Jan-2008' is a string not a date, so '3-Feb-2009' is before '4-Jan-2008'.

Quote:
since this record does not have a cost which is less than 4-Jan-2008, i want to pick the first cost which is more than 4-Jan-2008 order by expiry asc

Which one when they are several? For instance, add
insert into expiry values (7,130,'3-Jan-2008');

Regards
Michel

[Updated on: Sun, 15 February 2009 10:39]

Report message to a moderator

Re: first_value analytic function [message #386526 is a reply to message #386478] Mon, 16 February 2009 00:08 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

thansk for the reply.

If we have the following record added

insert into expiry values (7,130,'3-Jan-2008');


The output should change to 


Id , Code , Cost
1 , 6 , 100
2 , 7 , 130
3 , 6 , 100



Reason being since 3-Jan-2008 falls before 4-Jan-2008, it takes this value.

Basically the query should just pick cost before 4-Jan-2008, if there no matching found based on 'code', then it goes beyond 4-Jan-2008 and gets the cost.


My query works, but only thing, now, im not sure how to change it to work in a way if theres no 'cost' found for data 
 <= to_date(4-Jan-2008)
, how do i now change it to find data 
> to_date(4-Jan-2008)


If there a several in expirty table which falls on same day for the same 'code', it should just take the first cost, note: expiry column has a timestamp attached

[Updated on: Mon, 16 February 2009 00:12]

Report message to a moderator

Re: first_value analytic function [message #386534 is a reply to message #386526] Mon, 16 February 2009 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Sorry I meant if you insert
insert into expiry values (5,130,'3-Jan-2008');

And '3-Jan-2008' is still NOT a date:
SQL> insert into expiry values (6,101,'2-Jan-2008');
insert into expiry values (6,101,'2-Jan-2008')
                                 *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Regards
Michel

[Updated on: Mon, 16 February 2009 00:43]

Report message to a moderator

Re: first_value analytic function [message #386536 is a reply to message #386534] Mon, 16 February 2009 00:51 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi Michel,

Lets just assume 3-Jan-2008 is a date, when i try to insert the following into my table, it works fine. After insert its displayed this way

select expiry from expiry


> 3/01/2008

If the below sql is executed, it would not effect the end results since the 'code' 5 does not exist in product table

insert into expiry values (5,130,'3-Jan-2008');

Report message to a moderator

Re: first_value analytic function [message #386539 is a reply to message #386536] Mon, 16 February 2009 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Of course, also add it to product. The core question is:
Quote:
Which one when they are several possible?


And use TO_DATE to insert/select a date.

Regards
Michel

Report message to a moderator

Re: first_value analytic function [message #386545 is a reply to message #386539] Mon, 16 February 2009 02:11 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

I will just rephrase my question base on following data and table structure.

create table product (
   id number,
   code number,
   desc1 varchar2(1));


create table expiry (
  code number,
  cost number,
  expiry date);


insert into product values (1,6,'a');
insert into product values (2,7,'b');
insert into product values (3,6,'c');
insert into product values (4,8,'d');

insert into expiry values (6,101,'2-Jan-2008 11:49:41 PM');
insert into expiry values (6,100,'3-Jan-2008 11:49:41 PM');
insert into expiry values (6,120,'5-Jan-2008 11:49:41 PM');
insert into expiry values (7,120,'5-Jan-2008 11:49:41 PM');
insert into expiry values (5,130,'3-Jan-2008 11:49:41 PM');


My aim is to
1) For all product, i want to get the expiry.cost by joining the product.code and expiry.code

2) The way i extract the cost is by doing the following

2.1 if for each product.id, join product.code with expiry.code
if found, extract the first expiry.cost closest to <= '4-Jan- 2008'

partition by expiry.code order by expiry.expiry desc


2.2 If not found, with the same join, extract the first expiry.code closest to > '4-Jan-2008'


partition by expiry.code order by expiry.expiry asc


I have prepared a code, but the problem is, it does not process the point 2.2 correctly. 

select distinct p.id, p.code, first_value (e.cost ) over (partition by e.code order by e.expiry desc)
from 
product p, expiry e
where p.code = e.code
and e.expiry <= '4-Jan-2008'


Michel, earlier you inquired a question on
Which one when they are several possible?

Can you give me example what you meant by the above.

[Updated on: Mon, 16 February 2009 02:15]

Report message to a moderator

Re: first_value analytic function [message #386547 is a reply to message #386545] Mon, 16 February 2009 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
2.2 If not found, with the same join, extract the first expiry.code closest to > '4-Jan-2008'

If they are 2 or more rows satifying this condition, which one you will get?
This is the case in your example if you add product 5.

Regards
Michel

Report message to a moderator

Re: first_value analytic function [message #386559 is a reply to message #386547] Mon, 16 February 2009 02:32 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
If product 5 is added using the following code

insert into product values (5,5,'d');


And assuming theres the following row in expiry table

insert into expiry values (5,130,'3-Jan-2008');



In the final out, i will get a new result

id, code, cost
5, 5 , 130

If the code 5 does not exist in expiry table, i will not get the row above in final output.

If the expiry table have 2 rows for code 5

insert into expiry values (5,130,'3-Jan-2008 01:03:03am');
insert into expiry values (5,130,'3-Jan-2008 01:02:03 am');


And since both are <= 4-Jan-2008, it will be sorted desc, and the first_value(cost) will be taken

If the expiry table have the following 1 row

insert into expiry values (5,110,'5-Jan-2008 01:03:03am');


Then, first it will find for expiry <= 4-Jan-2008, since not found, it will now find for record > 4-Jan-2008, so it will output the following row

id, code, cost
5, 5 , 110

Report message to a moderator

Re: first_value analytic function [message #386563 is a reply to message #386559] Mon, 16 February 2009 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What should be the result for product 3 with
insert into expiry values (5,130,'3-Jan-2008 11:49:41 PM');

And I still can't execute your test case, it is not correct.

Regards
Michel

[Updated on: Mon, 16 February 2009 02:39]

Report message to a moderator

Re: first_value analytic function [message #386566 is a reply to message #386563] Mon, 16 February 2009 02:44 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Yeah, my test insert values have to be formatted correctly for inserting date

As for product 3

insert into product values (3,6,'c');


insert into expiry values (6,101,'2-Jan-2008 11:49:41 PM');
insert into expiry values (6,100,'3-Jan-2008 11:49:41 PM');
insert into expiry values (6,120,'5-Jan-2008 11:49:41 PM');


Since we have the following data.

It will join by code, and first it will look for expiry <= 4-Jan-2008, so the first_value of cost after sorting by expiry desc, will be 100. So result would be

id, code, cost
3, 6, 100

If for product 3, the expiry table only have two records as below

insert into expiry values (6,110,'5-Jan-2008 11:20:41 PM');
insert into expiry values (6,120,'5-Jan-2008 11:49:41 PM');


It will first search for expiry <= 4-Jan-2008, since no records found, it will then take first_value(cost) > '4-Jan-2008' order by expiry asc, which should give the following result

id, code, cost
3, 6, 110

Report message to a moderator

Re: first_value analytic function [message #386577 is a reply to message #386566] Mon, 16 February 2009 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Yeah, my test insert values have to be formatted correctly for inserting date

Once again you're wrong and my previous error example prove it.

What is the result for product 2?

Regards
Michel

Report message to a moderator

Re: first_value analytic function [message #386679 is a reply to message #386577] Mon, 16 February 2009 10:18 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi Michel,

I might be confusing you with the list of data i have generated as the test-case, i have considered all the questions you inquired and reorganize the test data.


create table product (
   id number,
   code number,
   desc1 varchar2(1));


create table expiry (
  code number,
  cost number,
  expiry date);

insert into product values (1,6,'a');
insert into product values (2,7,'b');
insert into product values (3,6,'c');
insert into product values (4,8,'d');
insert into product values (5,2,'e');

insert into expiry values (6,101,to_date('2008/01/02:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
insert into expiry values (6,100,to_date('2008/01/03:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
insert into expiry values (6,120,to_date('2008/01/05:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
insert into expiry values (7,130,to_date('2008/01/05:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
insert into expiry values (8,140,to_date('2008/01/05:08:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
insert into expiry values (8,150,to_date('2008/01/05:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
insert into expiry values (8,160,to_date('2008/01/06:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
insert into expiry values (9,170,to_date('2008/01/05:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));

commit;



So based on the business rule earlier, the following output is expected
id, code , cost
1, 6, 100 
2, 7, 130
3, 6, 100
4, 8, 140
5, 2, 0



Its basically just traversing the table back and forth. Managed to figure out the script, but the issue is now, the last record in the output above is not displayed [5,2,0], Is there any way i could simplify the query below and not to make it look redundant

[code]


select distinct p.id, p.code, first_value (e.cost ) over (partition by e.code order by e.expiry desc)
from
product p, expiry e
where p.code = e.code
and e.expiry <= to_date('4-Jan-2008')

union all

select distinct p.id, p.code, first_value (e.cost ) over (partition by e.code order by e.expiry asc)
from product p, expiry e
where p.code = e.code
and e.expiry > to_date('4-Jan-2008')
and p.id not in
(
select distinct p.id
from
product p, expiry e
where p.code = e.code
and e.expiry <= to_date('4-Jan-2008')
)

[code]

[Updated on: Mon, 16 February 2009 10:36]

Report message to a moderator

Re: first_value analytic function [message #386682 is a reply to message #386679] Mon, 16 February 2009 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why:
2, 7, 130
when
7,130,to_date('2008/01/05:12:00:00AM',
indicates it is after 4th january.

Regards
Michel

Report message to a moderator

Re: first_value analytic function [message #386683 is a reply to message #386682] Mon, 16 February 2009 10:41 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Yes, actually what it does if it could not find a match before 4-Jan-2008, it goes beyond that, and orders expiry date asc, and takes the first cost value.

Its basically just traversing the table back and forth. Managed to figure out the script, but the issue is now, the last record in the output above is not displayed [5,2,0], Is there any way i could simplify the query below and not to make it look redundant,if i do an outer join, results somehow gets duplicated.

select distinct p.id, p.code, first_value (e.cost ) over (partition by e.code order by e.expiry desc)
from 
product p, expiry e
where p.code = e.code
and e.expiry <= to_date('4-Jan-2008')

union all

select distinct p.id, p.code, first_value (e.cost ) over (partition by e.code order by e.expiry asc)
from product p, expiry e
where p.code = e.code
and e.expiry > to_date('4-Jan-2008')
and p.id not in
(
select distinct p.id
from 
product p, expiry e
where p.code = e.code
and e.expiry <= to_date('4-Jan-2008')
)


The results posted earlier has some issues, correct results should be

id, code , cost
1, 6, 100
2, 7, 130
3, 6, 100
4, 8, 150
5, 2, 0

The problem with the query above the record [5,2,0] is omitted, not sure how do i include it in

[Updated on: Mon, 16 February 2009 10:47]

Report message to a moderator

Re: first_value analytic function [message #386685 is a reply to message #386683] Mon, 16 February 2009 11:09 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Again not too sure whether this will satisfy all your criteria. 

select prod.id, prod.code, 
       nvl(
            min(exp.cost) 
                keep(dense_rank first order by abs(to_Date('04.01.2008','dd.mm.yyyy') - exp.expiry) , expiry 
                    )
           , 0) 
from
product prod,
expiry exp
where
prod.code = exp.code (+)
group by prod.id, prod.code;


Hope this helps.

Regards

Raj

[Updated on: Mon, 16 February 2009 11:40]

Report message to a moderator

Re: first_value analytic function [message #386720 is a reply to message #386685] Mon, 16 February 2009 18:59 Go to previous message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi Raj,

Thanks..
It seems to work with my test critera.

But appreciate if this main piece can be explanied a little

nvl(
            min(exp.cost) 
                keep(dense_rank first order by abs(to_Date('04.01.2008','dd.mm.yyyy') - exp.expiry) , expiry 
                    )
           , 0)



Because i plan to incorporate a small change whereby, if the cost returned for expiry <= 4-Jan-2008 is 0, i then want to take the cost for > 4-Jan-2008.

Report message to a moderator

Previous Topic: PLS-00405: subquery not allowed in this context
Next Topic: problem in opening a file (utl_file)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Feb 12 08:53:08 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.