CASE WHEN after THEN or Select value from other table

Question

I need to do this:

SELECT 
COLUMN1,
COLUMN2,
CASE SOMETHING WHEN 0 THEN 'SOMETHING'
   ELSE
      CASE SOMETHING1 WHEN 'SOMETHING2' THEN (Here I need my value from other table)
   ELSE
   ...
   ELSE
   ...
END
END
AS SOMETHINGSPECIAL
...
...
...

Entire select is horribly complicated sorry.

In the place after THEN in () I need to take out specific value from other table. I have tried almost everything there is from joins, to put there SELECT WHERE or CASE WHEN statement it always end up with some error. Keyword missing etc.

Also maybe problem is inside () there is long concatenate: ''

I need to put that specific value from other table into that concatenate. It either doesn't want to allow me to use other CASE WHEN after that THEN or I'm doing something wrong.

EDIT (sorry cant post entire query dont wanna have problems in work):

SELECT 
A.SOMETHING
CASE WHEN A.LIST_ID IN ('something','something') THEN '<A HREF="something?thresholdId=something' || GET_SITE_SUFFIX() || chr(38) || 'task=runSQL' || chr(38) || 'parseParams=true' || chr(38) || 'list_id=' || A.LIST_ID || chr(38) || 'list_name=' || A.LIST_NAME || '"> (MY VALUE FROM OTHER TABLE HERE) </A>'
END 
AS SOMETHINGSPECIAL
FROM SOMETABLE
...

(MY VALUE FROM OTHER TABLE HERE) I tried to put there Select statement condition, Case statement to take out that one value from other table but it just gives me error.

Answer 1

You can use a correlated subquery:

(CASE SOMETHING
     WHEN 0 THEN 'SOMETHING'
     ELSE (CASE SOMETHING1
               WHEN 'SOMETHING2' THEN (select value from othertable ot where ot.col = x.col)
               ELSE . . .

Do note that you don't need nested cases. You could write this as:

(CASE WHEN SOMETHING = 0 THEN 'SOMETHING'
      WHEN SOMETHING1 = 'SOMETHING2' THEN (select value from othertable ot where ot.col = x.col)
      ELSE . . .
 END)