Sybase T-SQL Tips

How to implement if-then-else or emulate the Oracle decode function/crosstab (ASE 11.5 +)
How to emulate the Oracle decode function/crosstab with boolean logic
How to implement if-then-else comparing strings in a select clause
How to avoid divide by zero
How to execute dynamic SQL in the server (within stored procedures and triggers)
How to concatenate all the values from a column and return a single row?
Getting the nth highest salary


How to implement if-then-else or emulate the Oracle decode function/crosstab (ASE 11.5 +)
---------------------------------------------------------------------------------

If you are using ASE version 11.5 or later, the simplest way to implement if-then-else or implement the Oracle decode is with the CASE statement. 

SELECT STUDENT_ID,
    (CASE WHEN COURSE_ID = 101 THEN 1 ELSE 0 END) AS COURSE_101,
    (CASE WHEN COURSE_ID = 105 THEN 1 ELSE 0 END) AS COURSE_105,
    (CASE WHEN COURSE_ID = 201 THEN 1 ELSE 0 END) AS COURSE_201,
    (CASE WHEN COURSE_ID = 210 THEN 1 ELSE 0 END) AS COURSE_210,
    (CASE WHEN COURSE_ID = 300 THEN 1 ELSE 0 END) AS COURSE_300
GROUP BY STUDENT_ID
ORDER BY STUDENT_ID

SELECT 
    CASE WHEN PRODUCT_SIZE = 'SMALL' THEN 'Petit'
    ELSE 'Grand'
FROM PRODUCTS


How to emulate the Oracle decode function/crosstab with boolean logic
---------------------------------------------------------------------------------

From the Rozenshteins book, "Advanced SQL", there is the concept of characteristic functions: the delta function. For a Boolean expression such as A = B is written delta[A = B]. Its definition is that delta[A = B] = 1 whenever A = B is true and delta[A = B] = 0 otherwise. For delta[ au_ord = 1 ] , the translation is ( 1 - abs( sign( au_ord - 1 ) ) )

If you want to test a field to see if it is equal to a value, say 100, use the following code: 

SELECT ( 1 - ABS( SIGN( ISNULL( 100 - <field>, 1 ) ) ) )

The innermost function will return 1 when the field is null, a positive value if the field < 100, a negative value if the field is > 100 and will return 0 if the field = 100. This example is for Sybase or Microsoft SQL server, but other servers should support most of these functions or the COALESCE() function, which is the ANSI equivalent to ISNULL. 


The SIGN() function returns zero for a zero value, -1 for a negative value, 1 for a positive value The ABS() function returns zero for a zero value, and > 1 for any non-zero value. In this case it will return 0 or 1 since the argument is the function SIGN(), thus acting as a binary switch.

Put it all together and you get '0' if the value match, and '1' if they don't. This is not that useful, so we subtract this return value from '1' to invert it, giving us a TRUE value of '1' and a false value of '0'. These return values can then be multiplied by the value of another column, or used within the parameters of another function like SUBSTRING() to return a conditional text value. 

This is a neat way to use boolean logic to perform cross-tab or rotation queries easily, and very efficiently. Using the aggregate 'Group By' clause in a query and the ISNULL(), SIGN(), ABS(), SUBSTRING() and CHARINDEX() functions, you can create queries and views to perform all kinds of summarizations but this technique does not produce easily understood SQL statements. 


How to implement if-then-else comparing strings in a select clause
---------------------------------------------------------------------------------

SELECT ISNULL( SUBSTRING( 'Petit', CHARINDEX( 'SMALL', PRODUCT_SIZE ), 255 ), 'Grand' ) 
FROM PRODUCTS


How to avoid divide by zero
---------------------------------------------------------------------------------

SELECT CASE WHEN field2 = 0 THEN 0 ELSE field1 / field2
FROM table

or

SELECT field1 / ( field2 * CONVERT( int, SUBSTRING( '1', 1, ABS( SIGN( field2 ) ) ) ) )
FROM table


How to execute dynamic SQL in the server (within stored procedures and triggers)
---------------------------------------------------------------------------------

ASE System 12 solution:

DECLARE @sqlstring varchar(255)
SELECT @sqlstring = "SELECT COUNT(*) FROM master..sysobjects"
EXEC (@sqlstring)
GO

ASE 11.5 and 11.9 solution utilizing the CIS features: 

Firstly define your local server to be a remote server using
sp_addserver LOCALSRV,sql_server[,INTERFACENAME]
GO

Enable CIS
sp_configure "enable cis",1
GO 

Finally, use sp_remotesql, sending the sql to the server defined in point 1.
DECLARE @sqlstring varchar(255)
SELECT @sqlstring = "SELECT COUNT (*) FROM master..sysobjects"
sp_remotesql LOCALSRV,@sqlstring
GO

Remember to ensure that all of the databases referred to in the SQL string are fully qualified since the call to sp_remotesql places you back in your default database.

ASE 10 solution (undocumented feature using stored procedures):

DECLARE @sqlstring varchar(255)
SELECT @sqlstring = "sp_who"
EXEC @sqlstring
GO


How to concatenate all the values from a column and return a single row?
---------------------------------------------------------------------------------

It was possible to concatenate a series of strings to return a single column, in a sort of analogous manner to sum summing all of the numbers in a column. However, this was not a 'feature' but a bug, so if you are running an EBF that has the fix for CR 210688, this is no longer possible. 

(Obsolete:) Use a case statement, a la, 

DECLARE @string_var varchar(255)
SELECT @string_var = ""
SELECT @string_var = @string_var +
    ( CASE 1 WHEN 1 THEN char_col
    END )
    FROM tbl_a

PRINT "%1!", @string_var
GO


How to get the nth highest salary 
--------------------------------------------------------------------------------

This SQL will find the employee with the nth highest salary from the 'employee' table defined as 
emp_id Integer 
salary decimal(12,0) 

SELECT emp_id, salary 
FROM employee a 
WHERE ( SELECT COUNT( * ) 
            FROM employee b 
            WHERE b.salary > a.salary ) = ( n - 1 )

To list all the employees in the 'top - n salary bracket' use: 

SELECT emp_id, salary 
FROM employee a 
WHERE ( SELECT COUNT( * ) 
            FROM employee b 
            WHERE b.salary > a.salary ) <= ( n - 1 )