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

## Content

Find more PRO+ content and other member only offers, here.

#### 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

• ### Streaming tool from StreamSets eyes data in motion for GDPR

StreamSets software for inspecting big data brings governance to data in motion. Such capabilities may find more use as the ...

• ### Data expert: GDPR deadline is an opportunity, not a burden

There is stress as the EU's General Data Protection Regulation compliance deadline nears, but the GDPR privacy movement is a good...

In big data news, we find Google TPUs, or Tensor Processing Units, offered as a cloud service, while LinkedIn is open sourcing a ...

• ### Rethinking analytics processes spurs enterprise innovation

By taking a fresh look at the makeup of their analytics organizations, enterprises can innovate their business models and take ...

• ### Diversified data sets for analytics deliver top results

Analytics teams should focus on data diversity to ensure that their projects deliver the most meaningful insights -- but they ...

• ### How to boost the value of BI in today's analytics landscape

Traditional BI reporting still gives businesses valuable information. But its value can be increased by incorporating it into a ...

## SearchSAP

• ### SAP Ariba Live focuses on procurement for purpose

SAP Ariba Live 2018 focused on the idea that businesses can use procurement technology to do good in the world; for example, by ...

• ### SAP debuts consumption-based pricing model for SAP Cloud

SAP Cloud Platform is now available as a consumption-based model, an alternative to the subscription model. SAP also updated the ...

SAP paid \$2.4 billion to acquire lead-to-money vendor CallidusCloud, and analysts agree that the significant price may be worth ...

## SearchSQLServer

SQL Operations Studio simplifies routine administration of SQL Server and Azure SQL databases, making database development and ...

• ### Meltdown and Spectre fixes eyed for SQL Server performance issues

Microsoft has responded to the Spectre and Meltdown chip vulnerabilities with patches and other fixes. But IT teams need to sort ...

• ### Five SQL Server maintenance steps you should take -- ASAP

Putting off SQL Server administration tasks can lead to database problems. Enact these often-neglected maintenance items to help ...

## TheServerSide.com

• ### Stay ahead of Java security issues like SQL and LDAP injections

Java security best practices include validating input to prevent injection attacks and managing malicious JAR vulnerabilities.

• ### Why the Waterfall or Agile debate will be around forever

Which is the right methodology to use for your project: Waterfall or Agile? The industry may be at peak Agile, as the ...

• ### Chef's InSpec 2.0 brings compliance automation to the cloud

Enterprises have been quick to adopt automation tools for development and deployment but only recently have organizations started...

## SearchDataCenter

• ### IBM cloud services to secure mainframes out to the edge

Big Blue will introduce IBM cloud services that use blockchain, containers and its z14 mainframes to deliver improved security ...

• ### Four disadvantages of hyper-converged infrastructure systems

Problems with scalability and unexpected licensing costs can create problems for organizations that deploy hyper-converged ...

• ### IBM Power9 servers seek market inroads to AI, cloud

IBM follows up its first Power9 server with a raft of systems designed to appeal to a wider array of markets -- most notably, AI ...

## SearchContentManagement

• ### Scrivito unveils serverless CMS product

By building the CMS with ReactJS, Scrivito gained attraction with development community, according to an analyst.

• ### Content personalization tools sharpen focus on customers

Content personalization isn't new; Amazon weaponized it, and Jeff Bezos is the world's richest man. New tools are putting it ...

• ### Leading brands see the need for personalized content

Content personalization continues to expand within companies as maturing technologies make it a viable marketing option for ...

## SearchHRSoftware

• ### Blockchain in HR remains in beta, outlook uncertain

Blockchain in HR verification is getting interest from universities, vendors and startups. There are powerful forces driving its ...

• ### How people analytics can improve HR effectiveness

Getting insight into your workforce can reveal everything from training issues to the reasons for turnover or missed corporate ...

• ### At Ceridian, role of CIO requires constant learning, adjusting

You might say Warren Perlman, CIO at Ceridian, a global HCM software company, has been preparing for the role of CIO all his life...

Close