home *** CD-ROM | disk | FTP | other *** search
- Path: senator-bedfellow.mit.edu!dreaderd!not-for-mail
- Message-ID: <databases/sybase-faq/part13_1082468590@rtfm.mit.edu>
- Supersedes: <databases/sybase-faq/part13_1074677126@rtfm.mit.edu>
- Expires: 2 Aug 2004 13:43:10 GMT
- References: <databases/sybase-faq/part1_1082468590@rtfm.mit.edu>
- X-Last-Updated: 2003/03/02
- From: dowen@midsomer.org (David Owen)
- Newsgroups: comp.databases.sybase,comp.answers,news.answers
- Subject: Sybase FAQ: 13/19 - ASE SQL (2 of 3)
- Reply-To: dowen@midsomer.org (David Owen)
- Followup-To: comp.databases.sybase
- Distribution: world
- Organization: Midsomer Consultants Inc.
- Approved: news-answers-request@MIT.EDU
- Keywords: FAQ, DATABASES, SYBASE, ASA, ASE, REP
- Originator: faqserv@penguin-lust.MIT.EDU
- Date: 20 Apr 2004 13:45:12 GMT
- Lines: 262
- NNTP-Posting-Host: penguin-lust.mit.edu
- X-Trace: 1082468712 senator-bedfellow.mit.edu 576 18.181.0.29
- Xref: senator-bedfellow.mit.edu comp.databases.sybase:106211 comp.answers:56957 news.answers:270297
-
- 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
-
- -------------------------------------------------------------------------------
-
-