'Writing a query for alternative column names as mentioned below eg

The query for, If the address is missing, displays the email id. If both address and email are missing then display ‘NA’. Give an alias name as CONTACT_DETAILS. Considering the above-mentioned example, please help with writing the query.

SELECT 
  CASE WHEN 1 THEN ifnull(address,email_id)  
       WHEN 2 THEN ifnull(email_id,'NA')
  END
as contact_details
FROM customers;

The query which i tried

sqlif-statementnullconditional-statements


Solution 1:[1]

You can use COALESCE 

SELECT COALESCE(address,email_id, 'NA') contact_details FROM customers;

From Documentation:

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

Solution 2:[2]

You can use COALESCE function. Your query would be something like this

SELECT COALESCE(address, email_id, 'NA') as contact_details
FROM customers;

As far i have checked its available in oracle and mysql. Use these links for reference:

  1. https://www.geeksforgeeks.org/mysql-coalesce-function/
  2. https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions023.htm#i1001341

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Digvijay S
Solution 2 targhs

Related Questions