Yesterday Last Record

[htran]

Hi all,

I am creating a query which will run every day at a specific time. My database is Oracle 8i. I need to know the last unique key the system used the previous day. My script looks like this:

select max(orderdetail.orderkey) from orderdetail,orders
where orderdetail.orderkey=orders.orderkey
and orderdetail.status = '0'
and orders.type = '0'
and orderdetail.adddate > (sysdate-1)
and orders.externorderkey not like 'M-%';

For example, the unique keys are assigned by the system as following:

0000001
0000002
0000003
0000004
0000005

and so on. With these unique keys, the script must be able to identify 0000005 is the last number was used. The script runs once a day at the same time using Windows Scheduler.

Could someone help me with this as my script does not pull the right record?

Thanks..

 

[SantaMufasa]

Tran,

Try this change to your code (presuming that the rest of your WHERE clauses are correct):

select max(orderdetail.orderkey) from orderdetail,orders
where orderdetail.orderkey=orders.orderkey
and orderdetail.status = '0'
and orders.type = '0'
and orderdetail.adddate [b]< trunc(sysdate)[/b]
and orders.externorderkey not like 'M-%';

Let us know if this improves your results.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[htran]

Hi Mufasa,

Thanks for the info. I figured it out last night. The correct code should be:

select max(orderdetail.orderkey) from orderdetail,orders
where orderdetail.orderkey=orders.orderkey
and orderdetail.status = '0'
and orders.type = '0'
and orderdetail.adddate > trunc(sysdate)
and orders.externorderkey not like 'M-%';

 

[BJCooperIT]

> trunc(sysdate)" will only return the max key dated sometime past 12:00am TODAY. You said you wanted the previous day correct?

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
Author and Sole Proprietor of: Emu Products Plus

 

[htran]

Hi BJCooperIT,

The command ">trunc(sysdate)" did produce the result I was expecting.

I have a Scheduler runs the script every night at 19:10 which would go back to 19:10 the night before. What I need is the last order from the 24 hour time frame so we could determine how many orders are coming in during that period.

Am I making any sense?

Thanks...

 

[SantaMufasa]

Tran,

Yes, as Barbara mentions, your code works fine provided you use the command:

set dwimnwis on

That is the "Do What I Mean, Not What I Say" option. Right, BJ?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[BJCooperIT]

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
Author and Sole Proprietor of: Emu Products Plus

 

[SantaMufasa]

So, Tran, as a followup to your code modification, isn't the qualification, ">trunc(sysdate)", going to simply give you the MAX(orderdetail.orderkey), period? Won't you receive the same result if you get rid of that entire clause ("and orderdetail.adddate > trunc(sysdate)")?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[htran]

Hi Santa,

It is true that if I get rid of the entire line "and orderdetail.adddate > trunc(sysdate)", the result will still be the same.

Can someone shed some lights on this for me.

Thanks...

 

[SantaMufasa]

If you assign orderdetail.orderkey in ascending order, then if you ask for

1) max(orderdetail.orderkey)

or

2) max(orderdetail.orderkey) since yesterday

Should they not be the same?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.