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:

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

case and values syntax:

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

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

        WHEN ns_update_flags = 1 THEN 'A'
        WHEN ns_update_flags = 2 THEN 'PTR'
        ELSE 'BOTH'
FROM obj_prof
WHERE ns_usage = 1
GROUP BY ns_update_flags

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

878 BOTH
12 A
187224 BOTH


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.

Add comment

Security code