Home » SQL & PL/SQL » SQL & PL/SQL » Sql query needed (9.0.4.0)
Sql query needed [message #339053] Wed, 06 August 2008 12:05 Go to next message
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
Cost_change  Item  Country

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 #339055 is a reply to message #339053] Wed, 06 August 2008 12:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use count?

Regards
Michel
Re: Sql query needed [message #339057 is a reply to message #339055] Wed, 06 August 2008 12:17 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Michel,

I want to find out those item numbers as well.

[Updated on: Wed, 06 August 2008 12:17]

Report message to a moderator

Re: Sql query needed [message #339059 is a reply to message #339057] Wed, 06 August 2008 12:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use it in its analytical form.

Regards
Michel
Re: Sql query needed [message #339062 is a reply to message #339059] Wed, 06 August 2008 12:26 Go to previous messageGo to next message
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 #339072 is a reply to message #339062] Wed, 06 August 2008 12:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand what you want.
Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Sql query needed [message #339078 is a reply to message #339072] Wed, 06 August 2008 12:58 Go to previous messageGo to next message
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
Re: Sql query needed [message #339099 is a reply to message #339078] Wed, 06 August 2008 14:23 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Previous Topic: query on "Can one retrieve only the Nth row from a table? "
Next Topic: concatinating date
Goto Forum:
  


Current Time: Sat Dec 10 22:45:17 CST 2016

Total time taken to generate the page: 0.11119 seconds