SQL query case when like

Question

I have 2 tables:

TABLE_1
PRODUCT | CATEGORIES_ID
A               | 1,4,115,3
B               |1,4,11,3
C               |1,4,3
D               |115,78

TABLE_2
PRODUCT | LAST_MINUTE
A               | true
B               | false
C               | false
D               | true

The field categories_id are a list of number separated by a coma. Each time I find the number 115 I want to push "true" in the last_minute field of my second table.

I have written the following code:

SELECT P.PRODUCT, P.CATEGORIES_ID,    
CASE WHEN (P.CATEGORIES_ID
LIKE '%,115,%' OR P.CATEGORIES_ID LIKE '115,%' OR P.CATEGORIES_ID LIKE
'%,115') THEN '1'       
ELSE '0' 
END AS M.LAST_MINUTE FROM TABLE_1 AS P
JOIN TABLE_2 as M ON
P.PACKAGE_ID=M.ID_PACKAGE

I have an error message:

× An error occurred while checking the query syntax. Errors: Incorrect syntax near '.'.

But still do not understand why.

Answer 1

Seems like the issue appears to be that you are adding an unnecessary reference to your 'as' statement on the CASE.

If you change M.LAST_MINUTE to just LAST_MINUTE - the query should work.

The reason is that the table reference (tablename.field) is only valid for pulling information and CASE statements CREATE information - so putting a reference in the field name (as statement) will break the syntax.

QUERY:

SELECT P.PRODUCT, P.CATEGORIES_ID,
CASE 
  WHEN (P.CATEGORIES_ID LIKE '%,115,%' OR P.CATEGORIES_ID LIKE '115,%' OR P.CATEGORIES_ID LIKE '%,115')
  THEN '1'
  ELSE '0'
END AS LAST_MINUTE
FROM TABLE_1 AS P
JOIN TABLE_2 as M
ON P.PACKAGE_ID=M.ID_PACKAGE

Answer 2

@Emilie try like this

SELECT P.PRODUCT, P.CATEGORIES_ID,
CASE WHEN P.CATEGORIES_ID LIKE '%115%' OR P.CATEGORIES_ID LIKE '%115%' OR 
  P.CATEGORIES_ID LIKE '%115%' THEN '1'
  ELSE '0' END AS LAST_MINUTE FROM TABLE_1 AS P JOIN TABLE_2 as M ON 
    P.PACKAGE_ID=M.ID_PACKAGE

Answer 3

Try removing both "as" from the table alias

SELECT P.PRODUCT, P.CATEGORIES_ID,
CASE WHEN P.CATEGORIES_ID LIKE '%115%' OR
          P.CATEGORIES_ID LIKE '%115%' OR 
          P.CATEGORIES_ID LIKE '%115%' 
     THEN '1'
     ELSE '0' 
END AS LAST_MINUTE 
FROM TABLE_1 P 
JOIN TABLE_2 M ON P.PACKAGE_ID=M.ID_PACKAGE