aspose file tools*
The moose likes Oracle/OAS and the fly likes Need help in an query development Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Need help in an query development" Watch "Need help in an query development" New topic
Author

Need help in an query development

Vikas Kapoor
Ranch Hand

Joined: Aug 16, 2007
Posts: 1374
I am facing some problem in developing an query and looking for some help.

Find all the records where
1)customer_id and product_id should have unique combination.
2)Should have valid date range.(start_date <= today(sysdate) <= end_date)
3)If you have more than one entry for particular customer_id and product_id combination then pick the record with latest modified_date.

Table Name: CUSTOMER_PRICE

Assumptions:Today:26-jul-08

customer_id product_id price start_date end_date modified_date

[1] [1] [50][20-jun-2008] [20-aug-2008] [10-jul-2008]
[1] [1] [55][25-jun-2008] [25-jul-2008] [15-jul-2008]
[1] [1] [45][26-jul-2008] [20-sep-2008] [12-jul-2008]
[2] [1] [10][10-AUG-2008] [30-AUG-2008] [11-JUL-2008]
[2] [1] [15][20-JUL-2008] [20-AUG-2008] [10-JUL-2008]
[2] [1] [5][20-JUL-2008] [20-SEP-2008] [08-JUL-2008]
[2] [2] ...
[1] [2] ...
so on

Expected Output

customer_id product_id price start_date end_date modified_date

[1] [1] [45][26-jul-2008] [20-sep-2008] [12-jul-2008]
[2] [1] [15][20-JUL-2008] [20-AUG-2008] [10-JUL-2008]

My attempts:

I have tried different queries but still couldn't get the desired output.Here they are,




I have also tried with distinct keyword.

Technologies used:struts,ejb3,oracle 10gXE

Please ask if you need more information.

Any help?
Thanks
[ July 26, 2008: Message edited by: Vishal Pandya ]
Agador Paloi
Ranch Hand

Joined: Jan 24, 2006
Posts: 118
I think you are close, the group by and use MAX(modified_date) to satisfy your third requirement .
Vikas Kapoor
Ranch Hand

Joined: Aug 16, 2007
Posts: 1374
Hello Agador,

Thanks for your reply(Atleast/last i got one).

I figured out the problem (using only queries)like this ,

And the output of first query is fed to next query.
Can this be done in better way?
[ July 29, 2008: Message edited by: Vishal Pandya ]
Hemavathy Ramamoorthy Gopal
Greenhorn

Joined: May 13, 2008
Posts: 17
Hi,

Please try this and let me know whether it met your requirement or not since I dont have oracle set up.

select cust_id,prod_id from cust_table
where m_dt in (select max(m_dt) from cust_table where st_dt <= sysdate and en_dt >= sysdate group by cust_id,prod_id)

Note : m_dt should contain unique data.


Regards,
Hema_RG
Vikas Kapoor
Ranch Hand

Joined: Aug 16, 2007
Posts: 1374
Originally posted by Hemavathy Ramamoorthy Gopal:
Note : m_dt should contain unique data.

Practically,many records can be modified on the same day.
Hemavathy Ramamoorthy Gopal
Greenhorn

Joined: May 13, 2008
Posts: 17
Hi,

As you said in practical it will get change for that purpose only we are using group by clause to get a unique record in order to make more perfect group by clause can be used in main query also.
Hemavathy Ramamoorthy Gopal
Greenhorn

Joined: May 13, 2008
Posts: 17
Hi Vishal,

I am egar to know whether the given query has meet your requirement or not because I framed out this query out visually since I don't have any setup to crosscheck the query. Please give me descriptive explanation in what ways it is not meeting your requirement.
Vikas Kapoor
Ranch Hand

Joined: Aug 16, 2007
Posts: 1374
The assumption that you have made is wrong. More than one record could be updated on the same day so we can't have unique data for modified_date.i.e.
Note : m_dt should contain unique data.
This isn't possible.
Hemavathy Ramamoorthy Gopal
Greenhorn

Joined: May 13, 2008
Posts: 17
Hi Vishal,

Check this out.

select distinct cust_id,prod_id, max(md_dt) from cust_price
where md_dt in (select max(md_dt) from cust_price where st_dt <= sysdate and en_dt >= sysdate group by cust_id,prod_id)
group by cust_id,prod_id;
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Need help in an query development