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

Stop and perform a task

To find inequalities within a column, how can I compare an input variable with each row of data in a particular column, check to see which value is greater, stop and perform a task if the input is greater than the row, or move on to the next if it's not?

SQL is a set-based language. While SQL does provide for cursors, you may research them yourself, as you will find no help from me on using them. Most database systems also provide for stored procedures, which would allow you both to write procedural code to "move on" from one row of a table to the next, and to "perform a task" when a condition is detected. However, you will again find no help from me on writing stored procedures.

A set-based solution is characterized by an approach in which you specify which rows you want, and typically this set of rows all satisfy a specific condition. This is the way SQL was designed to work.

An example which involves "performing a task" is an UPDATE. Suppose your input variable is 500, which represents an amount of five hundred dollars, and you want to compare this to a particular column called CustomerReturns in a table called StaffSalesTotals, and if 500 is greater than the value in the CustomerReturns column, this indicates that the sales person represented by this row has not exceeded the threshold for customer returns, and therefore the action that you wish to take is to set the value of the LowReturnsBonus column to 50, representing a special 50 dollar bonus amount for this salesperson.

update StaffSalesTotals
   set LowReturnsBonus = 50
 where CustomerReturns < 500   

Note in particular that there is nothing in this solution which involves "stopping" or "moving on to the next row" -- these concepts are meaningless in a set-based solution. All rows which meet the specified condition are updated simultaneously.

Dig Deeper on Oracle and SQL

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.