Got this worked out now and in fact it looks a big case statement is a bit
faster than the other methods. I also found that I don't need the CAST AS
INTEGER:
UPDATE A2ID965_J
SET DATE_OF_BIRTH =
CASE (DATE_OF_BIRTH / 100) % 100
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
END
This is about 20% faster than a table lookup, even when the lookup table is
already present.
So, learned something useful there.
RBS
-----Original Message-----
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 19 February 2007 08:04
To: Bart Smissaert2
Subject: FW: [sqlite] Re: What is wrong with this SELECT CASE statement?
-----Original Message-----
From: Igor Tandetnik [mailto:[EMAIL PROTECTED]
Sent: 19 February 2007 04:54
To: SQLite
Subject: [sqlite] Re: What is wrong with this SELECT CASE statement?
RB Smissaert <[EMAIL PROTECTED]>
wrote:
> Trying to update my yyyymmdd integers to months with a SELECT CASE
> statement:
>
> SELECT CASE
> (CAST(DATE_OF_BIRTH / 100 AS INTEGER) - CAST(DATE_OF_BIRTH / 10000 AS
> INTEGER) * 100)
> WHEN 1 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'January'
> WHEN 2 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'February'
> WHEN 3 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'March'
> WHEN 4 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'April'
> WHEN 5 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'May'
> WHEN 6 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'June'
> WHEN 7 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'July'
> WHEN 8 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'August'
> WHEN 9 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'September'
> WHEN 10 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'October'
> WHEN 11 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'November'
> WHEN 12 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'December'
> END
> FROM A2IDC21_J
>
> But no updates take place.
UPDATE is a statement, not an expression. It cannot appear nested in
another statement. You want
UPDATE A2IDC21_J SET DATE_OF_BIRTH =
CASE (CAST(DATE_OF_BIRTH / 100 AS INTEGER) -
CAST(DATE_OF_BIRTH / 10000 AS INTEGER) * 100)
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
...
END
Also, the expression in the CASE can be simplified to
CAST(DATE_OF_BIRTH AS INTEGER) / 100 % 100
Igor Tandetnik
----------------------------------------------------------------------------
-
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------------
-
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
