Home » SQL & PL/SQL » SQL & PL/SQL » select date range values
select date range values [message #278295] Fri, 02 November 2007 13:26 Go to next message
ravikanth_b
Messages: 42
Registered: November 2007
Location: Bay Area, CA
Member
Hi Experts,

Could you please help me with this query.

I have two tables.

Table1 has columms member_id,duration,pcses_id,computer_id,rn_id and event_date.

Table2 has columns member_id,duration,pcses_id,computer_id,rn_id,start_time,end_time.

I want to update member_id,duration,pcses_id in table1 using values in table2 for a date range (table1.event_date) spanning +/- 5 mins of table2 (start_date). If we get multiple rows in table2 we need to take the latest time. Here is the query i have, which gives an error sub-query gives too many rows. Please let me know how can I accomplish this.

UPDATE table1 est
SET (member_id,duration,pcses_id)=(SELECT distinct pcs.member_id,pcs.duration,pcs.pcses_id
FROM ((select * from table2 pcs
where CAST (pcs.start_time AS DATE) >= '01-AUG-07'
AND CAST (pcs.end_time AS DATE) < '01-SEP-07')) pcs
WHERE est.computer_id=pcs.computer_id
AND est.rn_id=pcs.rn_id
AND est.event_date BETWEEN CAST (pcs.start_time AS DATE) - 300/(24*60*60)
AND CAST (pcs.start_time AS DATE) + 300/(24*60*60)
);

Hope I made some sense here.

Thanks for your help!
Re: select date range values [message #278300 is a reply to message #278295] Fri, 02 November 2007 13:40 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
'01-AUG-07' & '01-SEP-07' are STRINGS; NOT date datatypes.
You MUST use TO_DATE to manipulate date datatypes in SQL.


'this is a string 01-SEP-07 and not a DATE!'

[Updated on: Fri, 02 November 2007 13:41] by Moderator

Report message to a moderator

Re: select date range values [message #278303 is a reply to message #278300] Fri, 02 November 2007 13:45 Go to previous messageGo to next message
ravikanth_b
Messages: 42
Registered: November 2007
Location: Bay Area, CA
Member
Well, that can be corrected. I am looking for more specific logic where i could pick up the most recent date in the given range.

Thanks for the reply.
Re: select date range values [message #278305 is a reply to message #278295] Fri, 02 November 2007 13:52 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
the MAX function is typically user to get the largest value
Re: select date range values [message #278306 is a reply to message #278305] Fri, 02 November 2007 13:57 Go to previous messageGo to next message
ravikanth_b
Messages: 42
Registered: November 2007
Location: Bay Area, CA
Member
I know that, but where do we fit in the clause.
Re: select date range values [message #278308 is a reply to message #278306] Fri, 02 November 2007 14:56 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Forget about the update for now. Try to write a select for the condition you have described. Then we will help you how to convert it into an equivalent update statement. By doing it that way you learn more.

Regards

Raj
Re: select date range values [message #278800 is a reply to message #278308] Mon, 05 November 2007 13:16 Go to previous messageGo to next message
ravikanth_b
Messages: 42
Registered: November 2007
Location: Bay Area, CA
Member
Hello guys,

I came up with the following. What do you think, am I doing it right or do you have any better suggestions.

UPDATE aude.rb_table1 est
     SET (member_id,duration,pcses_id)=(select max(pcs.member_id) keep (dense_rank first order by pcs.start_time) member_id,
                                               max(pcs.duration)  keep (dense_rank first order by pcs.start_time,pcs.member_id) duration,
                                               max(pcs.pcses_id)  keep (dense_rank first order by pcs.start_time,pcs.member_id,pcs.duration) ses_id
                                         FROM (select p.*
					                           from aude.rb_table2 p
					                          where CAST (p.start_time AS DATE) >= to_date('01-AUG-07')
                                                AND CAST (p.end_time AS DATE) < to_date('01-SEP-07')
                                               ) pcs
                     WHERE est.computer_id=pcs.computer_id
                       AND est.rn_id=pcs.meter_id
                       AND est.event_date BETWEEN CAST (pcs.start_time AS DATE) - 300/(24*60*60)
                                              AND CAST (pcs.start_time AS DATE) + 300/(24*60*60)
                       AND pcs.xflag=0
                       group by est.rn_id,est.computer_id
                      );
Re: select date range values [message #278810 is a reply to message #278800] Mon, 05 November 2007 14:41 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1197999096334

Regards

Raj
Re: select date range values [message #278849 is a reply to message #278295] Mon, 05 November 2007 23:42 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hi Rarvikanth ,

It is always good practice to add the clause "Where" clausea with Update statement unless you want to update all the rows in the table nad unless you are expecting the fields to be updated with NULL in case Update subquery is not satisfied.

Actually this is the common Mistake found.

Thumbs Up
Rajuvan.

[Updated on: Mon, 05 November 2007 23:43]

Report message to a moderator

Re: select date range values [message #278881 is a reply to message #278849] Tue, 06 November 2007 02:12 Go to previous messageGo to next message
ravikanth_b
Messages: 42
Registered: November 2007
Location: Bay Area, CA
Member
Well, I was aware of this when framing this update. Actually, the columns that I will be updating will be NULL before the update hence this should not be an issue. Thanks for pointing out this though.
Re: select date range values [message #278960 is a reply to message #278800] Tue, 06 November 2007 07:54 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
ravikanth_b wrote on Mon, 05 November 2007 14:16

where CAST (p.start_time AS DATE) >= to_date('01-AUG-07')
AND CAST (p.end_time AS DATE) < to_date('01-SEP-07')



This is still a problem. TO_DATE requires a format mask.
FOO SCOTT>l
  1* select to_date('01-sep-07') from dual
FOO SCOTT>/
select to_date('01-sep-07') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Re: select date range values [message #279023 is a reply to message #278960] Tue, 06 November 2007 11:33 Go to previous messageGo to next message
ravikanth_b
Messages: 42
Registered: November 2007
Location: Bay Area, CA
Member
My ENV has format mask of DD-MON-YY so not a problem.

thanks.
Re: select date range values [message #279063 is a reply to message #279023] Tue, 06 November 2007 15:34 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
What he is trying to say it is a good practice to include the format when using to_date. Assuming you are writing a program you know what the program is supposed to do and you know what the requirement is, but still most of the organisation will ask you to write a specification. It is because if somebody is been asked to do an enhacement he/she can understand what the requirement is and what needs to be done. Similarly it is always a good practice to tell oracle what the format of the date is.

I am not being picky, just a thought.

Regards

Raj
Re: select date range values [message #279067 is a reply to message #279063] Tue, 06 November 2007 16:14 Go to previous message
ravikanth_b
Messages: 42
Registered: November 2007
Location: Bay Area, CA
Member
I totally agree to what you said. Suggestion taken Smile
Previous Topic: Overlap Issue
Next Topic: CREATE SYNONYM
Goto Forum:
  


Current Time: Fri Dec 09 21:13:34 CST 2016

Total time taken to generate the page: 0.14101 seconds