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