Toad World Blog

CASE Statement - the IF statement answer in Toad Data Point! (Oracle/SQL Server)

May 11, 2017 3:38:00 PM by Gregory X. Martin

Data analysts commonly use IF THEN ELSE statements when attempting to create a calculated field in a BI query and then filtering results on that calculated field.

However, this can be difficult to do in the SQL editors, because neither Oracle nor SQL Server supports direct IF THEN ELSE statements. And Toad Data Point does not currently supply a way to use IF THEN ELSE statement when the underlying database does not support those.

No worries, the magical solution is quite simple… A SQL CASE statement to simulate the IF THEN ELSE logical construct.

Example #1 – Oracle

Version 12.1.0.2.0

First, let’s look at the available mathematical operators and functions available in Toad Data Point using an Oracle connection.

Note:  All Toad Data Point examples are shown using version 4.1.0.226 (64 bit)

With every good magic trick, one has to convince the skeptics. Following the numbered instruction below will prove there really is not a conditional IF statement available.

Never fear the SQL CASE statement is near!

Let’s take a look!

 

Let’s set the stage…

After connecting to your database and navigating to the standard example HR schema supplied by Oracle perform the following:

1)      Drag the JOB_HISTORY table into the Query Builder and check the EMPLOYEE_ID, START_DATE and JOB_ID fields.

2)      Order by the EMPLOYEE_ID to maintain consistency in the result set.

Now for the magic trick, let’s begin by creating a new calculated field using a SQL CASE statement to determine if an employee is a new employee in a particular department. 

Start by looking at the existing query created above by Toad Data Point in the ‘Query’ tab.

For reference purposes only, the JOB_HISTORY table contains the following data:

EMPLOYEE_ID

START_DATE

END_DATE

JOB_ID

DEPARTMENT_ID

101

9/21/97

10/27/01

AC_ACCOUNT

110

101

10/28/01

3/15/05

AC_MGR

110

102

1/13/01

7/24/06

IT_PROG

60

114

3/24/06

12/31/07

ST_CLERK

50

122

1/1/07

12/31/07

ST_CLERK

50

176

3/24/06

12/31/06

SA_REP

80

176

1/1/07

12/31/07

SA_MAN

80

200

9/17/95

6/17/01

AD_ASST

90

200

7/1/02

12/31/06

AC_ACCOUNT

90

201

2/17/04

12/19/07

MK_REP

20

Add a CASE statement to the query in the Query tab as shown below.

SQL statement entered is below highlighted in yellow.

SELECT JOB_HISTORY.EMPLOYEE_ID,
       JOB_HISTORY.START_DATE,
       JOB_HISTORY.JOB_ID,
       CASE
          WHEN     (JOB_HISTORY.START_DATE > '03/JAN/2002')
               AND (JOB_HISTORY.JOB_ID = 'AC_ACCOUNT')
          THEN
             'New Account Employee'
          WHEN     (JOB_HISTORY.START_DATE > '03/APR/2005')
               AND (JOB_HISTORY.JOB_ID = 'ST_CLERK')
          THEN
             'New Clerk Employee'
          WHEN     (JOB_HISTORY.START_DATE > '03/APR/2000')
               AND (JOB_HISTORY.JOB_ID = 'IT_PROG')
          THEN
             'New IT Programmer Employee'
          ELSE
             'N/A'
       END
          AS "New Employees"
FROM HR.JOB_HISTORY JOB_HISTORY
ORDER BY JOB_HISTORY.EMPLOYEE_ID ASC

Let’s analyze by getting down and dirty in the weeds!

Breaking it down and looking at the first WHEN condition we see:

       CASE
          WHEN     (JOB_HISTORY.START_DATE > '03/JAN/2002')
               AND (JOB_HISTORY.JOB_ID = 'AC_ACCOUNT')
          THEN
             'New Account Employee'

This means within the JOB_HISTORY table if the START_DATE is greater than ‘03/JAN/2002’ and the JOB_ID equals ‘AC_ACCOUNT’ then we create a value ‘New Account Employee’ for the new ‘New Employees’ field defined in the ‘AS’ part of the CASE statement shown below highlighted in yellow.

Multiple WHEN conditions have been entered ending the SQL CASE statement with:

                  .
.
.
 ELSE
             'N/A'
       END
          AS "New Employees"

