1 / 59

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

ASE127: Exploring Transact -SQL for Business Applications. Kazuo Otani Sybase Consultant, Itochu Techno-Science Corporation kazuo.otani@ctc-g.co.jp Aug 6, 2003. Exploring Transact-SQL for Business Applications. About my company: Itochu Techno-Science Corporation.

hanne
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ASE127: Exploring Transact-SQL for Business Applications Kazuo OtaniSybase Consultant, Itochu Techno-Science Corporationkazuo.otani@ctc-g.co.jpAug 6, 2003

  2. Exploring Transact-SQL for Business Applications About my company: Itochu Techno-Science Corporation • 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 Topics • 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. Variable Assignment with Update Two methods of assigning local variable • 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

  6. Variable Assignment with Update Getting the value before update • 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)

  7. Variable Assignment with Update Applications update-with-variable work best • 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 -700 +1200

  8. Variable Assignment with Update Getting the difference of neighboring rows • 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 - 0.64 + 1.08

  9. Variable Assignment with Update Getting the difference of neighboring rows • 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 Add money type 'change' column filled with $0.0

  10. Variable Assignment with Update Getting the difference of neighboring rows • 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

  11. Variable Assignment with Update How does it work? 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) The price of current row The price of previous row

  12. Variable Assignment with Update Some notes for update-with-variable • 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.

  13. Variable Assignment with Update Finding missing numbers • 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! 1 1 2 1 3

  14. Variable Assignment with Update Finding missing numbers • 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

  15. Variable Assignment with Update Time interval • 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

  16. Variable Assignment with Update Row-by-row accumulation • 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.

  17. Variable Assignment with Update Row-by-row accumulation • 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

  18. Variable Assignment with Update Row-by-row accumulation • 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

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

  20. Your ID: SY012 Which Sybase products are you interested in? ASE ASA Replication Server ASIQ EAServer PowerBuilder Utilizing Bitwise Operators Storing data with only two values • 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?

  21. Utilizing Bitwise Operators Table using one column for each flag • 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

  22. Utilizing Bitwise Operators Table using bitmap to represent multiple flags • 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 bitmap

  23. Utilizing Bitwise Operators Table to store the items user can choose • 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.

  24. Utilizing Bitwise Operators Calculating bitmap value • The person whose ID "SY012" chose0th, 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)

  25. Utilizing Bitwise Operators Breaking bitmap down into bit, how? • How do we know the product names from bitmap? survey items

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

  27. Utilizing Bitwise Operators What number bit is 0 or 1? • When bitmap value is M, Nth bit is 1 if M&2Nequals 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?

  28. Utilizing Bitwise Operators Bitmap query 1 • 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" No loop is necessary

  29. Utilizing Bitwise Operators Bitmap query 2 • 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

  30. Utilizing Bitwise Operators Bitmap query 3 • 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 One item, one row All items in one row

  31. Utilizing Bitwise Operators Maintaining bitmap • 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. updatesurvey set stat = stat &~ 8 | 16 where user_id="SY012"

  32. Finding Similar Character Strings Finding Similar Character Strings

  33. Finding Similar Character Strings Headache of character string search • 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.

  34. Finding Similar Character Strings soundex function • 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

  35. Finding Similar Character Strings Limitations of soundex • 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.

  36. Finding Similar Character Strings What if soundex is not useful? • 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

  37. Finding Similar Character Strings Pattern matching with like predicate • 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%" Shifting the position of wildcard by one character Shifting the position of consecutive N characters

  38. Finding Similar Character Strings Utilizing dynamic SQL • Compose a string to be used as predicate of where clause. • Pass this variable to dynamic SQL exec("select code from table1 where "+@sql) @sql = "code like '%Y012' or code like 'S%012' or code like 'SY%12' or code like 'SY0%2' or code like 'SY01%' "

  39. Finding Similar Character Strings Sample stored procedure 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

  40. Finding Similar Character Strings like keyword search without local variable • 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 ... id column of sysobjects can be an alternative up to 19.

  41. Finding Similar Character Strings like keyword search without local variable • 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…

  42. Finding Similar Character Strings Caveats • 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.

  43. Recursive Procedure for Expanding Tree Structure Expanding Tree Structure

  44. Recursive Procedure for Expanding Tree Structure Tree structure • Sometimes we need to store data that form tree structure. A1 B2 B3 C0 C1 D2

  45. Recursive Procedure for Expanding Tree Structure Tree structure represented in parent-child relationship • 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?

  46. Recursive Procedure for Expanding Tree Structure Expanding the structure by recursion • 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

  47. Recursive Procedure for Expanding Tree Structure Stored procedure to expand the structure create proc expand (@parent_id char(2)) as declare @child_id char(2) declare csr cursor for select child_id from treeswhere 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 Cursor to find child Current level Go down one level by calling itself

  48. Recursive Procedure for Expanding Tree Structure Sample result and restriction • Nested procedure works until 16 levels. The procedure aborts when nest level exceeds 16. Level 0 1 2 3 exec expand "A1" go Level 1 : B2 Level 1 : B3 Level 2 : C0 Level 2 : C1 Level 3 : D2 A1 B2 B3 C0 C1 D2

  49. Recursive Procedure for Expanding Tree Structure Expanding nested structure of proc/view/table • Another example: Expanding the nested structure of procedure and view. • Get all the objects belong to proc1 and their nest level. Nest level 1 2 3 proc1 table1 view1 table2

  50. Recursive Procedure for Expanding Tree Structure Expanding nested structure of proc/view/table • 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 table1 proc1 view1 table2

More Related