[SalesForce] SQL query case when like

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.

Best Answer

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
Related Topic