[Comp.Sci.Dept, Utrecht] Note from archiver<at>cs.uu.nl: This page is part of a big collection of Usenet postings, archived here for your convenience. For matters concerning the content of this page, please contact its author(s); use the source, if all else fails. For matters concerning the archive as a whole, please refer to the archive description or contact the archiver.

Subject: Sybase FAQ: 13/19 - ASE SQL (2 of 3)

This article was archived around: 18 Apr 2006 04:29:49 GMT

All FAQs in Directory: databases/sybase-faq
All FAQs posted in: comp.databases.sybase
Source: Usenet Version


Archive-name: databases/sybase-faq/part13 URL: http://www.isug.com/Sybase_FAQ Version: 1.7 Maintainer: David Owen Last-modified: 2003/03/02 Posting-Frequency: posted every 3rd month A how-to-find-the-FAQ article is posted on the intervening months.
SQL Advanced 6.2.1 How to emulate the Oracle decode function/crosstab 6.2.2 How to implement if-then-else within a select-clause. 6.2.3 deleted due to copyright hassles with the publisher 6.2.4 How to pad with leading zeros an int or smallint. 6.2.5 Divide by zero and nulls. 6.2.6 Convert months to financial months. 6.2.7 Hierarchy traversal - BOMs. 6.2.8 Is it possible to call a UNIX command from within a stored procedure or a trigger? 6.2.9 Information on Identities and Rolling your own Sequential Keys 6.2.10 How can I execute dynamic SQL with ASE 6.2.11 Is it possible to concatenate all the values from a column and return a single row? 6.2.12 Selecting rows N to M without Oracle's rownum? 6.2.13 How can I return number of rows that are returned from a grouped query without using a temporary table? Useful SQL Tricks SQL Fundamentals ASE FAQ ------------------------------------------------------------------------------- 6.2.1: How to emulate the Oracle decode function/crosstab ------------------------------------------------------------------------------- If you are using ASE version 11.5 or later, the simplest way to implement the Oracle decode is with the CASE statement. The following code snippet should be compared with the example using a characteristic function given below . 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 However, if you have a version of ASE that does not support the case statement, then you will have to try the following. There may be other reasons to try characteristics functions. If you go to the Amazon web site and look for reviews for of Rozenshteins book, Advanced SQL, you will see that one reviewer believes that a true crosstab is not possible with the case statement. I am not sure. I have also not done any performance tests to see which is quicker. There 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. This technique does not produce easily understood SQL statements. 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. For example, to create a grid from a student registration table containing STUDENT_ID and COURSE_ID columns, where there are 5 courses (101, 105, 201, 210, 300) use the following query: Compare this version with the case statement above. SELECT STUDENT_ID, (1- ABS( SIGN( ISNULL( 101 - COURSE_ID, 1)))) COURSE_101, (1- ABS( SIGN( ISNULL( 105 - COURSE_ID, 1)))) COURSE_105, (1- ABS( SIGN( ISNULL( 201 - COURSE_ID, 1)))) COURSE_201, (1- ABS( SIGN( ISNULL( 210 - COURSE_ID, 1)))) COURSE_210, (1- ABS( SIGN( ISNULL( 300 - COURSE_ID, 1)))) COURSE_300 GROUP BY STUDENT_ID ORDER BY STUDENT_ID Back to top ------------------------------------------------------------------------------- 6.2.2: How to implement if-then-else in a select clause ------------------------------------------------------------------------------- ASE 11.5 introduced the case statement, which can be used to replace a lot of this 'trick' SQL with more readable (and standard) code. With a case statement, an if then else is as easy as: declare @val char(20) select @val = 'grand' select case when @val = 'small' then 'petit' else 'grand' end However, quite a number of people are still using pre-11.5 implementations, including those people using the free 11.0.3.3 Linux release. In that case you can use the following recipe. To implement the following condition in a select clause: if @val = 'small' then print 'petit' else print 'grand' fi in versions of ASE prior to 11.5 do the following: select isnull(substring('petit', charindex('small', @val), 255), 'grand') To test it out, try this: declare @val char(20) select @val = 'grand' select isnull(substring('petit', charindex('small', @val), 255), 'grand') This code is not readily understandable by most programmers, so remember to comment it well. Back to top ------------------------------------------------------------------------------- 6.2.3: Removed ------------------------------------------------------------------------------- 6.2.4: How to pad with leading zeros an int or smallint. ------------------------------------------------------------------------------- By example: declare @Integer int /* Good for positive numbers only. */ select @Integer = 1000 select "Positives Only" = right( replicate("0", 12) + convert(varchar, @Integer), 12) /* Good for positive and negative numbers. */ select @Integer = -1000 select "Both Signs" = substring( "- +", (sign(@Integer) + 2), 1) + right( replicate("0", 12) + convert(varchar, abs(@Integer)), 12) select @Integer = 1000 select "Both Signs" = substring( "- +", (sign(@Integer) + 2), 1) + right( replicate("0", 12) + convert(varchar, abs(@Integer)), 12) go Produces the following results: Positives Only -------------- 000000001000 Both Signs ------------- -000000001000 Both Signs ------------- +000000001000 Back to top ------------------------------------------------------------------------------- 6.2.5: Divide by zero and nulls ------------------------------------------------------------------------------- During processing, if a divide by zero error occurs you will not get the answer you want. If you want the result set to come back and null to be displayed where divide by zero occurs do the following: 1> select * from total_temp 2> go field1 field2 ----------- ----------- 10 10 10 0 10 NULL (3 rows affected) 1> select field1, field1/(field2*convert(int, substring('1',1,abs(sign(field2))))) from total_temp 2> go field1 ----------- ----------- 10 1 10 NULL 10 NULL Back to top ------------------------------------------------------------------------------- 6.2.6: Convert months to financial months ------------------------------------------------------------------------------- To convert months to financial year months (i.e. July = 1, Dec = 6, Jan = 7, June = 12 ) Method #1 select ... ((sign(sign((datepart(month,GetDate())-6) * -1)+1) * (datepart(month, GetDate())+6)) + (sign(sign(datepart(month, GetDate())-7)+1) * (datepart(month, GetDate())-6))) ... from ... Method #2 select charindex(datename(month,getdate()), " July August September October November December January Febuary March April May June " ) / 10 In the above example, the embedded blanks are significant. Back to top -------------------------------------------------------------------------------