Kazuo Otani Sybase Consultant, Itochu Techno-Science Corporation kazuo.otanictc-g.co.jp Aug 6, 2003

2. Exploring Transact-SQL for Business Applications Total IT systems integration service provider. Also known as CTC. World largest distributor of Sun Microsystems. The oldest and largest distributor of Sybase in Japan. Providing total solutions to all industries.

3. Exploring Transact-SQL for Business Applications Tips for variable assignment with update statement Bitwise operators can manage data having only two values Finding similar character strings if exact matching is not found Recursive procedure for expanding tree structure Comparing values from two tables to find differences, if any

4. Variable Assignment with Update Tips for Variable Assignment with Update Statement

5. Select statement is used to assign local variable. select @name = name, @address = address from customers where cust_id = @id Since ASE 11.5, variable assignment is also done with update. update customers set @name = name, @address = address where cust_id = @id Variable Assignment with Update

6. Update statement can do two tasks at once. Assigning the value of column before update Updating the column Example: Keeping track of price before and after update declare @price money update product set @price = price, price = price + $50.0 where code = "A120" insert into price_history (date, code, price_old, price_new) values (getdate(), "A120", @price, @price+$50.0) Variable Assignment with Update

7. Variable assignment occurs for every row, if multiple rows are qualified. Best for the application to compare values between current and previous rows. select val from table1 go --------- 2200 1500 2700 Variable Assignment with Update

8. Example: Determining the daily variations in price from stocks select date, price from stocks where symbol="SY" order by date go ----------- --------- Aug 4, 2003 14.74 Aug 5, 2003 14.10 Aug 6, 2003 15.18 Variable Assignment with Update

9. 1. Copy target rows to a temporary table. select date, price, change=$0.0 into #t1 from stocks where symbol="SY" order by date go select * from #t1 go date price change ----------- --------- --------- Aug 4, 2003 14.74 0.00 Aug 5, 2003 14.10 0.00 Aug 6, 2003 15.18 0.00 Variable Assignment with Update

10. 2. Perform update to get daily change of the price. declare @p money update #t1 set @p=price, change=isnull(price-@p,$0) go select * from #t1 go date price change ----------- --------- --------- Aug 4, 2003 14.74 0.00 Aug 5, 2003 14.10 -0.64 Aug 6, 2003 15.18 1.08 Variable Assignment with Update

11. date price change ----------- --------- --------- Aug 4, 2003 14.74 0.00 Aug 5, 2003 14.10 -0.64 Aug 6, 2003 15.18 1.08 declare @p money update #t1 set @p=price, change=isnull(price-@p,$0) Variable Assignment with Update

12. The order of updating rows has a meaning. Make sure the rows are in intended order. The column to be updated should be of fixed length and not null to ensure in-place update. Or some rows may change the location after update. Variable Assignment with Update

13. Finding missing numbers where continuity is expected. select seqno from table2 go -------- 1 2 3 5 6 9 Check the difference of current and previous seqno values. If diff = 1, the numbers are serial diff > 1, there is a gap! Variable Assignment with Update

14. Obtain the differences of adjoining numbers select seqno, diff=0 into #t2 from table2 order by seqno go declare @i int update #t2 set @i=seqno, diff=isnull(seqno-@i,0) go There are missing numbers if the difference is greater than 1 select seqno-diff, seqno from #t2 where diff > 1 go --------- --------- 3 5 --> 4 is missing 6 9 --> 7 and 8 are missing Variable Assignment with Update

15. Not for numeric columns only; it works on datetime column as well to get time interval. Example: How often does the customer place an order? Obtain the number of days between orders.  select order_date, interval=0 into #t3  from sales_orders where cust_id = "SY012" order by date go declare @t datetime update #t3 set @t = order_date,  interval = isnull(datediff(dd,@t,order_date),0) go Variable Assignment with Update

16. It also works fine for obtaining accumulated value. Example: Getting the daily accumulation of qty column. select date, qty, accml from dailysales go ----------- -------- ------- Aug 4, 2003 11 11 Aug 5, 2003 36 47 = 36+11 Aug 6, 2003 29 76 = 47+29 Aug 7, 2003 9 85 = 76+9 It is the sum of up to previous rows + current value. Variable Assignment with Update

17. 1. Copy rows to a temporary table. select date, qty, accml=0 into #t4 from dailysales where item="SY012" order by date go select * from #t4 go ----------- -------- ------- Aug 4, 2003 11 0 Aug 5, 2003 36 0 Aug 6, 2003 29 0 Aug 7, 2003 9 0 Variable Assignment with Update

18. 2. Get daily accumulated value with update statement. declare @i int select @i=0 update #t4 set @i = qty+@i, accml = qty+@i go select * from #t4 go ----------- -------- ------- Aug 4, 2003 11 11 Aug 5, 2003 36 47 Aug 6, 2003 29 76 Aug 7, 2003 9 85 Variable Assignment with Update

19. Utilizing Bitwise Operators Bitwise Operators can Manage Data Having Only Two Values

20. Example: Interactive GUI User can choose any number of items.The number of items appear in GUI will grow in the future. How do you create a table to store the data? Utilizing Bitwise Operators

