home *** CD-ROM | disk | FTP | other *** search
/ ftp.pasteur.org/FAQ/ / ftp-pasteur-org-FAQ.zip / FAQ / databases / sybase-faq / part13 < prev    next >
Encoding:
Internet Message Format  |  2004-04-21  |  9.6 KB

  1. Path: senator-bedfellow.mit.edu!dreaderd!not-for-mail
  2. Message-ID: <databases/sybase-faq/part13_1082468590@rtfm.mit.edu>
  3. Supersedes: <databases/sybase-faq/part13_1074677126@rtfm.mit.edu>
  4. Expires: 2 Aug 2004 13:43:10 GMT
  5. References: <databases/sybase-faq/part1_1082468590@rtfm.mit.edu>
  6. X-Last-Updated: 2003/03/02
  7. From: dowen@midsomer.org (David Owen)
  8. Newsgroups: comp.databases.sybase,comp.answers,news.answers
  9. Subject: Sybase FAQ: 13/19 - ASE SQL (2 of 3)
  10. Reply-To: dowen@midsomer.org (David Owen)
  11. Followup-To: comp.databases.sybase
  12. Distribution: world
  13. Organization: Midsomer Consultants Inc.
  14. Approved: news-answers-request@MIT.EDU
  15. Keywords: FAQ, DATABASES, SYBASE, ASA, ASE, REP
  16. Originator: faqserv@penguin-lust.MIT.EDU
  17. Date: 20 Apr 2004 13:45:12 GMT
  18. Lines: 262
  19. NNTP-Posting-Host: penguin-lust.mit.edu
  20. X-Trace: 1082468712 senator-bedfellow.mit.edu 576 18.181.0.29
  21. Xref: senator-bedfellow.mit.edu comp.databases.sybase:106211 comp.answers:56957 news.answers:270297
  22.  
  23. Archive-name: databases/sybase-faq/part13
  24. URL: http://www.isug.com/Sybase_FAQ
  25. Version: 1.7
  26. Maintainer: David Owen
  27. Last-modified: 2003/03/02
  28. Posting-Frequency: posted every 3rd month
  29.    A how-to-find-the-FAQ article is posted on the intervening months.
  30.  
  31.                                  SQL Advanced                                  
  32.                                                                                
  33.  
  34.  
  35.     6.2.1    How to emulate the Oracle decode function/crosstab
  36.     6.2.2    How to implement if-then-else within a select-clause.
  37.     6.2.3    deleted due to copyright hassles with the publisher
  38.     6.2.4    How to pad with leading zeros an int or smallint.
  39.     6.2.5    Divide by zero and nulls.
  40.     6.2.6    Convert months to financial months.
  41.     6.2.7    Hierarchy traversal - BOMs.
  42.     6.2.8    Is it possible to call a UNIX command from within a stored
  43.     procedure or a trigger?
  44.     6.2.9    Information on Identities and Rolling your own Sequential Keys
  45.     6.2.10  How can I execute dynamic SQL with ASE
  46.     6.2.11  Is it possible to concatenate all the values from a column and
  47.     return a single row?
  48.     6.2.12  Selecting rows N to M without Oracle's rownum?
  49.     6.2.13  How can I return number of rows that are returned from a grouped
  50.     query without using a temporary table?
  51.    
  52. Useful SQL Tricks SQL Fundamentals ASE FAQ
  53.  
  54. -------------------------------------------------------------------------------
  55.  
  56. 6.2.1: How to emulate the Oracle decode function/crosstab
  57.  
  58. -------------------------------------------------------------------------------
  59.  
  60. If you are using ASE version 11.5 or later, the simplest way to implement the
  61. Oracle decode is with the CASE statement. The following code snippet should be
  62. compared with the example using a characteristic function given below .
  63.  
  64. SELECT STUDENT_ID,
  65.        (CASE WHEN COURSE_ID = 101 THEN 1 ELSE 0 END) AS COURSE_101,
  66.        (CASE WHEN COURSE_ID = 105 THEN 1 ELSE 0 END) AS COURSE_105,
  67.        (CASE WHEN COURSE_ID = 201 THEN 1 ELSE 0 END) AS COURSE_201,
  68.        (CASE WHEN COURSE_ID = 210 THEN 1 ELSE 0 END) AS COURSE_210,
  69.        (CASE WHEN COURSE_ID = 300 THEN 1 ELSE 0 END) AS COURSE_300
  70. GROUP BY STUDENT_ID
  71. ORDER BY STUDENT_ID
  72.  
  73. However, if you have a version of ASE that does not support the case statement,
  74. then you will have to try the following. There may be other reasons to try
  75. characteristics functions. If you go to the Amazon web site and look for
  76. reviews for of Rozenshteins book, Advanced SQL, you will see that one reviewer
  77. believes that a true crosstab is not possible with the case statement. I am not
  78. sure. I have also not done any performance tests to see which is quicker.
  79.  
  80. There is a neat way to use boolean logic to perform cross-tab or rotation
  81. queries easily, and very efficiently. Using the aggregate 'Group By' clause in
  82. a query and the ISNULL(), SIGN(), ABS(), SUBSTRING() and CHARINDEX() functions,
  83. you can create queries and views to perform all kinds of summarizations.
  84.  
  85.     This technique does not produce easily understood SQL statements.
  86.    
  87. If you want to test a field to see if it is equal to a value, say 100, use the
  88. following code:
  89.  
  90. SELECT (1- ABS( SIGN( ISNULL( 100 - <field>, 1))))
  91.  
  92. The innermost function will return 1 when the field is null, a positive value
  93. if the field < 100, a negative value if the field is > 100 and will return 0 if
  94. the field = 100. This example is for Sybase or Microsoft SQL server, but other
  95. servers should support most of these functions or the COALESCE() function,
  96. which is the ANSI equivalent to ISNULL.
  97.  
  98. The SIGN() function returns zero for a zero value, -1 for a negative value, 1
  99. for a positive value The ABS() function returns zero for a zero value, and > 1
  100. for any non-zero value. In this case it will return 0 or 1 since the argument
  101. is the function SIGN(), thus acting as a binary switch.
  102.  
  103. Put it all together and you get '0' if the value match, and '1' if they don't.
  104. This is not that useful, so we subtract this return value from '1' to invert
  105. it, giving us a TRUE value of '1' and a false value of '0'. These return values
  106. can then be multiplied by the value of another column, or used within the
  107. parameters of another function like SUBSTRING() to return a conditional text
  108. value.
  109.  
  110. For example, to create a grid from a student registration table containing
  111. STUDENT_ID and COURSE_ID columns, where there are 5 courses (101, 105, 201,
  112. 210, 300) use the following query:
  113.  
  114. Compare this version with the case statement above.
  115.  
  116. SELECT STUDENT_ID,
  117.     (1- ABS( SIGN( ISNULL( 101 - COURSE_ID, 1)))) COURSE_101,
  118.     (1- ABS( SIGN( ISNULL( 105 - COURSE_ID, 1)))) COURSE_105,
  119.     (1- ABS( SIGN( ISNULL( 201 - COURSE_ID, 1)))) COURSE_201,
  120.     (1- ABS( SIGN( ISNULL( 210 - COURSE_ID, 1)))) COURSE_210,
  121.     (1- ABS( SIGN( ISNULL( 300 - COURSE_ID, 1)))) COURSE_300
  122. GROUP BY STUDENT_ID
  123. ORDER BY STUDENT_ID 
  124.  
  125. Back to top
  126.  
  127. -------------------------------------------------------------------------------
  128.  
  129. 6.2.2: How to implement if-then-else in a select clause
  130.  
  131. -------------------------------------------------------------------------------
  132.  
  133. ASE 11.5 introduced the case statement, which can be used to replace a lot of
  134. this 'trick' SQL with more readable (and standard) code. With a case statement,
  135. an if then else is as easy as:
  136.  
  137. declare @val char(20)
  138. select @val = 'grand'
  139.  
  140. select case when @val = 'small' then
  141.                 'petit'
  142.             else
  143.                 'grand'
  144.             end
  145.  
  146. However, quite a number of people are still using pre-11.5 implementations,
  147. including those people using the free 11.0.3.3 Linux release. In that case you
  148. can use the following recipe.
  149.  
  150. To implement the following condition in a select clause:
  151.  
  152. if @val = 'small' then
  153.     print 'petit'
  154. else
  155.     print 'grand'
  156. fi
  157.  
  158. in versions of ASE prior to 11.5 do the following:
  159.  
  160. select isnull(substring('petit', charindex('small', @val), 255), 'grand')
  161.  
  162. To test it out, try this:
  163.  
  164. declare @val char(20)
  165. select @val = 'grand'
  166. select isnull(substring('petit', charindex('small', @val), 255), 'grand') 
  167.  
  168. This code is not readily understandable by most programmers, so remember to
  169. comment it well.
  170.  
  171. Back to top
  172.  
  173. -------------------------------------------------------------------------------
  174.  
  175. 6.2.3: Removed
  176.  
  177. -------------------------------------------------------------------------------
  178.  
  179. 6.2.4: How to pad with leading zeros an int or smallint.
  180.  
  181. -------------------------------------------------------------------------------
  182.  
  183. By example:
  184.  
  185. declare @Integer        int
  186.  
  187. /* Good for positive numbers only. */
  188. select @Integer = 1000
  189.  
  190. select "Positives Only" =
  191.        right( replicate("0", 12) + convert(varchar, @Integer), 12)
  192.  
  193. /* Good for positive and negative numbers. */
  194. select @Integer = -1000
  195.  
  196. select "Both Signs" =
  197.        substring( "- +", (sign(@Integer) + 2), 1) +
  198.        right( replicate("0", 12) + convert(varchar, abs(@Integer)), 12)
  199.  
  200. select @Integer = 1000
  201.  
  202. select "Both Signs" =
  203.        substring( "- +", (sign(@Integer) + 2), 1) +
  204.        right( replicate("0", 12) + convert(varchar, abs(@Integer)), 12)
  205.  
  206. go
  207.  
  208. Produces the following results:
  209.  
  210.  Positives Only
  211.  --------------
  212.  000000001000
  213.  
  214.  Both Signs
  215.  -------------
  216.  -000000001000
  217.  
  218.  Both Signs
  219.  -------------
  220.  +000000001000
  221.  
  222. Back to top
  223.  
  224. -------------------------------------------------------------------------------
  225.  
  226. 6.2.5: Divide by zero and nulls
  227.  
  228. -------------------------------------------------------------------------------
  229.  
  230. During processing, if a divide by zero error occurs you will not get the answer
  231. you want. If you want the result set to come back and null to be displayed
  232. where divide by zero occurs do the following:
  233.  
  234. 1> select * from total_temp
  235. 2> go
  236.  field1      field2
  237.  ----------- -----------
  238.           10          10
  239.           10           0
  240.           10        NULL
  241.  
  242. (3 rows affected)
  243. 1> select field1, field1/(field2*convert(int,
  244.                   substring('1',1,abs(sign(field2))))) from total_temp
  245. 2> go
  246.  field1
  247.  ----------- -----------
  248.           10           1
  249.           10        NULL
  250.           10        NULL
  251.  
  252. Back to top
  253.  
  254. -------------------------------------------------------------------------------
  255.  
  256. 6.2.6: Convert months to financial months
  257.  
  258. -------------------------------------------------------------------------------
  259.  
  260. To convert months to financial year months (i.e. July = 1, Dec = 6, Jan = 7,
  261. June = 12 )
  262.  
  263. Method #1
  264.  
  265. select ... ((sign(sign((datepart(month,GetDate())-6) * -1)+1) *
  266.    (datepart(month, GetDate())+6))
  267.       + (sign(sign(datepart(month, GetDate())-7)+1) *
  268.    (datepart(month, GetDate())-6)))
  269.    ...
  270.   from ...
  271.  
  272. Method #2
  273.  
  274. select charindex(datename(month,getdate()),
  275. "          July      August    September October   November  December
  276. January   Febuary   March     April     May       June      "
  277.                      ) / 10
  278.  
  279. In the above example, the embedded blanks are significant.
  280.  
  281. Back to top
  282.  
  283. -------------------------------------------------------------------------------
  284.  
  285.