Q

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 first published in October 2002

Dig deeper on Oracle DBA jobs, training and certification

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

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

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

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close