Feeds:
Posts
Comments

Archive for the ‘Logical Functions’ Category

I need to find maximum between two numbers, So, I was searching for a solution to find maximum number between two numbers and I found few interesting articles by Pinal Dave And Madhivana .

I also would like to add one more solution with the help of new logical function “IIF” in SQL SERVER 2012.

Example 1 :

DECLARE @Value1 DECIMAL(5,2) = 9.22
DECLARE @Value2 DECIMAL(5,2) = 8.34
SELECT IIF(ISNULL(@Value1,0) > ISNULL(@Value2,0) ,@Value1, @Value2) AS MaxColumn
--RESULT
9.22

Example 2 : (With NULL)

DECLARE @Value1 DECIMAL(5,2) = 9.22
DECLARE @Value2 DECIMAL(5,2) = NULL
SELECT IIF(ISNULL(@Value1,0) > ISNULL(@Value2,0) ,@Value1, @Value2) AS MaxColumn
--RESULT
9.22

Example 3 : (With Negative Value)

DECLARE @Value1 DECIMAL(5,2) = -9.22
DECLARE @Value2 DECIMAL(5,2) = 8.34
SELECT IIF(ISNULL(@Value1,0) > ISNULL(@Value2,0) ,@Value1, @Value2) AS MaxColumn
--RESULT
8.34

Read Full Post »

SQL Server 2012 introduced new logical function namely “IIF”. Lets discuss the syntax and purpose of this function in detail.

IIF:

Syntax :

 IIF ( boolean_expression, true_value, false_value )

Purpose :
The purpose of this logical function is to check the Boolean condition and if the condition is true it will return true value otherwise false value.
This function is available in SSRS and now, it is available in SQL SERVER 2012. By using this function, you don’t need to use if else , you can validate and return value in a single line of code.

Examples :

Example-1

DECLARE @int1 int = 100;
DECLARE @int2 int = 200;
SELECT IIF ( @int1> @int2, 'TRUE', 'FALSE' ) AS Result;
--Result
FALSE

Example 2

DECLARE @int1 int = 100;
DECLARE @int2 int = 200;
SELECT IIF ( @int1> @int2, NULL, NULL ) AS Result;
--Result

Msg 8133, Level 16, State 1, Line 3</span>
At least one of the result expressions in a CASE specification must be an expression other than the NULL constant.
Reason : Because both the constant are NULL, even if one constant is not NULL it will not give this error as explained in example 3.
But, you can pass NULL parameter in both true and false value and SQL server will accept it as explained in example 4.
Example 3

DECLARE @int1 int = 100;
DECLARE @int2 int = 200;
SELECT IIF ( @int1> @int2, NULL, 'False' ) AS Result;
--Result
False

Example 4

DECLARE @int1 int = 100;
DECLARE @int2 int = 200;
DECLARE @Condition_True int = NULL;
DECLARE @Condition_False int = NULL;
SELECT IIF ( @int1> @int2, @Condition_True, @Condition_False) AS Result;
--Result
NULL

Example 5 :
Real world example of IIF function

In this example, we will enter marks in IIF condition and will get different grades as per the marks.

DECLARE @Marks int = 95;
SELECT IIF ( @Marks> 90, 'A Grade', IIF(@Marks>=80 And @Marks<=90,'B Grade','C Grade')) AS Result;
--Result
A Grade

GO
DECLARE @Marks int = 80;
SELECT IIF ( @Marks> 90, 'A Grade', IIF(@Marks>=80 And @Marks<=90,'B Grade','C Grade')) AS Result;
GO
--Result
B Grade

DECLARE @Marks int = 70;
SELECT IIF ( @Marks> 90, 'A Grade', IIF(@Marks>=80 And @Marks<=90,'B Grade','C Grade')) AS Result;
--Result
C Grade

Reference : MSDN

Read Full Post »

SQL Server 2012 introduced new logical function namely “Choose”. Lets discuss the syntax and purpose of this function in detail.

Syntax :

  CHOOSE ( index, val_1, val_2 [, val_n ] )

Purpose :

The purpose of this logical function is to return the value from the list as per the index (1 based index). For example, we have four quarters (QUARTER-1 on 1st, QUARTER-2 on 2nd , QUARTER-3 on 3rd and QUARTER-4 on 4th index respectively) and would like to pick the values (Quarters) as per the index.

Note : If choose function will not find any value in a given index, it will return NULL value.

Example :

Select CHOOSE(0,'QUARTER-1','QUARTER-2','QUARTER-3','QUARTER-4') AS [ANNUAL QUARTER]
--OUTPUT
--NULL

Select CHOOSE(1,'QUARTER-1','QUARTER-2','QUARTER-3','QUARTER-4') AS [ANNUAL QUARTER]
--OUTPUT
--QUARTER-1

Select CHOOSE(2,'QUARTER-1','QUARTER-2','QUARTER-3','QUARTER-4') AS [ANNUAL QUARTER]
--OUTPUT
--QUARTER-2

Select CHOOSE(3,'QUARTER-1','QUARTER-2','QUARTER-3','QUARTER-4') AS [ANNUAL QUARTER]
--OUTPUT
--QUARTER-3

Select CHOOSE(4,'QUARTER-1','QUARTER-2','QUARTER-3','QUARTER-4') AS [ANNUAL QUARTER]
--OUTPUT
--QUARTER-4

Select CHOOSE(5,'QUARTER-1','QUARTER-2','QUARTER-3','QUARTER-4') AS [ANNUAL QUARTER]
--OUTPUT
--NULL

Read Full Post »