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

Comments   

0 #1 Elías González E 2016-09-20 09:39
Hey there. I was reading your post, quite useful. Maybe you could be gentle and helpe me with this.
I've got a query to create a matrix table in sybase12. columns are "MinAge", "MaxAge", "Qty" and "Genre". Where Genre values are "Male or Female". So I want to create a matrix grouping by range of ages. Wrote the code concatenating minAge and MaxAge, and a2 statements to diplay Qty in tow columns "Males" and "Female", grouping them by the concatenation fo Min Age and Max Age, but I get duplicated rows with ranges "10-15" for males, and "10-15" for females. I just want 1 row 10-15 with the values for both male and females.
Quote

Add comment


Security code
Refresh