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

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:

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'

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

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!

How to get the most out of Toad Data Point

Toad Data Point, is a powerful tool that will help you access and prepare data for faster business insights. Toad Data Point enables business or data analysts to seamlessly access more than 50 data sources—both on premises and in the cloud—and switch between these data sources with near-zero transition times. Users can connect, query and prepare data for faster business insights. 

Whether you are currently a Toad Data Point customer or just getting started with our free 30-day trial, learn more and access the Toad Data Point User Guide.

If you're in a trial and Toad Data Point is helping you access and prepare data for faster business insights, buy it now or contact a sales representative.

Useful resources

Video: Top 5 reasons to buy Toad Data Point Professional Edition, a solution for simplifying data access, integration, and provisioning.

Case study #1: Dell: Enterprise financial group solves data prep challenge.

Case study #2: Opening doors and creating opportunities with data insights.

Related blogs

Access more Toad Data Point blogs.

Got questions?

If you have any questions, please post questions to the Toad Data Point forum on Toad World.