SQL NULL Functions.

SQL ISNULL(), NVL(), IFNULL() and COALESCE() Functions.



Look at the following "Products" table:
P_IdProductNameUnitPriceUnitsInStockUnitsOnOrder
1Jarlsberg10.451615
2Mascarpone32.5623 
3Gorgonzola15.67920

Suppose that the "UnitsOnOrder" column is optional, and may contain NULL values.
We have the following SELECT statement:
SELECT ProductName,UnitPrice*(UnitsInStock+UnitsOnOrder)
FROM Products
In the example above, if any of the "UnitsOnOrder" values are NULL, the result is NULL.
Microsoft's ISNULL() function is used to specify how we want to treat NULL values.
The NVL(), IFNULL(), and COALESCE() functions can also be used to achieve the same result.
In this case we want NULL values to be zero.
Below, if "UnitsOnOrder" is NULL it will not harm the calculation, because ISNULL() returns a zero if the value is NULL:
MS Access
SELECT ProductName,UnitPrice*(UnitsInStock+IIF(ISNULL(UnitsOnOrder),0,UnitsOnOrder))
FROM Products
SQL Server
SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))
FROM Products
Oracle
Oracle does not have an ISNULL() function. However, we can use the NVL() function to achieve the same result:
SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0))
FROM Products
MySQL
MySQL does have an ISNULL() function. However, it works a little bit different from Microsoft's ISNULL() function.

In MySQL we can use the IFNULL() function, like this:
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products
or we can use the COALESCE() function, like this:
SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products

Comments

Post a Comment

Popular posts from this blog

Book ticket online through IRCTC using Selenium Webdriver.

Hi Guys, Everyday I try something new, So Today I will share code to book ticket online From IRCTC with the help of Selenium Webdriver. But before please! NOTE some points before Executing the code: All the CAPTCHA Field's have to be filled Manually because I didn't found how to auto fill it. After Searching trains all fields and links are generated dynamically so you have to Inspect the field every time. I have wrote this code to book ticket between NDLS(New Delhi) to CNB(Kanpur) Journey date June,13 You cannot inspect element by right clicking on element, press F12 and do it manually. This code will only take you to payment gateway after that you have to handle it manually. Now The Code is Below: import java.util.List; import java.util.concurrent.TimeUnit; import org.openqa.selenium.By; import org.openqa.selenium.Keys; import org.openqa.selenium.WebDriver; import org.openqa.selenium.WebElement; import org.openqa.selenium.firefox.FirefoxDriver; im
Read more

How to create a New Gmail Account using Selenium Web Driver.

package Signup_gmail; import org.apache.bcel.generic.Select; import org.apache.james.mime4j.message.Message; import org.openqa.selenium.By; import org.openqa.selenium.WebDriver; import org.openqa.selenium.WebElement; import org.openqa.selenium.firefox.FirefoxDriver; public class Sign_up { public static void main(String[] args) throws InterruptedException { WebDriver driver=new FirefoxDriver(); //Creating Object of firefox driver. driver.navigate().to("https://accounts.google.com/signup?hl=en-GB"); //Navigating to web page. driver.findElement(By.id("FirstName")).sendKeys(""); //Entering First Name. WebElement TxtBoxContent = driver.findElement(By.id("errormsg_0_LastName")); //Object creation of web element. TxtBoxContent.getText(); //Fetching value of error message through "TxtBoxContent" object. Object obj= new Object(); //Creating New Object. obj=driver.findElement(By.i
Read more

SQL Injection.

Image
An SQL Injection can destroy your database. SQL in Web Pages When SQL is used to display data on a web page, it is common to let web users input their own search values. Since SQL statements are text only, it is easy, with a little piece of computer code, to dynamically change SQL statements to provide the user with selected data: Server Code txtUserId = getRequestString("UserId"); txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId; The example above, creates a select statement by adding a variable (txtUserId) to a select string. The variable is fetched from the user input (Request) to the page. The rest of this chapter describes the potential dangers of using user input in SQL statements. SQL Injection SQL injection is a technique where malicious users can inject SQL commands into an SQL statement, via web page input. Injected SQL commands can alter SQL statement and compromise the security of a web application. SQL Injection B
Read more