How can this be re-written? somehow not able to make it right. In case statement THEN and ELSE are the same would like to change it to a single statement.

SELECT    t.status_id status_id, (
CASE WHEN t .status_id NOT IN (SELECT     status_id  FROM  firm_return   WHERE      enabled = 'D' and firm_id=t.firm_id) 

THEN (cl.category_code + '-' + s.status_name)   ELSE cl.category_code + '-' + s.status_name END)  AS status_type
   FROM       bla bla 
New contributor
fabkhush is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • You have to specify column name immediately after case keyword. Please google once before posting question here --> sqlshack.com/case-statement-in-sql – Learning_DBAdmin Jan 12 at 6:25
  • ??? Both alternatives are absolutely the same, so CASE is excess, and this is an equivalent of simple SELECT t.status_id, cl.category_code + '-' + s.status_name AS status_type FROM ... (except query falling when firm_return table is not accessible). – Akina Jan 12 at 6:56
  • Its hard to add firm_return in where due to lot many joins. Any good way to achieve above? – fabkhush Jan 12 at 7:02
  • Do a LEFT JOIN on firm_return and in the SELECT results have a IF(firm_return.status_id IS NULL, ..., .....) as status_type (missed this was sql-server - maybe doesn't have an IF expression however CASE of a similar concept) – danblack Jan 12 at 8:25

Your example query appears to be incomplete (e.g. your aliases t and cl don't correspond to anything in the example). Ignoring that for a second and using your example query, this is probably what you're after:

SELECT t.status_id AS status_id, cl.category_code + '-' + s.status_name AS status_type
FROM SomeTable AS t
LEFT JOIN firm_return AS s
    ON t.firm_id = s.firm_id
    AND s.enabled = 'D'
... -- The rest of your query logic here (e.g. the table for the cl alias)

If you care about NULLS and want to handle them with a default value then you can use the ISNULL() function around the logic for your status_type column like so: ISNULL(cl.category_code + '-' + s.status_name, 'SomeDefaultValueGoesHere') AS status_type

Not the answer you're looking for? Browse other questions tagged or ask your own question.