Results 1 to 13 of 13

Thread: index and dates

  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: index and dates

    Hi All,
    I'm selecting data from a table for a specific time period(say for 200402).
    But if I have any function around the date column it might not use the index on the it. So I'm trying to rewrite the query to pull the records for the date passed in and use the index on open_dt for it. But I get an error here.
    Any idea where I might be going wrong.

    here open_dt is a date column and I'm passing a parameter 'period' which is a number column in YYYYMM format.

    SQL> ed
    Wrote file afiedt.buf

    1 select open_dt from acct_c a
    2 where
    3 to_number(to_char(a.open_dt,'yyyymm')) > any (&period,'YYYYMM') and
    4* to_number(to_char(a.open_dt,'yyyymm')) <= any (&period, 'YYYYMM')
    SQL> /
    Enter value for period: 200302
    old 3: to_number(to_char(a.open_dt,'yyyymm')) > any (&period,'YYYYMM') and
    new 3: to_number(to_char(a.open_dt,'yyyymm')) > any (200302,'YYYYMM') and
    Enter value for period: 200302
    old 4: to_number(to_char(a.open_dt,'yyyymm')) <= any (&period, 'YYYYMM')
    new 4: to_number(to_char(a.open_dt,'yyyymm')) <= any (200302, 'YYYYMM')
    to_number(to_char(a.open_dt,'yyyymm')) <= any (200302, 'YYYYMM')
    *
    ERROR at line 4:
    ORA-01722: invalid number

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >any (200302, 'YYYYMM')
    ^^^ Where/what is this "any" function documented?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    compare the dates and do not convert to number.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jul 2005
    Posts
    276
    SQL> ED
    Wrote file afiedt.buf

    1 select open_dt from acct_c a
    2 where
    3 to_char(a.open_dt,'yyyymm') > (&period,'YYYYMM') and
    4* to_char(a.open_dt,'yyyymm') <= (&period, 'YYYYMM')
    SQL> /
    Enter value for period: 200801
    old 3: to_char(a.open_dt,'yyyymm') > (&period,'YYYYMM') and
    new 3: to_char(a.open_dt,'yyyymm') > (200801,'YYYYMM') and
    Enter value for period: 200802
    old 4: to_char(a.open_dt,'yyyymm') <= (&period, 'YYYYMM')
    new 4: to_char(a.open_dt,'yyyymm') <= (200802, 'YYYYMM')
    to_char(a.open_dt,'yyyymm') > (200801,'YYYYMM') and
    *
    ERROR at line 3:
    ORA-01797: this operator must be followed by ANY or ALL
    I get the above error, so I included 'any' to see if thats what its looking for. And I get data when I have ANY in the query.

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    change your where selection to something like:
    Code:
    where a.open_dt >= to_date( &period, 'YYYYMM' ) and a.open_dt < last_day( to_date( &period, 'YYYYMM' ) ) + 1
    ANY is a pretty-well, uncommon to me, documented function.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    here is a free clue & I wonder how long it will be retained.
    Code:
    select open_dt from acct_c a
    where a.open_dt between to_date('200801','YYYYMM') 
    and to_date('200802','YYYYMM');
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jul 2005
    Posts
    276
    I used Jmartinez query and it works perfectly fine. Thank you very much!

    But shouldn't ANY be used in the query? I havent seen anyone use it though. But it always throws an error when I use dates with > OR <

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >But it always throws an error when I use dates with > OR <
    Errors occur when you use invalid syntax.

    >ANY is a pretty-well, uncommon to me, documented function.
    Now that I have RTFM, I still don't understand its real world application or use.
    Last edited by anacedent; 08-05-08 at 17:55.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Jul 2005
    Posts
    276
    >any (200302, 'YYYYMM')
    ^^^ Where/what is this "any" function documented?

    Your reply made me to think if I was using the wrong syntax by including ANY. Now are you saying that its valid to use ANY in the query?

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It is your query & you can use what ever syntax that produces the desired results.
    Whether or not ANY is "valid" I can not answer because I don't know the data in the table & I don't know what the output requirements are.
    Prior to today I was not aware that "ANY" was a valid comparison operator, so i have learned something by RTFM.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    seems like a lazy way to code. definitely a funky function.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >definitely a funky function.
    That was my initial reaction, but IMO it is NOT a function according to the documentation. It is a group comparison condition.


    http://download.oracle.com/docs/cd/B...htm#sthref2720
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  13. #13
    Join Date
    Jul 2003
    Posts
    2,296
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •