Conditional SQL
Home
afs:TRADE
Services
Support
About AFS

conditional SQL logic Sybase report

Q:  I am writing a report and need to calculate a different value based on the product's calc_code.  How do I express an equation is SQL that is dependent on a column value?

A:  You have to use string functions and conditional logic...

The charindex function returns the starting point of a string within another string so 

charindex('00', calc_code)
= 1 if calc_code = '00' and 
zero otherwise...

So, if you wanted to return a number = 10 if a product's calc_code = '00' and 20 otherwise, you could use the following

select product_id,
myNumber = 20 - 10*charindex('00', calc_code)
from product

You can also use a combination of charindex and 1 - charindex for more control. For example, you could also write the above as

select product_id,
myNumber = 10*charindex('00', calc_code) + 
20*(1 - charindex('00', calc_code))
from product

When the calc_code is 00, the second term is zero, when it is not 00, the first term is zero.

Up Next