CODEWITHSUNDEEP
sqlif-statementcase
Mischa Mustermann
Mischa Mustermann

Reputation: 91

SQL Case / if value from other CASE is empty

I have a SQL query where is have for a name a german and a english translation available. Based on the user settings and the existing data for translation i want to define the SQL query output. In my example the CASE for choosing the language based on the user setting is working but the second CASE function is not working because it does not know the definition from the first CASE function.

I am working on XAMPP v3.2.4 / Server-Version: 10.4.11-MariaDB

Here is my try:

SELECT questionaire.quest_name_de, questionaire.quest_name_en,

# CASE 1: check what language settings the user haves selected
CASE :language WHEN 'de' THEN questionaire.quest_name_de ELSE questionaire.quest_name_en END AS quest_name_translation,

# CASE 2: if the output is empty take german translation which is never empty
CASE quest_name_translation WHEN '' THEN questionaire.quest_name_de ELSE quest_name_translation END AS quest_name_final
 
FROM questionaire

My desired output is as following:

The user language settings is englisch so the first CASE function in my query will select 'questionaire.quest_name_en'. As next the second CASE function will check if the data for 'quest_name_translation' is not empty. If its empty then choose 'questionaire.quest_name_de'. My problem is that the second CASE does not know 'quest_name_translation' from the first CASE. is this method not possible?

Upvotes: 0

Views: 2273

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

Use AND to combine conditions. Only if English is requested and English is available will you show the English text, otherwise German:

SELECT
  questionaire.quest_name_de,
  questionaire.quest_name_en,
  CASE WHEN :language = 'en' AND quest_name_en <> '' AND quest_name_en IS NOT NULL
    THEN quest_name_en
    ELSE quest_name_de
  END AS quest_name_final
FROM questionaire;

Upvotes: 1

Related Questions