How to update column (ISO_WEEK) in TOAD Oracle?

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • PL Buenvenida
    New Member
    • Jan 2011
    • 3

    How to update column (ISO_WEEK) in TOAD Oracle?

    Hi All,

    Good day.

    We have a job in BODI that is currently populating this current table and we want to update column (ISO_WEEK) in the attached file, I highlight the affected rows in yellow there are correct and the red are the one need to update in 2009W53-2010W53.

    Here is my script that I use and it still missing something?

    UPDATE AW_WAREHOUSE_TI ME_DIM2
    SET ISO_WEEK = ISO_YEAR||'WK53 '
    WHERE
    ISO_WEEK_IN_YEA R = 52 AND
    MONTH_OF_CALEND ER_YEAR = 12 AND
    DAY_OF_CALENDER _MONTH <= 31

    Anyone inputs is highly appreciated.

    Thanks,
    Piery Lancer J. Buenvenida
    Attached Files
  • amitpatel66
    Recognized Expert Top Contributor
    • Mar 2007
    • 2358

    #2
    Are you facing any issue? can you post the error that you are facing?

    Comment

    • PL Buenvenida
      New Member
      • Jan 2011
      • 3

      #3
      Currently the script that I using has no error rather it update all week52 to week53 and that is wrong.

      The issue on our side is when year end arrives and BODI jobs failed due to calculation of ISO_WEEK is wrong.

      I set an example the year 2009 it has an excess week52 (WK52 range Dec 20-26). Please see detail below:

      CALENDAR_DATE ISO_WEEK

      20/12/2009 2009W52
      21/12/2009 2009W52
      22/12/2009 2009W52
      23/12/2009 2009W52
      24/12/2009 2009W52
      25/12/2009 2009W52
      26/12/2009 2009W52
      27/12/2009 2009W52
      28/12/2009 2009W52
      29/12/2009 2009W52
      30/12/2009 2009W52
      31/12/2009 2009W52

      I wanted to do is everytime it has an excess week52 per year it will update ISO_WEEK to 20**W53

      Comment

      • PL Buenvenida
        New Member
        • Jan 2011
        • 3

        #4
        Here is my new script but still it encounter an error saying ORA-00933: SQL command not properly ended

        UPDATE AW_WAREHOUSE_TI ME_DIM2
        SET ISO_WEEK = '2009WK53' FROM (select COUNT(ISO_WEEK_ IN_YEAR) from AW_WAREHOUSE_TI ME_DIM2
        where
        ISO_YEAR = '2009' AND
        ISO_WEEK_IN_YEA R = '52'
        WHERE
        ISO_WEEK = 52 AND
        MONTH_OF_CALEND ER_YEAR = 12 AND
        DAY_OF_CALENDER _MONTH <= 31 AND
        COUND(ISO_WEEK_ IN_YEAR) > 7

        Comment

        Working...