21. Checkbox is a "flag": possible value is 0 or 1. How about assigning one column for each flag? create table survey (user_id char(6), ase_flag bit, asa_flag bit, rep_flag bit, asiq_flag bit, eas_flag bit, pb_flag bit) What if another flag is added? Need to modify the table and application Utilizing Bitwise Operators

22. Use one integer column as bitmap. create table survey (user_id char(6), stat int ) Let each bit of integer represent the choice(s) of the user. Nth bit 7 6 5 4 3 2 1 0 0 0 0 0 1 0 1 1 = 11 0th bit = ASE flag 1st bit = ASA flag 2nd bit = Rep Server flag 3rd bit = ASIQ flag 4th bit = EAServer flag 5th bit = PowerBuilder flag Utilizing Bitwise Operators

23. Create items table to store product id and names that user can choose. select id, name from items go -------- ------------------ 0 ASE 1 ASA 2 Replication Server 3 ASIQ 4 EAServer 5 PowerBuilder id corresponds to the bit number of bitmap column. = stat column in survey table Can store up to 31 items if bitmap is an integer. Utilizing Bitwise Operators

24. The person whose ID "SY012" chose 0th, 1st and 3rd item. The bitmap value is the sum of 2n, where n is item number(s) user chose. insert into survey values ("SY012", power(2,0)+power(2,1)+power(2,3)) = 20+21+23 = 1 + 2 + 8 = 11 or insert into survey select "SY012", sum(power(2,id)) from items where id in (0,1,3) Utilizing Bitwise Operators

25. How do we know the product names from bitmap? Utilizing Bitwise Operators

26. Transact-SQL provides bitwise operators. Use '&' operator to check Nth (N=0,1,2, �) bit is 0 or 1. Utilizing Bitwise Operators

27. When bitmap value is M, Nth bit is 1 if M&2N equals to 2N Example: The bitmap integer is 11. Which bit is 1? 11 & power(2,0) = power(2,0) : 0th bit is 1 11 & power(2,1) = power(2,1) : 1st bit is 1 11 & power(2,2) != power(2,2) : 2nd bit is 0 11 & power(2,3) = power(2,3) : 3rd bit is 1 11 & power(2,4) != power(2,4) : 4th bit is 0 ... Do we need while loop to check each bit? Utilizing Bitwise Operators

28. Which products were chosen if bitmap is 11? select name from items where 11 & power(2,id) = power(2,id) go ------------ ASE ASA ASIQ Which products the person with user_id="SY012" did choose? select i.name from items i, survey s where s.stat & power(2,i.id)=power(2,i.id) and s.user_id = "SY012" Utilizing Bitwise Operators

29. Who did choose both 3rd and 4th items? select * from survey where stat & 24 = 24 (= where stat&8=8 and stat&16=16) Who did choose 3rd or 4th item? select * from survey where stat & 24 != 0 (= where stat&8=8 or stat&16=16) Find the persons who chose 3 or more items select s.user_id from survey s, items i where s.stat & power(2,i.id) = power(2,i.id) group by s.user_id having count(*) >= 3 Utilizing Bitwise Operators

30. How many person did choose each item? select i.name, count(*) from survey s, items i where s.stat & power(2,i.id) = power(2,i.id) group by i.id or select "ASE" = sum(sign(stat&1)), "ASA" = sum(sign(stat&2)), "Rep" = sum(sign(stat&4)), "ASIQ" = sum(sign(stat&8)), "EAS" = sum(sign(stat&16)), "PB" = sum(sign(stat&32)) from survey Utilizing Bitwise Operators

31. Set Nth bit of stat column update survey set stat = stat | power(2,N) where user_id = ... Clear Nth bit update survey set stat = stat &~ power(2,N) where user_id = ... The person first chose 3rd item but later changed to 4th. = Clear 3rd bit and set 4th. update survey set stat = stat &~ 8 | 16 where user_id="SY012" Utilizing Bitwise Operators

32. Finding Similar Character Strings Finding Similar Character Strings

33. Finding matching character string is often annoying. misspelling extra or missing character a word having more than one spelling ASCII control characters Want to search "Green", but not found� select * from customers where name="Green" go --------------- (0 rows affected) soundex is one of the solution. Finding Similar Character Strings

34. soundex is a phonetic index used to search strings sound the same, but are spelled differently. select soundex("Green"), soundex("Grean") go ----- ----- G650 G650 Try soundex, if exact matching is not found. select name from customers where soundex(name) = soundex("Green") go --------------- Grean Greene Finding Similar Character Strings

35. soundex may not be always helpful. Sometimes matching range is too wide.soundex("Sybase") = soundex("showbiz") = soundex("SFX") = "S120" Alphabetic characters only. soundex("1-234-567") = soundex("$7,200") = soundex("<ASE>") = "0000" Returns "0" for non-alphabetic characters. Finding Similar Character Strings

36. How do you find matching candidates if soundex is not useful? Need "SY012", but there is no matching� select * from table1 where code = "SY012" -> (0 rows affected) Data in a table may be CY012 SI012 SY-012 SY0123 Finding Similar Character Strings

37. Leverage like keyword with wildcard. select * from table1 where code like "%Y012" or code like "S%012" or code like "SY%12" or code like "SY0%2" or code like "SY01%" or select * from table1 where code like "%SY0%" or code like "%Y01%" or code like "%012%" Finding Similar Character Strings

38. Compose a string to be used as predicate of where clause. Pass this variable to dynamic SQL exec("select code from table1 where "+@sql) Finding Similar Character Strings

39. create proc find_similar_string (@str varchar(30)) as declare @sql varchar(1000), @i int select @i=1 while @i<=char_length(@str) begin select @sql = @sql + "code like '" + stuff(@str,@i,1,"%") +"' or " select @i = @i+1 end select @sql = substring(@sql,1,char_length(@sql)-3) exec("select * from table1 where "+@sql) return Finding Similar Character Strings

40. Do you prefer query without local variable? Prepare a table having sequence numbers starting from 1. select i from seqno go -------- 1 2 3 4 ... Finding Similar Character Strings

41. The following query returns the same result. select distinct code from table1, seqno where code like stuff("SY012", i, 1, "%") and i <= char_length("SY012") go -------------------- CY012 SI012 SY-012 SY0123 Works fine, but be careful if the table is large� Finding Similar Character Strings

42. The previous query behaves like: select code from table1 where code like "%Y012" union select code from table1 where code like "S%012" union ... union select code from table1 where code like "SY01%" It is multiple table scans! May result in poor performance. Check whether it is acceptable. Finding Similar Character Strings

43. Recursive Procedure for Expanding Tree Structure Expanding Tree Structure

44. Sometimes we need to store data that form tree structure. Recursive Procedure for Expanding Tree Structure

45. Considering parent-child relationship to represent the structure. parent_id child_id --------- --------- A1 B2 A1 B3 B3 C0 B3 C1 C1 D2 How do you get all the items belong to A1? Recursive Procedure for Expanding Tree Structure

46. Expand the structure with recursive procedure call. In the procedure, use cursor to check the passed item has subordinates or not. If it does, go one level deeper by calling itself. Pass a subordinate of current item Or go back one level. Current level is obtained from @@nestlevel Recursive Procedure for Expanding Tree Structure

47. create proc expand (@parent_id char(2)) as declare @child_id char(2) declare csr cursor for select child_id from trees where parent_id = @parent_id for read only open csr fetch csr into @child_id while @@sqlstatus!=2 begin print "Level %1! : %2!", @@nestlevel, @child_id exec expand @child_id fetch csr into @child_id end return Recursive Procedure for Expanding Tree Structure

48. Nested procedure works until 16 levels. The procedure aborts when nest level exceeds 16. Recursive Procedure for Expanding Tree Structure

49. Another example: Expanding the nested structure of procedure and view. Get all the objects belong to proc1 and their nest level. Recursive Procedure for Expanding Tree Structure

50. The information of nested structure is in sysdepends table. In sysdepends, id: parent object id depid: child object id that depends on the parent select id, depid from sysdepends go --------- --------- 52801881 24500322 52801881 36008417 36008417 29603788 Recursive Procedure for Expanding Tree Structure

51. create proc sp_expand_procs (@proc varchar(30)) as declare @id int, @depid int, @type varchar(2) select @id=object_id(@proc) if (@id=null) return 1 declare csr cursor for select depid from sysdepends where id=@id for read only open csr fetch csr into @depid Recursive Procedure for Expanding Tree Structure

52. -- continued while @@sqlstatus!=2 begin select @proc = name, @type = type from sysobjects where id = @depid print "%1! (%2!) %3!", @@nestlevel, @type, @proc if (@type in ("P","V")) exec sp_expand_procs @proc fetch csr into @depid end close csr deallocate cursor csr return Recursive Procedure for Expanding Tree Structure

53. Comparing the Contents of Two Tables Comparing the Contents of Two Tables

54. Two tables have identical schema; # of column, column order and its datatype are the same. The contents of both tables are expected to be exactly the same. How do you confirm? The easiest way may be bcp out and OS diff. How do you check it in SQL? Comparing the Contents of Two Tables

55. Create a union view to select all columns of both tables. create view table12 as select * from table1 union select * from table2 return If contents are the same, all three queries will return the same result. select count(*) from table1 select count(*) from table2 select count(*) from table12 Comparing the Contents of Two Tables

56. It doesn't work if a table has  text/image columns duplicated rows UNIONing large tables is resource consuming. tempdb should be large enough to perform the query. union creates a worktable in tempdb Comparing the Contents of Two Tables

57. Identifying the primary key values, if contents of any non-PK columns are not the same. Would like to know 12 and 20 have different contents. Comparing the Contents of Two Tables

58. Create UNIONed temporary table from two tables. select * into #sales12 from sales1 union select * from sales2 If there are different rows, same id appears twice. select id from #sales12 group by id having count(*) > 1 go -------- 12 20 Comparing the Contents of Two Tables

59. Thanks! Exploring Transact-SQL for Business Applications