AdministratorFebruary 6, 2021 at 6:13 pm
The COALESCE function in SQL returns the first non-NULL expression among its arguments. It is the same as the following CASE WHEN statement:
CASE ("column_name") WHEN "expression 1 is not NULL" THEN "expression 1"
WHEN "expression 2 is not NULL" THEN "expression 2"
For examples, say we have the following table Table Contact_Info
Name Business_Phone Cell_Phone Home_Phone
Jeff 531-2531 622-7813 565-9901
Laura NULL 772-5588 312-4088
Peter NULL NULL 594-7477
and we want to find out the best way to contact each person according to the following rules:
1. If a person has a business phone, use the business phone number.
2. If a person does not have a business phone and has a cell phone, use the cell phone number.
3. If a person does not have a business phone, does not have a cell phone, and has a home phone, use the home phone number.
We can use the COALESCE function to achieve our goal:
COALESCE(Business_Phone, Cell_Phone, Home_Phone) as Contact_Phone
They do the same job as the below SAS code:
If Business_phone is not null then contact_phone=business_phone;
Else if cell_phone is not null then contact_phone=cell_phone;
Else if home_phone is not null then contact_phone=home_phone;
Isn’t it cool? Then practice and use it!
AdministratorFebruary 6, 2021 at 6:09 pm
The NVL() function is available in Oracle, and not in MySQL or SQL Server. This function is used to replace NULL value with another value. It is similar to the IFNULL Function in MySQL and the ISNULL Function in SQL Server.
For example, if we have the following table:
Store A 300
Store B NULL
Store C 150
The following SQL code:
SELECT SUM (NVL(Sales, 100)) FROM Sales_Data;
This is because NULL has been replaced by 100 via the ISNULL function, hence the sum of the 3 rows is 300 + 100 + 150 = 550.
Please note: NVL function works with both character and numeric variables.
*** Equivalent to this in SAS: If missing(sales) then sales= 0;
NVL(Name, “Not available”);
*** Equivalent to this in SAS: If missing(names) then names=”Not available”;
Log in to reply.