As some extra info on Andreas’ reply:
Most databases do support conditional calculations. For example, Microsoft SQL Server, IBM DB2 and Teradata use the ‘Case’ statement; Oracle uses the ‘Decode’ function (also ‘Case’ I believe from version 8i and so on); and Microsoft Access uses the ‘iff’ statement.
Case When example:
The syntax for the Case statement is as Andreas already stated:
CASE
WHEN TableName.Columnname = Value1
THEN 'Output1'
WHEN TableName.Columnname = Value2
THEN 'Output2'
ELSE 'Output3'
END
If the Columnvalue is equal to Value1, then display as “Output1” else etc.
Oracle Example Decode
ORACLE also supports a commonly used function known as decode, which has the following syntax: decode(field,value-to-test,value-if-true,value-if-false)
A simple example is:
decode(TableName.Columnname, 'Value1', 'Output1', 'Value2', 'Output2', 'Output3')
English interpretation:
If the Columnvalue is equal to Value1, then display “Output1” else if the Columnvalue is equal to value2, then display “Output” and display other values for Tablename.Columnname as “Output3”.
You can ‘Nest’ your Decodes in one and another, but: Multiple conditions are complex to write!
The decode statement in ORACLE suffers from a number of limitations:
- Decode can only positively test for a single condition. Multiple conditions require nested decodes but as I already said: they are complex to write.
- Only an ‘equality’ test is supported - it is not possible to test for greater than or less than, between, etc.
Microsoft Access Example
A functionally similar calculation to decode is supported by Microsoft Access:
iff(condition,value-if-true,value-if-false)
The following calculation is similar to the ORACLE example, and derives the calculation ‘iff_example’:
iff(Tablename.Columnname = 'Value1', 'Output1', iif(Tablename.Columnname = 'Value2', 'Output3'))
English interpretation:
If the Columnvalue is equal to Value1, then display “Output1” else if the Columnvalue is equal to value2, then display “Output” and display other values for Tablename.Columnname as “Output3”.
The iff calculation (or was it ‘iif’?) suffers from the same limitations of the ORACLE decode I guess.
These are the only ones I know, Good luck!
jobjoris
(BOB member since 2004-06-30)