Home » SQL & PL/SQL » SQL & PL/SQL » Query recent records (PL/SQL 10.2.0.4.0)
Query recent records [message #396250] Sun, 05 April 2009 20:15 Go to next message
jen804
Messages: 22
Registered: March 2009
Location: USA
Junior Member
Hi I have two files. I want to find all the records on the price file that matches with the records on the datamgmt file. This is my query, I want to restrict the eff_date on the price file to be less than 1/1/2009 but the max of all the dates in the file. Where should I insert the restriction in and how? I read that the max function might work for my scenario, but I can't figure out how to fit it in this. Thanks!

select d.vb_no, 
       d.vendor_name, 
       d.item_no, 
       d.bill_to, 
       p.sell_price, 
       p.jde_price_code, 
       p.contract_cost, 
       p.eff_date, 
       p.term_date, 
       p.current_flag
from datamgmt d, price_file p
where d.item_no=p.item_no
and d.bill_to=p.bill_to_number


Re: Query recent records [message #396251 is a reply to message #396250] Sun, 05 April 2009 20:20 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.


Post DDL for tables.
Post DML for test data.

Post expected/desired results.

>I want to restrict the eff_date on the price file to be less than 1/1/2009 but the max of all the dates in the file.
I do not understand this statement. Please clarify.

>Hi I have two files
I have more than 2 files, but what does that have to do with SQL?

If the contents of the files are meaningful to your problem or solution, then perhaps we might benefit from knowing what is contained in the 2 file.
Re: Query recent records [message #396437 is a reply to message #396251] Mon, 06 April 2009 12:23 Go to previous messageGo to next message
jen804
Messages: 22
Registered: March 2009
Location: USA
Junior Member
I've posted a test post. I don't want to make more mistakes with these postings. Post #396436. Thanks.
Re: Query recent records [message #396449 is a reply to message #396437] Mon, 06 April 2009 15:33 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
jen804 wrote on Mon, 06 April 2009 18:23
I've posted a test post. I don't want to make more mistakes with these postings. Post #396436. Thanks.

No, post the test case here. Also, if you want ot reference a post, post a link, not just the post id
Re: Query recent records [message #396455 is a reply to message #396250] Mon, 06 April 2009 17:10 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>http://www.orafaq.com/forum/m/396439/136107/#msg_396436
But contrary to previous claims, no Test Case has been provided.

Ready, Fire, Aim!
Re: Query recent records [message #396458 is a reply to message #396455] Mon, 06 April 2009 17:49 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
BlackSwan wrote on Mon, 06 April 2009 23:10
>http://www.orafaq.com/forum/m/396439/136107/#msg_396436
But contrary to previous claims, no Test Case has been provided.

Ready, Fire, Aim!


The thing that really worries me is that you can't tell the difference between test case and test post.

That'd be the later - you did tell the OP to do that after all.


@jen804 - If you supply use with create table statements, some inserts to populate them and an example of the output you expect - all the ingredients of a good test case basically - then you should get a useful answer pretty quickly.
Re: Query recent records [message #396463 is a reply to message #396458] Mon, 06 April 2009 19:17 Go to previous messageGo to next message
jen804
Messages: 22
Registered: March 2009
Location: USA
Junior Member
cookiemonster,

2 questions - what's op? and how do i post a test case? do i post the sql that i wrote or the actual data to upload somewhere? maybe i'm not understanding the lingo appropriately. i've only use oracle as writing simple queries and not much ddl. the most i've used is "create table as select"...i haven't us insert,etc.

i'm going to take a whack at what i think you guys are asking. there's no example and i tried a few "examples" on various post here and there but nothing really dots the i's yet.

so here goes another one...
My script:
create table test3
As select
       d.item_no, 
       d.cust_no, 
       p.sell_price, 
       p.eff_date, 
from test1 d, test2 p
where d.item_no=p.item_no
and d.cust_no=p.cust_num


Test table 1
item_no customer_no
55555   2626
55555   2626
62333   2546
62333   2626

Test table 2

item_no cust_num sell_price date
55555   2626        $12    2/3/2009
55555   2626        $10    1/2/2009
62333   2546        $15    5/4/2008
62333   2546        $13    5/4/2009
62333   2626        $14    6/17/2008


I want to place this (below) restriction or something like this.. I can only describe here, can't tell you exact because I don't know how to write or incorporate it in the script above. Basically I want to pull item_no and cust_no from test table 1 and pull sellprice and date from test table 2 together to make a test table 3 with the restrictions below (records less than 1/3/2009 but pull back a the most recent record on file after removing anything greater than 1/3/2009 and pull back single record for each cust_no with that item_no (ex. test table 3)

Want this restrictions... (explanation above)
where max(eff_date)<to_date('03-jan-2009','dd-mm-yyyy')


to get this result:

Test table 3
item_no cust_no sell price  date
55555   2626    $10         1/2/2009   
62333   2546    $15         5/4/2008
62333   2626    $14         6/17/2008


thanks a bunch!

[Updated on: Mon, 06 April 2009 19:22]

Report message to a moderator

Re: Query recent records [message #396467 is a reply to message #396250] Mon, 06 April 2009 19:33 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
create table test3
As select
       d.item_no, 
       d.cust_no, 
       p.sell_price, 
       p.eff_date, 
from test1 d, test2 p
where d.item_no=p.item_no
and d.cust_no=p.cust_num

Above contains syntax error

where max(eff_date)<to_date('03-jan-2009','dd-mm-yyyy')

Above contains syntax error

What happens after fixing error immediately above & changing WHERE to AND while including into CREATE TABLE?
Re: Query recent records [message #396535 is a reply to message #396463] Tue, 07 April 2009 02:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You want to use analytic functions for this I suspect.

Something like:
select distinct
       d.item_no, 
       d.cust_no, 
       max(p.sell_price) over (partition by d.item_no,d.cust_no order by p.eff_date desc) sell_price,
       max(p.eff_date) over (partition by d.item_no,d.cust_no order by p.eff_date desc) eff_date
from test1 d, test2 p
where d.item_no=p.item_no
and d.cust_no=p.cust_num 
where eff_date <to_date('03-jan-2009','dd-mm-yyyy')
Re: Query recent records [message #396537 is a reply to message #396250] Tue, 07 April 2009 02:56 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
OP means original poster - that'd be you in this case.

When I say create table and insert statements I mean just that
CREATE TABLE test AS
(col1     NUMBER(6),
 col2     VARCHAR2(8),
.....

INSERT INTO test (<columns>) VALUES (<values);
INSERT INTO test (<columns>) VALUES (<more values);
.....


It should be like that, in code tags and it should run without error.
Point is to give us some scripts we can run on our databases to recreate your tables and data so we can try different approaches to fixing the problem.

So in this case we'd need create tables for test1 and test2.

Create table as select is no use as we don't have the tables you're selecting from.
Re: Query recent records [message #396540 is a reply to message #396467] Tue, 07 April 2009 03:05 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
BlackSwan wrote on Tue, 07 April 2009 02:33
create table test3
As select
       d.item_no, 
       d.cust_no, 
       p.sell_price, 
       p.eff_date, 
from test1 d, test2 p
where d.item_no=p.item_no
and d.cust_no=p.cust_num

Above contains syntax error

where max(eff_date)<to_date('03-jan-2009','dd-mm-yyyy')

Above contains syntax error



How do you know that the statements contain syntax errors?
Just by looking at it?
Or by actually trying it?




Re: Query recent records [message #396631 is a reply to message #396540] Tue, 07 April 2009 06:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
just by looking in my case.

The trailing comma after the last of the columns to be selected is a dead giveaway - I've done that plenty of times myself.
Re: Query recent records [message #396632 is a reply to message #396631] Tue, 07 April 2009 07:00 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
JRowbottom wrote on Tue, 07 April 2009 13:57
just by looking in my case.

The trailing comma after the last of the columns to be selected is a dead giveaway - I've done that plenty of times myself.


Same here Wink

But to my big surprise even BlackSwan found errors without having a test-case at hand Wink
Re: Query recent records [message #396654 is a reply to message #396467] Tue, 07 April 2009 08:16 Go to previous messageGo to next message
jen804
Messages: 22
Registered: March 2009
Location: USA
Junior Member
Quote:

What happens after fixing error immediately above & changing WHERE to AND while including into CREATE TABLE?


I get ORA-00934: group functions is not allowed here.
Re: Query recent records [message #396655 is a reply to message #396535] Tue, 07 April 2009 08:17 Go to previous messageGo to next message
jen804
Messages: 22
Registered: March 2009
Location: USA
Junior Member
JRowbottom wrote on Tue, 07 April 2009 03:53
You want to use analytic functions for this I suspect.

Something like:
select distinct
       d.item_no, 
       d.cust_no, 
       max(p.sell_price) over (partition by d.item_no,d.cust_no order by p.eff_date desc) sell_price,
       max(p.eff_date) over (partition by d.item_no,d.cust_no order by p.eff_date desc) eff_date
from test1 d, test2 p
where d.item_no=p.item_no
and d.cust_no=p.cust_num 
where eff_date <to_date('03-jan-2009','dd-mm-yyyy')




I got error:

ora-00923: FROM keyword not found where expected
Re: Query recent records [message #396656 is a reply to message #396250] Tue, 07 April 2009 08:32 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
it's got two where's. change the second to an and.
Re: Query recent records [message #396672 is a reply to message #396656] Tue, 07 April 2009 09:41 Go to previous messageGo to next message
jen804
Messages: 22
Registered: March 2009
Location: USA
Junior Member
Thank you for everyone's help. Smile

This works but I have one concern. By putting the max at the begining, it has already filters out all the dates and only selected the max of those partitions. It would eliminate any possible dates after 1/3/2009. Does { eff_date <to_date('03-jan-2009','dd-mm-yyyy')} removes that concern?



select distinct
       d.item_no, 
       d.cust_no, 
       max(p.sell_price) over (partition by d.item_no,d.cust_no order by p.eff_date desc) sell_price,
       max(p.eff_date) over (partition by d.item_no,d.cust_no order by p.eff_date desc) eff_date
from test1 d, test2 p
where d.item_no=p.item_no
and d.cust_no=p.cust_num 
and eff_date <to_date('03-jan-2009','dd-mm-yyyy')


[Updated on: Tue, 07 April 2009 10:30]

Report message to a moderator

Re: Query recent records [message #396683 is a reply to message #396250] Tue, 07 April 2009 11:08 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
analytics (max in this case) are applied after the where clause.
So that query will blanket ignore any record after 3rd Jan.
Re: Query recent records [message #396689 is a reply to message #396683] Tue, 07 April 2009 13:13 Go to previous messageGo to next message
jen804
Messages: 22
Registered: March 2009
Location: USA
Junior Member
So what you're saying is I should use the below?

select distinct
       d.item_no, 
       d.cust_no, 
       p.sell_price       
       p.eff_date
from test1 d, test2 p
where d.item_no=p.item_no
and d.cust_no=p.cust_num 
and iif(eff_date is null and eff_date<to_date('03-jan-2009','dd-mm-yyyy'), Null,max(eff_date)


Re: Query recent records [message #396690 is a reply to message #396250] Tue, 07 April 2009 13:17 Go to previous message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>iif(eff_date is null and eff_date<to_date('03-jan-2009','dd-mm-yyyy'), Null,max(eff_date)
NOT valid SQL

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm


Show us what you did (if you tried it yourself) and how Oracle responded (COPY & PASTE your SQL*Plus session),
including errors and/or why the result is not what you want.

Do not describe, explain or report - show us!

[Updated on: Tue, 07 April 2009 13:20]

Report message to a moderator

Previous Topic: view to see create table script
Next Topic: Result of COUNT function back in Table Column
Goto Forum:
  


Current Time: Fri Dec 02 22:36:10 CST 2016

Total time taken to generate the page: 0.40021 seconds