OraFAQ Forum: SQL & PL/SQL » what's the difference in terms of result

Home » SQL & PL/SQL » SQL & PL/SQL » what's the difference in terms of result (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

what's the difference in terms of result [message #346803]

Tue, 09 September 2008 13:42

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'

Report message to a moderator

Re: what's the difference in terms of result [message #346804 is a reply to message #346803]

Tue, 09 September 2008 13:48

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: what's the difference in terms of result [message #346808 is a reply to message #346804]

Tue, 09 September 2008 14:03

beetel
Messages: 96
Registered: April 2007

Member

Thanks, but what I mean is in terms of logic/result set - not really the performance.

Report message to a moderator

Re: what's the difference in terms of result [message #346811 is a reply to message #346803]

Tue, 09 September 2008 14:11

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: what's the difference in terms of result [message #346818 is a reply to message #346803]

Tue, 09 September 2008 15:16

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

You'll also need to use a proper TO_DATE function around your bind variables.

Report message to a moderator

Re: what's the difference in terms of result [message #346889 is a reply to message #346803]

Wed, 10 September 2008 00:57

flyboy
Messages: 1903
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' )

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 06 01:47:12 CST 2024