Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Help needed to convert Access filter to Oracle

    Hi folks,

    I'm trying to convert a date filter used in an access query, but can't figure out what it should be in the Oracle language.

    Here is the Access filter:
    Code:
    IIf(Month(Date())<9,Year(Date())-1,Year(Date()))
    I thought maybe I could use something like the following for Oracle, but it throws the error 'invalid number':
    Code:
    IF EXTRACT(MONTH FROM current_date) < 9 THEN EXTRACT(YEAR FROM current_date) - 1 ELSE EXTRACT(YEAR FROM current_date) END IF
    Should I be looking at it another way?

    Thanks
    <- Hides behind a rock.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Should I be looking at it another way?
    TO_CHAR()
    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
    Sep 2005
    Posts
    240
    I should have pointed out that I'm adding this as a filter to the year field in this query:
    Code:
    SELECT     PERSON.ID_NUMBER, ADDRESS.ADDRESS_LINE1, ADDRESS.ADDRESS_LINE2, COURSE.DESCRIPTION AS COURSE, 
                          COURSE_INSTANCE.COURSE_YEAR AS YEAR, COURSE_INSTANCE.ACADEMIC_YEAR, COURSE.COURSE AS CODE
    FROM         PERSON, ADDRESS, STUDENT_COURSE_DETAIL, COURSE_INSTANCE, COURSE, STATUS, STUDENT_CATEGORY
    WHERE     PERSON.TERM_ADDRESS = ADDRESS.OBJECT_ID AND PERSON.OBJECT_ID = STUDENT_COURSE_DETAIL.PERSON AND 
                          STUDENT_COURSE_DETAIL.COURSE_INSTANCE = COURSE_INSTANCE.OBJECT_ID AND COURSE_INSTANCE.COURSE = COURSE.OBJECT_ID AND 
                          STUDENT_COURSE_DETAIL.STATUS = STATUS.OBJECT_ID AND 
                          STUDENT_COURSE_DETAIL.STUDENT_CATEGORY = STUDENT_CATEGORY.OBJECT_ID AND (NOT (COURSE.DESCRIPTION LIKE '*CCC*')) AND 
                          (STUDENT_CATEGORY.STUDENT_CATEGORY LIKE 'RES') AND (STATUS.STATUS = 'R')
    Quote Originally Posted by anacedent
    TO_CHAR()
    So, I should use TO_CHAR instead of EXTRACT?

    I have tried the following:
    Code:
    IF TO_CHAR(current_date, 'MM') < 9 THEN TO_CHAR(current_date, 'YYYY') - 1 ELSE TO_CHAR(current_date 'YYYY') END IF
    When I add this however, it puts it all in quotes, and no records are returned, also the year is a numeric field.
    <- Hides behind a rock.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Use CASE

    Code:
    WHERE YEAR = (case when to_char(sysdate,'MM') in ('01','02',...'08') then 
                      to_number(to_char(sysdate,'YYYY')) - 1
                  else
                      to_number(to_char(sysdate,'YYYY'))
                  end)

  5. #5
    Join Date
    Sep 2005
    Posts
    240
    Thanks chuck_forbes,

    I tried your example, but it returns the error 'invalid or missing expression'.
    <- Hides behind a rock.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What does it mean? I hope you didn't literally copy-pasted Chuck's
    Code:
    in ('01','02',...'08')
    because "..." mean that Chuck didn't want to bother with 03, 04, 05, 06 and 07 (but you should).

  7. #7
    Join Date
    Sep 2005
    Posts
    240
    No, you'll be pleased to know I didn't forget to add the other months.

    But when trying it again, it seemed to wrap the CASE in to_number as well, but then it returns the error ORA-01722 'invalid number'.

    Code:
    = TO_NUMBER('CASE WHEN TO_CHAR(SYSDATE, MM) IN ('01', '02', '03', '04', '05', '06', '07', '08') THEN TO_NUMBER(TO_CHAR(SYSDATE, YYYY))') - TO_NUMBER('1 ELSE TO_NUMBER(TO_CHAR(SYSDATE, YYYY)) END')
    Also using the following gives Error after predicate near 'THEN':

    Code:
    (CASE WHEN TO_CHAR(SYSDATE, MM) IN ('01', '02', '03', '04', '05', '06', '07', '08') THEN TO_NUMBER(TO_CHAR(SYSDATE, YYYY)) - 1 ELSE TO_NUMBER(TO_CHAR(SYSDATE, YYYY)) END)
    Last edited by KevCB226; 01-30-09 at 06:02.
    <- Hides behind a rock.

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Hm, code you have posted is not valid.

    Date format mask requires single quotes:
    Code:
    SQL> select to_char(sysdate, mm) from dual;
    select to_char(sysdate, mm) from dual
                            *
    ERROR at line 1:
    ORA-00904: "MM": invalid identifier
    
    
    SQL> select to_char(sysdate, 'mm') from dual;
    
    TO
    --
    01
    
    SQL>
    On the other hand, if you enclose TO_NUMBER's argument into single quotes (as you did: TO_NUMBER('CASE WHEN ...), you'll get an error because 'CASE WHEN ...' can not be converted into a number - remove a single quote in front of the CASE keyword!

  9. #9
    Join Date
    Sep 2005
    Posts
    240
    OK, I've added the quotes to the format masks, but it still gives me the error 'invalid or missing expression'.

    I also should have pointed out I'm doing this for a report in SQL Reporting Services, but using the Oracle .NET Provider instead of the MS one.

    Maybe it's reporting services that is causing this, will look into trying it another way.

    Thanks for the help.
    <- Hides behind a rock.

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, maybe your reporting tool doesn't recognize CASE construct. Or, did you really forget to put that missing expression into the statement?

    Because, as far as any recent (in a matter of at least a decade) Oracle database version is concerned, Chuck's code should work.

  11. #11
    Join Date
    Sep 2005
    Posts
    240
    I've managed to get it working using the examples posted.

    It seems that the diagram view pane doesn't like the CASE statement, but if you switch this pane off, and just display the syntax and results pane, it works fine.
    <- Hides behind a rock.

Posting Permissions

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