Home » SQL & PL/SQL » SQL & PL/SQL » what's the difference in terms of result (10g)
what's the difference in terms of result [message #346803] Tue, 09 September 2008 13:42 Go to next message
beetel
Messages: 96
Registered: April 2007
Member
SELECT a.pindex,
       TO_CHAR(LAST_DAY(TRUNC(a.l_dt)), 'YYYY-MM-DD HH24:MI:SS') AS l_dt,
       SUM(a.cond) AS cond,
       'N' flag
  FROM MY_TABLE a
 WHERE l_dt between :first_day_current_month and :last_day_current_month
   AND a.pindex IN
       (SELECT DISTINCT b.pindex
          FROM MY_TABLE b
         WHERE b.l_dt BETWEEN :prev_date AND :current_date)
 GROUP BY pindex, LAST_DAY(TRUNC(l_dt)), 'N'


versus

SELECT a.pindex,
       TO_CHAR(LAST_DAY(TRUNC(a.l_dt)), 'YYYY-MM-DD HH24:MI:SS') AS l_dt,
       SUM(a.cond) AS cond,
       'N' flag
  FROM MY_TABLE a
 WHERE l_dt between :first_day_current_month and :last_day_current_month
   AND l_dt between :prev_date AND :current_date
 GROUP BY pindex, LAST_DAY(TRUNC(l_dt)), 'N'
Re: what's the difference in terms of result [message #346804 is a reply to message #346803] Tue, 09 September 2008 13:48 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

post EXPLAIN_PLAN for both
Re: what's the difference in terms of result [message #346808 is a reply to message #346804] Tue, 09 September 2008 14:03 Go to previous messageGo to next message
beetel
Messages: 96
Registered: April 2007
Member
Thanks, but what I mean is in terms of logic/result set - not really the performance.
Re: what's the difference in terms of result [message #346811 is a reply to message #346803] Tue, 09 September 2008 14:11 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above


# Provide DDL (CREATE TABLE, etc.) as appropriate instead of listing the table structure.
# Provide INSERT statements for sample data

Then while you are at it, run the SQL yourself.
Re: what's the difference in terms of result [message #346818 is a reply to message #346803] Tue, 09 September 2008 15:16 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
You'll also need to use a proper TO_DATE function around your bind variables.
Re: what's the difference in terms of result [message #346889 is a reply to message #346803] Wed, 10 September 2008 00:57 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> what's the difference in terms of result

Strange to put the question into title, but, the answer depends on MY_TABLE data and input conditions. But, you may try both queries with two rows having the same PINDEX, non-zero COND column and L_DT equal to
TO_DATE( '05.09.2008', 'dd.mm.yyyy' ) (first row)
TO_DATE( '07.09.2008', 'dd.mm.yyyy' ) (second row)

and input conditions
FIRST_DATE_CURRENT_MONTH = TO_DATE( '01.09.2008', 'dd.mm.yyyy' )
LAST_DATE_CURRENT_MONTH = TO_DATE( '30.09.2008', 'dd.mm.yyyy' )
PREV_DATE = TO_DATE( '06.09.2008', 'dd.mm.yyyy' )
CURRENT_DATE = TO_DATE( '10.09.2008', 'dd.mm.yyyy' )

Previous Topic: Update Statement Performance Issue
Next Topic: Delete with commit scope
Goto Forum:
  


Current Time: Sat Dec 10 04:55:18 CST 2016

Total time taken to generate the page: 0.11336 seconds