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 )