Hello,
I have a problem executing a query that uses concat / pipes in WHERE-clause. Since the query returns null if only one of the fields is null, I added a CASE WHEN statement to check if the field is empty.
Like this:
SELECT * FROM mitgl_0 WHERE ( CASE WHEN mitgl_name is null THEN '- unknown -' ELSE mitgl_name END||CASE WHEN mitgl_vorname is null THEN '- unknown -' ELSE mitgl_vorname END||CASE WHEN mitgl_strasse is null THEN '- unknown -' ELSE mitgl_strasse END||CASE WHEN mitgl_plz is null THEN '- unknown -' ELSE mitgl_plz END||CASE WHENmitgl_ort is null THEN '- unknown -' ELSE mitgl_ort END||CASE WHEN mitgl_bemerkungen is null THEN '- unknown -' ELSE mitgl_bemerkungen END LIKE '%Mario%' )
My problems come up when having a BLOB field (only text) in the table.
Using the statement above the query returns an error telling me that datatype (BLO😎 is not allowed in CASE statement. I checked that and this is true.
Excluding the BLOB-field from CASE statement returns no error but also returns no matches on searches where the BLOB is empty even if one of the other fields contains a match on a searched keyword, which is normal when concatenating fields without handling null-filelds.
LIKE this:
SELECT * FROM mitgl_0 WHERE ( CASE WHEN mitgl_name is null THEN '- unknown -' ELSE mitgl_name END||CASE WHENmitgl_vorname is null THEN '- unknown -' ELSE mitgl_vorname END||CASE WHEN mitgl_strasse is null THEN '- unknown -' ELSE mitgl_strasse END||CASE WHEN mitgl_plz is null THEN '- unknown -' ELSE mitgl_plz END||CASE WHENmitgl_ort is null THEN '- unknown -' ELSE mitgl_ort END||mitgl_bemerkungen LIKE '%Mario%' )
// mitgl_bemerkungen is the BLOB field
Is there anybody out there who knows how to make it work?
Many thanks in advance
cheers
gaucho
P.S.: I need this to work without fulltext search functionality from MySQL.
