Referencing a column in an SQLite case statement

I have an SQLite table called table. It has 4 columns: col_1, col_2, col_3, col_4.

If I was to use MySQL as a way of explaining what I want to do, it is something like this:

SELECT CASE
WHEN col_4 IS NOT NULL THEN col_4,
WHEN col_3 IS NOT NULL THEN (CONCAT COL_3 and COL_2)
ELSE (CONCAT STRING + col_2)
END AS clue
    FROM TABLE

Essentially, what I want to do is say, pick the value of col_4 if there is a value there. If there is nothing, then check if there is a value in col_3. If there is, then concatenate the values of col_3 and col_2. If this is also null, then concatenate a string with the value of col_2 (which will never be null, as per how I’ve designed it.

To try and get this, I wrote the following SQLite query:

select CASE col_4
WHEN NOT NULL col_4 
ELSE CASE col_3 
WHEN NOT NULL col_3 || ' - ' || col_2  
ELSE'String: ' || col_2 END AS clue 
FROM table

But when running it in an SQLite view online, it fails at the very first case statement.

It doesn’t let me do CASE col_4 WHEN NOT NULL col_4, i.e return a value from a column within in my statement. It’s making me write a specific string, which is not what I want.

Is there a way around this that I am missing?

Answer

The syntax you use for CASE is wrong.
Change it to this:

SELECT CASE
    WHEN COL_4 IS NOT NULL THEN col_4,
    WHEN COL_3 IS NOT NULL THEN COL_3 || '-' || COL_2
    ELSE 'String: ' || COL_2
  END AS clue
FROM TABLE

Keep in mind that the the result of the concatenation of 2 values is NULL if any of the 2 values is NULL.
So if COL_2 is NULL the result of the last 2 branches of the CASE expression will also be NULL.

If COL_2 can’t be NULL then the code can be simplified to this:

SELECT COALESCE(COL_4, COL_3 || '-' || COL_2, 'String: ' || COL_2)
FROM TABLE