Solved

Oracle query using time component not returning expected results

Posted on 2011-03-02
21
638 Views
Last Modified: 2012-05-11
I'm running this query but not getting expected results

select pt.clm_line_tier_seqno, pt.clm_paid_dt, pt.load_dt, pt.activity_timestamp
   from ods.pclm_clm_line_tier pt
  where clm_line_tier_seqno between 190000000000000 and 190999999999999
    and trunc(load_dt) = '2-mar-2011'
    and trunc(load_dt) < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')
    order by load_dt desc;

The above query returns the following rows, but it's "less than" the time component:

Seqno                      Paid Dt             Load Dt                            Update Date
190000007066923      01-Mar-2011      02-Mar-2011 3:31:42 AM      02-Mar-2011 3:31:42 AM
190000007066924      01-Mar-2011      02-Mar-2011 3:31:42 AM      02-Mar-2011 3:31:42 AM
190000007066925      01-Mar-2011      02-Mar-2011 3:31:42 AM      02-Mar-2011 3:31:42 AM
190000007066932      01-Mar-2011      02-Mar-2011 3:31:42 AM      02-Mar-2011 3:31:42 AM


Oracle-query-with-time-component.pdf
0
Comment
Question by:Alaska Cowboy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
  • 2
  • +2
21 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 125 total points
ID: 35018624
and trunc(load_dt) = '2-mar-2011'
    and trunc(load_dt) < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')

these don't make much sense

the first should have a to_date on it, or  better yet use a range query
also,  every time on March 2, 2011 will be truncated to be less than 3:31:42am.

trunc(noon of a day) = trunc(1am of that day) = trunc(7pm of that day) = 00:00:00 of that day


0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 50 total points
ID: 35018627
You are comparing to TRUNC(LOAD_DT).  That would make the time component midnight for comparison purposes.  If you really want less than than your query should look like this:

select pt.clm_line_tier_seqno, pt.clm_paid_dt, pt.load_dt, pt.activity_timestamp
   from ods.pclm_clm_line_tier pt
  where clm_line_tier_seqno between 190000000000000 and 190999999999999
    and load_dt < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')
    order by load_dt desc;
0
 
LVL 15

Assisted Solution

by:Aaron Shilo
Aaron Shilo earned 50 total points
ID: 35018637
try this :

select pt.clm_line_tier_seqno, pt.clm_paid_dt, pt.load_dt, pt.activity_timestamp
   from ods.pclm_clm_line_tier pt
  where clm_line_tier_seqno between 190000000000000 and 190999999999999
    and to_char(load_dt,'dd-mon-rr') = '2-mar-2011'
    and load_dt < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')
    order by load_dt desc;


0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 35018667
by a range query I mean this...

      and load_dt >= to_date( '2-mar-2011' ,'dd-mon-yyyy')
      and load_dt < to_date( '2-mar-2011' ,'dd-mon-yyyy') +1

if you only want times that occur before 3:31:42 am then don't trunc it

    and load_dt < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')

if you have any indexes on load_dt,  you'll probably find using these 3 conditions instead of the two TRUNC calls will be faster
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35018703
to_char(load_dt,'dd-mon-rr')

won't work  because the  "rr" format will produce 11,  not 2011

also,  it suffers from the same index problem that TRUNC does
you can get around that with function based indexes but range queries are more versatile
with the only cost being a few keystrokes
0
 
LVL 35

Expert Comment

by:johnsone
ID: 35018709
@ashilo

 to_char(load_dt,'dd-mon-rr') = '2-mar-2011'

????

That condition will never be true.  The TO_CHAR would output 02-mar-11, which can never match.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35018736
you could change the rr to rrrr or yyyy,  but it's not an improvement over the trunc.

The data conversion should be on the constant, not the column.  That way the conversion cost is absorbed once at parse time, not once per row at runtime
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35018749
sdstuber,

>>if you only want times that occur before 3:31:42 am then don't trunc it
and load_dt < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')

but that's what I did . . . the only difference is I limited records to those loaded on Mar 2.

0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35018791
johnsone,

>>your query should look like this:

select pt.clm_line_tier_seqno, pt.clm_paid_dt, pt.load_dt, pt.activity_timestamp
   from ods.pclm_clm_line_tier pt
  where clm_line_tier_seqno between 190000000000000 and 190999999999999
    and load_dt < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')
    order by load_dt desc;

