I need the equivalent of this Count with Case for Firebird 3 database

Question

I need the equivalent of this Count with Case for a Firebird 3 database. I get an error when I try it:

SQL error code = -104.
Invalid usage of boolean expression.

I was just recently introduced to the Case command and I can't seem to rework it myself. I managed to get it to work with SQLite just fine.

The intent is to do an AND operation, the Where can't do an AND because the keywords are in rows.

SELECT Count((CASE WHEN keywords.keyword LIKE '%purchased%'
              THEN 1 END) AND
             (CASE WHEN keywords.keyword LIKE '%item%'
              THEN 1 END)) AS TRows
FROM products
     LEFT OUTER JOIN keywords_products ON
       products.product_rec_id = keywords_products.product_rec_id
     LEFT OUTER JOIN keywords ON
       keywords_products.keyword_rec_id = keywords.keyword_rec_id
WHERE (keywords.keyword LIKE '%purchased%' OR
       keywords.keyword LIKE '%item%')

I have three SQLite tables, a products table, a keywords_products table, and a keywords table.

CREATE TABLE products (
    product_rec_id  INTEGER  PRIMARY KEY  NOT NULL,
    name            VARCHAR (100)  NOT NULL
);

CREATE TABLE keywords_products (
    keyword_rec_id INTEGER NOT NULL,
    product_rec_id INTEGER NOT NULL
);

CREATE TABLE keywords (
    keyword_rec_id INTEGER  PRIMARY KEY  NOT NULL,
    keyword        VARCHAR (50)  NOT NULL  UNIQUE
);

The keywords_products table holds the the record id of a product and a record id of a keyword. Each product can be assigned multiple keywords in the keywords table.

The keyword table looks like this:

keyword_rec_id  keyword
--------------  -----------
60              melee
43              scifi
87              water

The keywords_products table looks like this (one keyword can be assigned to many products):

keyword_rec_id  product_rec_id
--------------  --------------
43              1
60              1
43              2
87              3

The products table looks like this:

product_rec_id  name
--------------  --------------
1               Scifi Melee Weapons
2               Scifi Ray Weapon
3               Lake House

Answer 1

You have to use ONE single CASE expression with multiple WHEN branches.

Making Boolean functions of distinct CASE expressions just makes no sense - the CASE is not Boolean function itself.

You can see rules and an example at CASE.

case
  when Age >= 18 then 'Yes'
  when Age <  18 then 'No'
end;

Remake you two CASE clauses to a single CASE clause following this pattern.

---

However, you only use CASE when you can not move filters and conditions into standard part of SQL select. Normal approach would be to minimize data that SQL engine has to fetch, using pre-filtering. The CASE uses post-filtering, it makes SQL engine to fetch all the data, regardless if it needs it or not, and then discard the unneeded fetched data. That is redundant work slowing down the process.

In your case you already extracted the condition into WHERE clause, that is good.

SELECT
...

WHERE (keywords.keyword LIKE '%purchased%') 
   OR (keywords.keyword LIKE '%item%') 

Since you pre-filter your data stream to always contain "item" or "purchase" then the CASE clause of yours would always return 1 on all rows selected under this WHERE pre-filtering. Hence - just remove the redundant CASE clause and put "1" instead.

SELECT Count(1)
FROM products
  LEFT JOIN keywords_products ON products.product_rec_id = keywords_products.product_rec_id
  LEFT JOIN keywords ON keywords_products.keyword_rec_id = keywords.keyword_rec_id
WHERE (keywords.keyword LIKE '%purchased%') 
   OR (keywords.keyword LIKE '%item%') 

---

Now, given that WHERE clause is processed logically after JOINing, this your query de facto transformed LEFT JOINs into FULL JOINs ( your WHERE clause just discards rows with NULL "keyword" column values ) but aghain in unreliable and inefficient method. Since you do not want to have "keyword is NULL" kind of rows anyway - just convert your left joins to normal joins.

Answer 2

I'm assuming you want to count how many rows there are where both conditions are true.

The error occurs because you can't use AND between integer values. The values must be true booleans.

So, change your code to

Count((CASE WHEN keywords.keyword LIKE '%purchased%'
          THEN TRUE END) AND
         (CASE WHEN keywords.keyword LIKE '%item%'
          THEN TRUE END))

However that is far too complex. You can simplify your expression to

count(nullif(
    keywords.keyword LIKE '%purchased%' and keywords.keyword LIKE '%item%',
    false))

The use of NULLIF is needed because COUNT will count all non-NULL values (as required by the SQL standard), and false is non-NULL as well. So to achieve the (assumed) desired effect, we transform false to NULL using NULLIF.