mysql - MariaDB/Mysql SELECT on CASE Statement inside CAST causes an error

Question

I'm working with a frustrating set of data that contains null, 0, 1, and 2 values for the 'active' column in my users table.

Why does this work:

SELECT u.id                                            AS LegacyContactKey,
       u.first_name                                    AS FirstName,
       u.last_name                                     AS LastName,
       c.company_name                                  AS CompanyName,
       u.email                                         AS EmailAddress,
       (CASE WHEN u.active = 1 THEN 1 WHEN u.active = 2 THEN 1 ELSE 0 END) as IsMember
FROM tb_users AS u
    INNER JOIN tb_company AS c ON u.company_id = c.company_id

But not this?

SELECT u.id                                            AS LegacyContactKey,
       u.first_name                                    AS FirstName,
       u.last_name                                     AS LastName,
       c.company_name                                  AS CompanyName,
       u.email                                         AS EmailAddress,
       CAST((CASE WHEN u.active = 1 THEN 1 WHEN u.active = 2 THEN 1 ELSE 0 END) AS BOOLEAN) as IsMember
FROM tb_users AS u
    INNER JOIN tb_company AS c ON u.company_id = c.company_id

I want to cast one of my query results as a boolean so it returns true or false but when I add CAST() I get an error (near BOOLEAN)

Running MariaDB 10.3

标签： mysqlsqlcastingmariadb-10.3

Answer 1

我认为不cast()支持布尔值。但最简单的方法真的是：

   (u.active IN (1, 2)) as IsMember

您可以只使用布尔表达式并为其分配列别名。

编辑：

如果NULL是一个问题，只需将其包含在表达式中：

   (u.active IN (1, 2) AND u.active IS NOT NULL) as IsMember