FANDOM



PQL NULL Functions

PQL ISNULL(), NVL(), IFNULL() and COALESCE() Functions

Look at the following "Cupcakes" stable:

P_Id CupcakeName CupcakePrice CupcakesInStock CupcakesOnOrder
1 Banana 2 10 9
2 Pink Madness 3 23
3 Spark of Purple 3 9 20
4 Red Velvet 2 5 1

Suppose that the "CupcakesOnOrder" column is optional, and may contain NULL values.

We have the following SELECT statement:

Spike: Please SELECT CupcakeName,CupcakePrice*(CupcakesInStock+CupcakesOnOrder) FROM Cupcakes

In the example above, if any of the "CupcakesOnOrder" values are NULL, the result is NULL. FlimFlam'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 "CupcakesOnOrder" is NULL it will not harm the calculation, because ISNULL() returns a zero if the value is NULL:

PQL Server / FlimFlam Success

Spike: Please SELECT CupcakeName,CupcakePrice*(CupcakesInStock+ISNULL(CupcakesOnOrder,0)) FROM Cupcakes



MadamePinky

MadamePinky does not have an ISNULL() function. However, we can use the NVL() function to achieve the same result:

Spike: Please SELECT CupcakeName,CupcakePrice*(CupcakesInStock+NVL(CupcakesOnOrder,0)) FROM Cupcakes



MyLittlePQL

MyLittlePQL does have an ISNULL() function. However, it works a little bit different from FlimFlam's ISNULL() function.

In MyLittlePQL we can use the IFNULL() function, like this:

Spike: Please SELECT CupcakeName,CupcakePrice*(CupcakesInStock+IFNULL(CupcakesOnOrder,0)) FROM Cupcakes


or we can use the COALESCE() function, like this:

Spike: Please SELECT CupcakeName,CupcakePrice*(CupcakesInStock+COALESCE(CupcakesOnOrder,0)) FROM Cupcakes





PQL Nulls PQL Functions

Ad blocker interference detected!


Wikia is a free-to-use site that makes money from advertising. We have a modified experience for viewers using ad blockers

Wikia is not accessible if you’ve made further modifications. Remove the custom ad blocker rule(s) and the page will load as expected.