It looks like the result of an IF statement is not case insensitive

SELECT * FROM
(
SELECT
EXTRACT(HOUR FROM _Timestamp) AS _Hour,
CAST(
CAST( EXTRACT(HOUR FROM _Timestamp) AS VARCHAR(2))+
IF(EXTRACT(MINUTE FROM _Timestamp) <=15 THEN ':00'
ELSE IF(EXTRACT(MINUTE FROM _Timestamp) <=30 THEN ':15'
ELSE IF(EXTRACT(MINUTE FROM _Timestamp) <=45 THEN ':30'
ELSE ':45')))
AS TIME) AS _Instance,
IF(_mmolPl >= 7.9 THEN 'Above' ELSE IF(_mmolPl <=3.9 THEN 'Below' ELSE 'Normal')) AS _Band,
COUNT(*) AS _Freq
FROM BloodTests
WHERE
_Testpoint = 'before breakfast'
AND
EXTRACT(HOUR FROM _Timestamp) BETWEEN 4 AND 8
GROUP BY
_Instance, IF(_mmolPl >= 7.9 THEN 'Above' ELSE IF(_mmolPl <=3.9 THEN 'Below' ELSE 'Normal'))
)X
where _band COLLATE ANSI_CI = 'above' <<<<<<< the collate ansi_ci is needed otherwise there are no rows
ORDER BY _Instance DESC

Work round is easy but since the dialog in EDBManager defaults VARCHARs to case insensitive I was surprised to encounter this.

What's the opinion - bug or WAD?

Roy Lambert

Roy,

<< Work round is easy but since the dialog in EDBManager defaults VARCHARs to case insensitive I was surprised to encounter this. >>

The EDB Manager's behavior is governed by the Preferences/Dialogs/Default Collation Settings.

For SQL, the collation is determined by the source column(s), and if there aren't any, then it is set to the default collation for the session (ANSI or Unicode).

Tim Young
Elevate Software
www.elevatesoft.com

Tim


Now I know about that (must spend time seeing what you've done to Preferences since I last looked I've checked). Mine are ANSI & case-insensitive and I've just checked all test columns in the database and all are set to ANSI_CI.

Roy Lambert

Roy,

<< Now I know about that (must spend time seeing what you've done to Preferences since I last looked I've checked). Mine are ANSI & case-insensitive and I've just checked all test columns in the database and all are set to ANSI_CI. >>

The only string expressions driving the _band SELECT expression are these:

'Above'
'Below'
'Normal'

None of which are actual columns, hence the absence of any _CI modifiers and the reason for what you're seeing.

Tim Young
Elevate Software
www.elevatesoft.com

Tim


I now see where my confusion came from - there is the session character set and the default collation settings as specified in File | Preferences | Dialogs.

It would make more sense to me to use the default collation settings but its not going to happen very often so its no big deal.

Thanks for clarifying

Roy Lambert