Q
Problem solve Get help with specific problems with your technologies, process and projects.

# A data-driven solution to changing expression when criteria change

I have the following SQL expression:

(CASE WHEN XYZ >469 AND XYZ BETWEEN 580 AND 599 THEN 4 ELSE
(CASE WHEN XYZ >454 AND XYZ BETWEEN 600 AND 614 THEN 4 ELSE
(CASE WHEN XYZ >429 AND XYZ BETWEEN 615 AND 639 THEN 4 ELSE
(CASE WHEN XYZ BETWEEN 400 AND 499 AND XYZ BETWEEN 640 AND 669 THEN 4 ELSE
(CASE WHEN XYZ BETWEEN 400 AND 469 AND XYZ BETWEEN 670 AND 694 THEN 4 ELSE
(CASE WHEN XYZ BETWEEN 370 AND 469 AND XYZ BETWEEN 695 AND 714 THEN 4 ELSE
(CASE WHEN XYZ BETWEEN 370 AND 439 AND XYZ >714 THEN 4 ELSE
(CASE WHEN XYZ >499 AND XYZ BETWEEN 640 AND 684 THEN 3 ELSE
(CASE WHEN XYZ BETWEEN 470 AND 499 AND XYZ BETWEEN 670 AND 714 THEN 3 ELSE
(CASE WHEN XYZ >499 AND XYZ BETWEEN 685 AND 749 THEN 2 ELSE
(CASE WHEN XYZ BETWEEN 440 AND 499 AND XYZ >714 THEN 2 ELSE
(CASE WHEN XYZ >499 AND XYZ >749 THEN 1 ELSE NULL END)
END) END) END) END) END) END) END) END) END) END)
END)
Does anyone have a data-driven solution so that if criteria changes, my one change can traverse through all the places where this expression is used?

First, I'd suggest that you review your CASE statement as it appears to have some logic in it that would not work properly. For example, several of the cases have checks like this one: CASE WHEN XYZ BETWEEN 400 AND 499 AND XYZ BETWEEN 640 AND 669 THEN... That case will NEVER get met because a single number can not be in both ranges. Perhaps you meant to use an OR operator rather than an AND. Anyway, just check your statement for validity to make sure!

Now, to make a "data-driven" solution, I'd suggest that you drop the CASE statement entirely and write a function that contains all your case (if-then-else) logic. The function would take XYZ as an IN parameter and RETURN a number which is the value (1, 2, 3, 4 or null) that you want to use depending on the value of XYZ. Then, instead of a case statement, you'd call the function you wrote as follows: SELECT get_xyz_val(xyz) FROM table; The function returns the value you want and you don't have all that "messy" case logic in your select statement. Also, when you want to change criteria, you simply change and recompile the function....easy!

Here's a sample function (note that I just use your exact checks from your CASE statement...I still think you need to take a look at these for validity):

CREATE OR REPLACE FUNCTION get_xyz_value (p_xyz IN number)
RETURN number
AS
v_return number := 0 ;
BEGIN
IF p_xyz > 469 AND
p_xyz BETWEEN 580 AND 599 THEN
v_return := 4;
ELSIF p_xyz > 454 AND
p_xyz BETWEEN 600 AND 614 THEN
v_return := 4;
ELSIF p_xyz > 429 AND p_xyz BETWEEN 615 AND 639 THEN
v_return := 4;
ELSIF p_xyz BETWEEN 400 AND 499 AND
p_xyz BETWEEN 640 AND 669 THEN
v_return := 4 ;
ELSIF p_xyz BETWEEN 400 AND 469 AND
p_xyz BETWEEN 670 AND 694 THEN
v_return := 4 ;
ELSIF p_XYZ BETWEEN 370 AND 469 AND
p_XYZ BETWEEN 695 AND 714 THEN
v_return := 4 ;
ELSIF p_XYZ BETWEEN 370 AND 439 AND
p_XYZ >714 THEN
v_return := 4 ;
ELSIF p_XYZ > 499 AND
p_XYZ BETWEEN 640 AND 684 THEN
v_return := 3 ;
ELSIF p_XYZ BETWEEN 470 AND 499 AND
p_XYZ BETWEEN 670 AND 714 THEN
v_return := 3 ;
ELSIF p_XYZ > 499 AND
p_XYZ BETWEEN 685 AND 749 THEN
v_return := 2 ;
ELSIF p_XYZ BETWEEN 440 AND 499 AND
p_XYZ > 714 THEN
v_return := 2 ;
ELSIF p_XYZ > 499 AND
p_XYZ > 749 THEN
v_return := 1 ;
ELSE
v_return := null ;
END IF;

