SQL CASE How do I correct assign a value?-CodePudding

First, I would like to say I have already spent the due time and diligence on my part by watching videos, using other sources, and reading other posts and answers on SOF before asking this and have been unable to find a solution.

The issue I am running into, in a particular case, is a certain type is being passed in, which would require the use of LIKE as the specific type itself will not match anything as three types use the one type, say 'painting' in this situation. The database has a 'painting small' and 'painting large.'

Code

// I tried this
CASE WHEN type = 'painting' THEN inventory.type LIKE '%' type '%' ELSE inventory.type = type END

I keep running into the "An expression of a non-boolean type specified in a context where a condition is expected. There are a few other variations I have tried as well as IF ELSE, however, I run into the same issue. Someone else may be able to word this question better.

I mainly want to be pointed in the right direction and receive clarification on what I am doing wrong.

Thank you

CodePudding user response：

There are a few problems with your query. Rather than the CASE expression itself I'm going to address the less obvious problem, your lack of prefixing. Take this clause:

inventory.type LIKE '%' type '%'

This could likely either error, due to an ambiguous column name, or resolve to inventory.type LIKE '%' inventory.type '%'; obviously the latter is going to always be true unless the column type has the value NULL. Always prefix your column names, especially when your query contains 2 tables.

As for the actual problem, this is presumably part of a WHERE, therefore use OR and AND logic:

WHERE (({Other Table Prefix}.[type] = 'painting' AND inventory.[type] LIKE '%'   {Other Table Prefix}.[Type]   '%')
   OR  ({Other Table Prefix}.[type] != 'painting' AND inventory.[type] = {Other Table Prefix}.[Type]))

Obviously, you need to appropriately replace {Other Table Prefix} with the correct prefix.

CodePudding user response：

The problem seems to be in the

   LIKE '%' type '%'

where LIKE may be returning a boolean value.