How to update column (ISO_WEEK) in TOAD Oracle? [1 Attachment]

Toad for Oracle
1

[ Attachment(s) from Buenvenida included below]

Message from: Ranseru

Hi

I have this set of data below in our oracle table:

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

And I want to update to this one 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 2009W53
28/12/2009 2009W53
29/12/2009 2009W53
30/12/2009 2009W53
31/12/2009 2009W53

Here is my script I used but currently it didn’t work.

UPDATE (select COUNT ( ISO_WEEK_IN_YEAR ) from AW_WAREHOUSE_TIME_DIM2 )

SET ISO_WEEK = ISO_YEAR || ‘WK53’

WHERE

ISO_WEEK = 52 AND

MONTH_OF_CALENDER_YEAR = 12 AND

DAY_OF_CALENDER_MONTH

COUNT ( ISO_WEEK_IN_YEAR ) > 7

Can anyone help me on this one?
Your response is highly appreciate

Historical Messages

Author: Buenvenida
Date: Thu Feb 03 06:09:33 PST 2011
Hi

I have this set of data below in our oracle table:

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

And I want to update to this one 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 2009W53
28/12/2009 2009W53
29/12/2009 2009W53
30/12/2009 2009W53
31/12/2009 2009W53

Here is my script I used but currently it didn’t work.

UPDATE (select COUNT ( ISO_WEEK_IN_YEAR ) from AW_WAREHOUSE_TIME_DIM2 )

SET ISO_WEEK = ISO_YEAR || ‘WK53’

WHERE

ISO_WEEK = 52 AND

MONTH_OF_CALENDER_YEAR = 12 AND

DAY_OF_CALENDER_MONTH

COUNT ( ISO_WEEK_IN_YEAR ) > 7

Can anyone help me on this one?
Your response is highly appreciate
__

Attachment(s) from Buenvenida

1 of 1 File(s)

yearend.zip

2

Ranseru,

I'm running on a few assumptions here but this is what I see with the
information you provided

table AW_WAREHOUSE_TIME_DIM2
( ...
CALENDAR_DATE DATE
ISO_WEEK VARCHAR2(7)
);

I'm guessing CALENDAR_DATE is a DATE and ISO_WEEK is a VARCHAR2 datatype

If so then wouldn't this work

UPDATE AW_WAREHOUSE_TIME_DIM2
SET ISO_WEEK = to_char(CALENDAR_DATE,'YYYY')|| 'WK53'
WHERE ISO_WEEK = '2009W52'
AND CALENDAR_DATE BETWEEN to_date('27/12/2009','dd/mm/yyyy') AND
to_date('31/12/2009','dd/mm/yyyy');

I'm not sure what MONTH_OF_CALENDER_YEAR, DAY_OF_CALENDER_MONTH and
ISO_WEEK_IN_YEAR are. Are they real columns in your table in addition
to CALENDAR_DATE and ISO_WEEK?

Ed
[TeamT]

On 2/3/2011 9:09 AM, Buenvenida wrote:

[Attachment(s) from Buenvenida included below]

Message from: Ranseru

Hi

I have this set of data below in our oracle table:

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

And I want to update to this one 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 2009W53
28/12/2009 2009W53
29/12/2009 2009W53
30/12/2009 2009W53
31/12/2009 2009W53
*
Here is my script I used but currently it didn't work.

UPDATE(selectCOUNT(ISO_WEEK_IN_YEAR)fromAW_WAREHOUSE_TIME_DIM2)

SETISO_WEEK =ISO_YEAR || 'WK53'

WHERE

ISO_WEEK =52AND

MONTH_OF_CALENDER_YEAR =12AND

DAY_OF_CALENDER_MONTH

COUNT(ISO_WEEK_IN_YEAR)>7

Can anyone help me on this one?
Your response is highly appreciate

3

Afternoon,

does this work?

UPDATE AW_WAREHOUSE_TIME_DIM2)
SET ISO_WEEK = ‘2009WK53’
WHERE
ISO_WEEK=‘2009WK52’
AND to_date(calendar_date, ‘dd/mm/yyyy’) between to_date(‘27/12/2009’,
'dd/mm/yyyy) and to_date(‘31/12/2009’,‘dd/mm/yyyy’);

I’m assuming that the calendar_date is a varchar2 and not currently a
date column. If it is a date then:

…
AND calendar_date between to_date(‘27/12/2009’, 'dd/mm/yyyy) and
to_date(‘31/12/2009’,‘dd/mm/yyyy’);

–
Cheers,
Norm. [TeamT]