RE: [sqlite] Re: What is wrong with this SELECT CASE statement?

[RB Smissaert]

Thanks, I understand now.
Will fix it and see how it compares the other methods.

RBS

-----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]
-----------------------------------------------------------------------------