How to update column (ISO_WEEK) in TOAD Oracle?

[PL Buenvenida]

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_TIME_DIM2
SET ISO_WEEK = ISO_YEAR||'WK53'
WHERE
ISO_WEEK_IN_YEAR = 52 AND
MONTH_OF_CALENDER_YEAR = 12 AND
DAY_OF_CALENDER_MONTH <= 31

Anyone inputs is highly appreciated.

Thanks,
Piery Lancer J. Buenvenida

[amitpatel66]

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

[PL Buenvenida]

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

[PL Buenvenida]

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

UPDATE AW_WAREHOUSE_TIME_DIM2
SET ISO_WEEK = '2009WK53' FROM (select COUNT(ISO_WEEK_IN_YEAR) from AW_WAREHOUSE_TIME_DIM2
where
ISO_YEAR = '2009' AND
ISO_WEEK_IN_YEAR = '52'
WHERE
ISO_WEEK = 52 AND
MONTH_OF_CALENDER_YEAR = 12 AND
DAY_OF_CALENDER_MONTH <= 31 AND
COUND(ISO_WEEK_IN_YEAR) > 7