-- but I don't want records prior to March 2
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35018862
What's the expected result for the query?
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 125 total points
ID: 35018903
>>>> sdstuber,

>>>>>>if you only want times that occur before 3:31:42 am then don't trunc it
>>>>and load_dt < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')

>>>>but that's what I did . . . the only difference is I limited records to those loaded on Mar 2.

Check your original query posted in the question you DID put a trunc on the load_dt,

that's the problem.  because trunc(load_dt) will be 2011-03-02 00:00:00  for ALL date values of March 2.

So,   these are all the same value...
trunc(2011-03-02 00:00:00)
trunc(2011-03-02 03:31:42)
trunc(2011-03-02 15:27:14)
trunc(2011-03-02 19:00:00)
trunc(2011-03-02 23:59:59)

if you only want the load_dt values < 3:31:42,  then DON'T use trunc
if that's not what you want,  please rexplain the requirements,  or better yet

Provide sample data and expected output (preferably in text, not images, so we can import it to test with)
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35018909
wdosanjos,

I have two data loads, one before 12:50am and one after 3:30am, so I'm trying to separate them.
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35018930
Ok, I eliminated the "trunc(load_dt) = '2-mar-2011'

I ran this

select pt.clm_line_tier_seqno, pt.clm_paid_dt, pt.load_dt, pt.activity_timestamp
   from ods.pclm_clm_line_tier pt
  where clm_line_tier_seqno between 140000000000000 and 140999999999999
     and trunc(load_dt) = to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')
    order by load_dt desc;

and got 0 records returned, but you can see from my data (and the attachment on my original post) that the time component is 3:31:42

so something's not right with my time logic . . .
0
 
LVL 23

Assisted Solution

by:wdosanjos
wdosanjos earned 25 total points
ID: 35018975
This should give you the load before 12:50 am:

select pt.clm_line_tier_seqno, pt.clm_paid_dt, pt.load_dt, pt.activity_timestamp
   from ods.pclm_clm_line_tier pt
  where clm_line_tier_seqno between 190000000000000 and 190999999999999
    and trunc(load_dt) = '2-mar-2011'
    and load_dt < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')
    order by load_dt desc;

Open in new window

0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35018989
This also returns 0 rows

select pt.clm_line_tier_seqno, pt.clm_paid_dt, pt.load_dt, pt.activity_timestamp
   from ods.pclm_clm_line_tier pt
  where clm_line_tier_seqno between 140000000000000 and 140999999999999
    and trunc(load_dt) > to_date('02-Mar-2011 01:31:42 AM','dd-mon-yyyy hh:mi:ss PM')
    order by load_dt desc;

but the time component is clearly

This returns rows, but it sure doesn't look right:

select pt.clm_line_tier_seqno, pt.clm_paid_dt, pt.load_dt, pt.activity_timestamp
   from ods.pclm_clm_line_tier pt
  where clm_line_tier_seqno between 140000000000000 and 140999999999999
    and trunc(load_dt) < to_date('02-Mar-2011 01:31:42 AM','dd-mon-yyyy hh:mi:ss PM')
    order by load_dt desc;

Seq No                     Paid Dt                 Load Dt                             Update Date
140000007066963      01-Mar-2011      02-Mar-2011 3:31:42 AM      02-Mar-2011 3:31:42 AM
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35018992
>>>> so something's not right with my time logic . . .

yes, exactly my point above
 DON'T USE TRUNC  - you're making ALL times equivalent by doing that.

TRUNC(sysdate)  = trunc(sysdate + 1/1000)


if my query didn't return the results you wanted,  
please explain what it did wrong or what it did not do right
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35019127
oh, oops, I'm terribly sorry.

I was blind to the trunc( ) on my time component, when you all explained it I was thinking of the first trunc and didn't even see it on the time component because.

So, I'm good but thank you all for the assistance.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35019164
so the conditions I put in http:#35018667  worked then?

 great

glad I could help
please remember to close the question
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35019652
increase points
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35019765
as the asker you have to increase the points if you want to.

or request attention and a Moderator will do it for you.

I can too as a Zone Advisor, but don't do so unless specifically instructed to since it's a conflict of interest as I could be rewarding myself
0
 
LVL 1

Author Closing Comment

by:Alaska Cowboy
ID: 35019819
thanks for the multiple responses.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question