Problem with Syntax CASE WHEN END

Databases
1

Hi there, I need your help.

With this sql query I need this output:

+--------+--------------------+
| Xtr    | Codigo_FLGHT       |
+--------+--------------------+
| RAS_SD | Zone FlightCA      |
| RAS_SD | Zone FlightN       |
| RAS_SD | Zone FlightOR      |
| RAS_SD | Zone FlightS       |
| RAS_SD | Zone FlightO       |
| RAS_SD | Xtr_SAR            |
+--------+--------------------+

But I have in column Codigo_FLGHT ALL null values.

Can you help me?

Please check my query below.

Thanks in advance.

SELECT
	Xtr,
	CASE
WHEN INSTR(Codigo_FLGHT, 'Zone' > 0) THEN
	Codigo_FLGHT = MID(Codigo_FLGHT, 6, 100)
WHEN INSTR(Codigo_FLGHT, 'Xtr' > 0) THEN
	Codigo_FLGHT = Codigo_FLGHT
END Codigo_FLGHT
FROM
	tbl_myflight
WHERE
	1
AND INSTR(Codigo_FLGHT, 'Zone')
AND (Xtr LIKE 'RAS_SD%')
GROUP BY
	Codigo_FLGHT;
2

change this (which is hard to read) –

CASE
WHEN INSTR(Codigo_FLGHT, 'Zone' > 0) THEN
	Codigo_FLGHT = MID(Codigo_FLGHT, 6, 100)
WHEN INSTR(Codigo_FLGHT, 'Xtr' > 0) THEN
	Codigo_FLGHT = Codigo_FLGHT
END Codigo_FLGHT

to this (which also contains your syntax corrections) –


CASE WHEN INSTR(Codigo_FLGHT, 'Zone' > 0) 
     THEN MID(Codigo_FLGHT, 6, 100)
     WHEN INSTR(Codigo_FLGHT, 'Xtr' > 0) 
     THEN Codigo_FLGHT
 END AS Codigo_FLGHT

the THEN and ELSE portions of the CASE expression must specify values, whereas you were specifying true or false results of an equality comparison

be aware that if you do not specify an ELSE value, then NULL is the result if all the WHEN tests fail

and you said that all your results are showing NULLs, so that means both of your WHEN tests are failing

ask yourself what might be causing INSTR(Codigo_FLGHT, ‘Zone’ > 0) and INSTR(Codigo_FLGHT, ‘Xtr’ > 0) to fail

if you can’t see it, you’ll need to look up the syntax of the INSTR() function and examine it very carefully

:slight_smile:

3

Now I can, thank you for help :slight_smile:

SELECT
	Xtr,
	CASE
WHEN INSTR(Codigo_FLGHT, 'Zone') > 0 THEN
	MID(Codigo_FLGHT, 6, 100)
WHEN INSTR(Codigo_FLGHT, 'Xtr') > 0 THEN
	Codigo_FLGHT
END AS Codigo_FLGHT
FROM
	tbl_myflight
WHERE
	1
AND (
	INSTR(Codigo_FLGHT, 'Zone')
	OR INSTR(Codigo_FLGHT, 'Xtr')
)
AND (Xtr LIKE 'RAS_SD%')
GROUP BY
	Codigo_FLGHT;

+--------+--------------------+
| Xtr    | Codigo_FLGHT       |
+--------+--------------------+
| RAS_SD | Zone FlightCA      |
| RAS_SD | Zone FlightN       |
| RAS_SD | Zone FlightOR      |
| RAS_SD | Zone FlightS       |
| RAS_SD | Zone FlightO       |
| RAS_SD | Xtr_SAR            |
+--------+--------------------+