If none of the WHEN conditions are ‘TRUE’, the ELSE condition is executed giving a value ‘N/A’ for the ‘New Employees’ field which will be the name of the calculated field within Toad Data Point.

Every magician has a lovely assistant, meet mine:

Did you notice the text that says ‘Please press Visualize Query context menu item to synchronize query and diagram after editing.’ in the above screen shot highlighted in green.

VERY IMPORTANT: You must satisfy the loveable green dude and CLICK the ‘Visualize Query’ button to complete the trick.

Click the ‘Visualize Query’ button as shown above highlighted in yellow to allow Toad Data Point to create a new diagram based on the modified query with new CASE statement just entered.

To view the results of clicking the ‘Visualize Query’ button, click the ‘Diagram’ tab as show below.

Let’s run the query and look at the results. 

1)      Click green arrow to run query

2)      Click Results tab

Note the values calculated by the SQL CASE statement in the ‘New Employees’ field.

Lastly, check out the definition of the new calculated field ‘New Employees’.

As shown at the beginning, use the sigma symbol ‘∑’, ‘Edited calculated fields’ button to navigate to the ‘New Employees’ calculated field and click the drop down as shown below.

Voila!  The SQL CASE statement entered into the ‘Query’ tab shows up as a calculated field simulating an IF statement.

Why is this IMPORTANT you ask?  Typically a data analyst would like to filter on this calculated field.

To do this, simply enter a ‘WHERE’ condition as shown below.

1)      Click on ‘Where’ condition for the calculated ‘New Employees’ field

2)      Enter a value to filter on such as ‘New IT Programmer Employee’

3)      Click OK

Next, run the query and view the results.

1)      Click green arrow to run query

2)      Click the Results tab

Note:  Only the values of ‘New IT Programmer Employee’ shows in the results set.

Example #2 – SQL Server

Version

Microsoft SQL Server Management Studio

13.0.16106.4

Microsoft Analysis Services Client Tools

13.0.1700.441

Microsoft Data Access Components (MDAC)

10.0.14393.0

Microsoft MSXML

3.0 6.0

Microsoft Internet Explorer

9.11.14393.0

Microsoft .NET Framework

4.0.30319.42000

Operating System

6.3.14393

 

This SQL Server CASE statement example is much like the Oracle example. 

First create an SQL Server connection similar to the one below.

1)      Choose Microsoft SQL Server as the connection type

2)      Choose an SQL Server database

Following the same steps for the Toad Data Point Oracle example above with some minor modification to the CASE statement syntax and given a different table and data, create the SQL Server CASE statement shown below highlighted in yellow.

Note the use of the ‘LIKE’ statement in the first WHEN condition.

SELECT Employee.NationalIDNumber,
       Employee.HireDate,
       Employee.JobTitle,
       CASE
          WHEN     (HireDate > '2002-02-23')
               AND (JobTitle LIKE 'Production Technician%')
          THEN
             'New Production Technician - WC60 Employee'
          WHEN     (HireDate > '2002-02-23')
               AND (JobTitle = 'Facilities Manager')
          THEN
             'New Facilities Employee'
          WHEN     (HireDate > '2002-02-23')
               AND (JobTitle = 'Sales Representative')
          THEN
             'New Sales Representative Employee'
          WHEN     (HireDate > '2002-02-23')
               AND (JobTitle = 'Marketing Assistant')
          THEN
             'New Marketing Assistant Employee'
          ELSE
             'N/A'
       END
          AS [New Employees]
FROM AdventureWorks2016CTP3.HumanResources.Employee Employee
ORDER BY Employee.NationalIDNumber ASC

Using the SQL Server 2016 standard example database AdventureWorks2016CTP3. The EMPLOYEES table in this database is too large show the detailed data.

But that won’t stop us from viewing the results!

Let’s be bold, dashing, daring and ‘Run’ the query. Note the values appearing the ‘New Employees’ calculated field albeit a SQL CASE statement is performing the work.

In conclusion, when the need arises for a conditional IF statement within Toad Data Point, use a SQL CASE statement. Many queries can bring back huge results sets and are then filtered with an IF statement. These result sets can be limited by creating a SQL CASE statement and filtering at the database level providing HUGE overall performance query gains by limiting the result set returned by the database engine!

Tags: Toad Data Point Analysis

Gregory X. Martin

Written by Gregory X. Martin