Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2011
    Posts
    82

    Unanswered: Syntex that still requires last weeks data

    Hello

    I have a piece of syntex which has been working perfectly, up until the new financial year (Today)

    My SQL is:

    decode(to_char(sysdate, 'Q'),
    '1', trunc(add_months(sysdate, -9), 'Q'),
    add_months(trunc(sysdate, 'YYYY'), 3))
    AND next_day(sysdate,'SUNDAY')-7


    This has been picking up the last financial year up until the last Sunday. However when i ran this data today 02/04/2013 our new financial year started on 01/04/2013 and now I get an error message:

    Error: ORA-00920: invalid relational operator
    (State1000, Native Code: 398)

    As last Sunday was 31/03/2013 what I had hoped to show was a full year of data, but am returning 0 values

    Could anyone help me rewrite the correct syntex so that I capture the entire financial year? and inaddition I need to know whether my above syntex will work when i run it next week and will pick up just April 2013 data?

    01/04/2012 - 31/03/2013

    Thanks
    Helen

  2. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Post the complete statement, That is not a valid statement, looks like you are trying to see if a date is between two dates, something like this:
    Code:
    select 'something'
     from  'some_table(s)'
    where  'some_date' between
           decode( to_char( sysdate, 'Q' ),
                   '1',
                   trunc( add_months( sysdate, -9 ), 'Q' ),
                   add_months( trunc( sysdate, 'YYYY' ), 3 ) )
     and   next_day( sysdate, 'SUNDAY' ) - 7
    You can run the below statement to see what each result in your 'decode' statement will be:
    Code:
    select to_char( sysdate, 'Q' ) as quarter, trunc( add_months( sysdate, -9 ), 'Q' ) as a_prv_quarter, add_months( trunc( sysdate, 'YYYY' ), 3 ) as yyyy,
           decode( to_char( sysdate, 'Q' ), '1', trunc( add_months( sysdate, -9 ), 'Q' ), add_months( trunc( sysdate, 'YYYY' ), 3 ) ),
           next_day( sysdate, 'SUNDAY' ) - 7
     from  dual

Posting Permissions

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