RETURN v_return ;
END;
/
Then to use, just run your select statement as follows:
SELECT xyz, get_xyz_value(xyz) as xyz_value
FROM your_table ;
Here's an example of the results (using some dummy data I created):
SQL> select xyz, get_xyz_value(xyz) as xyz_value from testxyz ;

XYZ  XYZ_VALUE
---------- ----------
799          1
699          2
599          4
475
499
642          3
I hope that helps!

This was last published in October 2002

#### Have a question for an expert?

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

#### Start the conversation

Send me notifications when other members comment.

## SearchDataManagement

• ### GPU databases bring greater parallelism to big data processing

GPU databases offer a new way to process data. 451 Research analyst James Curtis discusses where they fit in big data ...

• ### Distributed SQL database wagered on for scalability boost, GDPR

CockroachDB is a distributed database system that seeks to support processing on a global scale, a need that online gambling ...

• ### GDPR data protection edicts make good data governance a must

The European Union's new GDPR law puts the onus on companies to ensure that their data governance and management practices enable...

• ### Ten KPI templates for your dashboards

KPIs help companies gauge success, but how do you choose the right metrics to create useful reports? Here you'll find 10 KPI ...

• ### Data visualization process demands smart design, accurate data

Well-designed data visualizations can enable executives to make more-informed business decisions, increasing the potential ROI of...

• ### Seven good data visualization practices for visual integrity

Data visualizations need visual integrity to ensure that the data they present can be interpreted correctly. Follow these design ...

## SearchSAP

• ### Inside SAP's digital transformation strategy

In this Q&A, Bertram Schulte, SAP's chief digital officer, discusses the how and why as the company goes through its own internal...

• ### SAP Cloud Platform pushes to break new ground, extend ERP

SAP wants its Cloud Platform to connect ERP systems with new technology like machine learning and IoT, resulting in new processes...

• ### SAP Cloud Platform architecture is glue for new technologies

SAP Cloud Platform has numerous integration tools for linking next-gen technologies like blockchain, machine learning and IoT to ...

## SearchSQLServer

• ### Azure Cosmos DB features, pricing morph with new provisioning model

The ability to provision Azure Cosmos DB throughput at the database level could come with pricing sticker shock for some users of...

• ### Why running SQL Server on Docker is no longer frowned upon

Microsoft now lets SQL Server databases run in Docker containers, a capability that depends on using volumes to store data in a ...

• ### What the Microsoft GDPR compliance toolkit offers for SQL Server

The set of GDPR compliance tools that Microsoft offers for SQL Server is designed to make it easier for users of the database ...

## TheServerSide.com

• ### How to calculate McCabe cyclomatic complexity in Java

An understanding of how to calculate McCabe cyclomatic complexity in Java is the first step in better testing and improved ...

• ### Why you need to learn these automated testing tools for Java

How do Selenium, Junit, Grinder and other automated testing tools work? Here's a tool-centric guide to unit testing, integration ...

• ### Fix broken builds with this log4j Maven dependency example

DevOps professionals need to know how Maven dependency management works. This log4j Maven dependency example demonstrates how to ...

## SearchDataCenter

• ### Assess network device monitoring capabilities with these tips

Organizations considering new network device monitoring software should evaluate the alerting capabilities and extensibility of ...

• ### Hadoop cluster capacity planning best practices

Trying to calculate Hadoop cluster capacities isn't always straightforward. It's important for organizations to include IOPS and ...

• ### Decide which application monitoring software is right for you

Organizations that want to implement application monitoring tools should consider how the tool gathers and reports information, ...

## SearchContentManagement

• ### New age collaboration tools empower the future of ECM

Document management, file-sharing and ECM platforms might represent 'ancient' 1990s technology, but AI and collaboration tools ...

• ### Future of collaboration technology: More Slack and AI, less email

Companies have moved beyond messaging and email to Slack, Teams and even AI-powered scheduling and voice recognition. Are you ...

• ### Adding artificial intelligence bots to Cortana for Office 365

AI bots aren't just for requesting songs and definitions anymore. As enterprises look for ways to bring virtual assistants into ...

## SearchHRSoftware

• ### Huge potential of video interview software comes with risks

Tech-savvy millennials' advantage over older workers risks ageism, and AI needs work handling emotions and ethnicity. But few ...

• ### How to solve six of the biggest employee retention challenges

Recruiters are enticing workers with new opportunities on an increasingly frequent basis. Give your employees plenty of reasons ...

• ### Recruiting online giants turn to new technologies

Technology is driving major changes to the recruiting online market. Monster.com and CareerBuilder.com have deployed new job ...

Close