Sql query needed [message #339053] |
Wed, 06 August 2008 12:05  |
user71408
Messages: 585 Registered: November 2007 Location: NE
|
Senior Member |
|
|
Hi All,
I have two tables." Head" and "Detail"
Head table is having folloeing columns.
cost_change Description Act_Date Create_Date
Detail Table contains
these two tables having "Cost_change" is common columns
if I am giving the following query
select * from Head sh,
Detail sd
where sh.cost_change=sd.cost_change
and sd.item =1270523
and sh.act_date<=get_sysdate+1;
I got the O/P as folows
COST_CHANGE ACT_DATE CREATE_DATE COUNTRY ITEM
89082 12/7/2008 21/09/06 UK 1270523
89083 12/7/2008 21/09/06 UK 1270523
177456 11/7/2008 14/01/08 UK 1270523
I have millions of records which one single item is having different Cost_change and in this some cost_changes having same ACT_DATE and some having different ACT_DATE, I want to find out number of items which is having data like above.So please give me idea to write a query.
Please let me know for more information.
Thank you.
[Updated on: Wed, 06 August 2008 12:12] Report message to a moderator
|
|
|
|
|
|
Re: Sql query needed [message #339062 is a reply to message #339059] |
Wed, 06 August 2008 12:26   |
user71408
Messages: 585 Registered: November 2007 Location: NE
|
Senior Member |
|
|
Michel,
I wrote the query as follows. But Here I am having more than 43000 reocrds. I want ot update those cost_changes data to different ACT_DATES. is it possible to select those items and update act_dates at a time in a query. please let me know.
to find items I wrote the query as follows.
select * from Head sh,
Detail sd
where sh.cost_change=sd.cost_change
and sd.item in (select item from Detail)
and sh.act_date<=get_sysdate+1;
|
|
|
|
Re: Sql query needed [message #339078 is a reply to message #339072] |
Wed, 06 August 2008 12:58   |
user71408
Messages: 585 Registered: November 2007 Location: NE
|
Senior Member |
|
|
Michel,
There are different cost_changes for single item 1270523.
89082,89083,177456.89092 and 89083 is having same ACT_DATE .So i want to find out the items and update the ACT_DATES. No Cost_chnage should have same ACT_Date. So I need to update the act_dates for particular cost_change of an item.
Thank you
|
|
|
|