Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Indicator and Step Functions

SQL Tips by Donald Burleson

An indicator function 1A(x) maps every element x of a set A into 1, and any element which is not in A into 0. Formally: 

1A(x) := if x A then 1 else 0 endif

Set operations can be expressed in terms of indicator functions. Intersection is as simple as multiplication: 

1AB(x) = 1A(x) 1B(x)

Unlike set theory where union is dual to intersection, there is no duality between multiplication and addition of indicator functions. Therefore, union has to be expressed via the inclusion-exclusion principle as:

1AB(x) = 1A(x) +1B(x) - 1A(x) 1B(x)

In SQL context, the indicator function's domain is a set of numbers. An indicator function for a set x[0,∞) is a very important special case. It is called (a primitive) step function, and has a designated notation:

1x := 1[0,∞)(x)

Let's also write the primitive step function definition in pseudo programming notation, which was used for the indicator function in the very beginning of the session

1x := if x ≥ 0 then 1 else 0 endif

If the abstraction level is raised and the hard coded constants 0, 1 and yet another 0, is converted into variables,

if x ≥ x0 then α else β endif

then the pseudo code starts looking similar to the case operator. This expression is a generic step function (although it doesn't have any abbreviated notation).

The generic step function can be expressed via the primitive step function:

if x ≥ x0 then α else β endif = α 1x-x0 + β 1 x0-x

So far, so good handling simple case operators, but what about nested case expressions? Consider the following:

if x ≥ 0 then (if y ≥ 0 then 1 else 0 endif) else 0 endif

Easy: the above formula for general step function should handle the case where α is an expression rather than a constant. By substitution we have

if x ≥ 0 then (if y ≥ 0 then 1 else 0 endif) else 0 endif = 1y 1x

as if we had just an intersection of the x ≥ 0 and y ≥ 0 sets!

However, still missing is a way to express the step function in SQL. True, we can trivially utilize the case operator, but then we can hardly justify learning the step and indicator functions. The key formula is expressing the step function via the standard numeric sign function:

step(x) := sign(1+sign(x))

Now queries with conditional expressions can be written in a peculiar way:

select ename, sal,
       sign(1+sign(sal-2000)) SALgt2000 -- i.e. step(x-2000)
from emp

The step function method was the only game in town a long time ago when the case operator was not part of the SQL standard yet. Nowadays a seasoned SQL programmer writes a case expression without giving it a second thought. Yet, there are rare cases when clever application of indicator function is still a contender. Consider the following data:

 select * from Transactions

You are asked to sum transaction quantities grouped by Date to produce the output like this:

The first step towards a solution is recognizing the indicator function hidden in the above data. The character Type column is very inconvenient to deal with; can it be transformed into numeric? The two value column certainly can be coded with numbers, but that is not what we are looking for. It is the instr(Type, ?debit?) and instr(Type, ?credit?) expressions that convert the character column values into indicator functions. We proceed by simply summarizing the Amount weighted by indicator functions:

select Date,
       sum(Amount*instr(Type,?debit?)) debit,
       sum(Amount*instr(Type,?credit?)) credit
from Transactions
group by Date
This nice solution is due to Laurent Schneider. Compare it to a conditional summation with the case operator:

select Date,
       sum(case when Type=?debit? then Amount else 0 end) debit,
       sum(case when Type=?credit? then Amount else 0 end) credit
from Transactions
group by Date


This is an excerpt from the new book SQL Design Patterns: The Expert Guide to SQL Programming by Vadim Tropashko

You can buy it direct from the publisher for 30%-off.


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.