The Sybase Case statement is handy for performing conditional SQL Expressions.  Recently, I needed to summarize the number of static host objects in the VitalQIP database using Sybase.  I needed to summarize them by counting how many statically defined objects by:

  • 'A' or Address only records
  • 'PTR' or Reverse PTR only records
  • 'A' and 'PTR' records

The easiest way to do that was to use the Sybase Case Statement. The syntax for the Case Statement is as follows:

case 
    when search_condition then expression 
   [when search_condition then expression]...
   [else expression]
end

case and values syntax:

case expression
    when expression then expression
   [when expression then expression]...
   [else expression]
end

The Case Statement used to get my data looked like this:

SELECT COUNT(obj_id),
    CASE
        WHEN ns_update_flags = 1 THEN 'A'
        WHEN ns_update_flags = 2 THEN 'PTR'
        ELSE 'BOTH'
    END
FROM obj_prof
WHERE ns_usage = 1
GROUP BY ns_update_flags

When the SQL is executed, the output will look like this:

COUNTNS_UPDATE_FLAGS
878 BOTH
12 A
1 PTR
187224 BOTH

Add comment


Security code
Refresh