Kazuo Otani Sybase Consultant, Itochu Techno-Science Corporation [e-mail] Aug 6, 2003

1
ASE127 Exploring Transact-SQL for Business
Applications
Kazuo OtaniSybase Consultant, Itochu
Techno-Science Corporationkazuo.otani_at_ctc-g.co.jp
Aug 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 _at_name name, _at_address address
• from customers where cust_id _at_id
• Since ASE 11.5, variable assignment is also done
  with update.
• update customers
• set _at_name name, _at_address address
• where cust_id _at_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 _at_price money
• update product set _at_price price,
• price price 50.0
• where code "A120"
• insert into price_history
• (date, code, price_old, price_new) values
• (getdate(), "A120", _at_price, _at_price50.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, change0.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 _at_p money
• update t1
• set _at_pprice, changeisnull(price-_at_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 _at_p money
• update t1 set _at_pprice, changeisnull(price-_at_p,
  0)

The price of current row
The price of previous row
price _at_p beforeassignment price - _at_p _at_p afterassignment
1st row 14.74 NULL 14.74 - NULL NULL 14.74
2nd row 14.10 14.74 14.10 - 14.74 -0.64 14.10
3rd row 15.18 14.10 15.18 - 14.10 1.08 15.18
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 gt 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, diff0 into t2 from table2
• order by seqno
• go
• declare _at_i int
• update t2 set _at_iseqno, diffisnull(seqno-_at_i,0
  )
• go
• There are missing numbers if the difference is
  greater than 1
• select seqno-diff, seqno from t2 where diff gt
  1
• go
• --------- ---------
• 3 5 --gt 4 is missing
• 6 9 --gt 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, interval0 into t3 from
  sales_orders where cust_id "SY012"
• order by date
• go
• declare _at_t datetime
• update t3 set _at_t order_date, interval
  isnull(datediff(dd,_at_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 3611
• Aug 6, 2003 29 76 4729
• Aug 7, 2003 9 85 769
• 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, accml0 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 _at_i int
• select _at_i0
• update t4 set _at_i qty_at_i, accml qty_at_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
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" 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))
• 202123 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
user_id stat
SY011 15
SY012 11
SY015 5
SY020 33
... ...
id name
0 ASE
1 ASA
2 Replication Server
3 ASIQ
4 EAServer
5 PowerBuilder
26
Utilizing Bitwise Operators
Transact-SQL bitwise operators

• Transact-SQL provides bitwise operators.
• Use '' operator to check Nth (N0,1,2, ) bit is
  0 or 1.

AND 10 6 OR 10 6 EXOR 10 6 NOT 10
10 6 00001010 00000110 00001010 00000110 00001010 00000110 00001010
Result 00000010 2 00001110 14 00001100 12 11110101 -11
27
Utilizing Bitwise Operators
What number bit is 0 or 1?

• When bitmap value is M,
• Nth bit is 1 if M2N 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?

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 stat88 and
  stat1616)
• Who did choose 3rd or 4th item?
• select from survey where stat 24 ! 0
• ( where stat88 or stat1616)
• 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() gt 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(stat1)),
• "ASA" sum(sign(stat2)),
• "Rep" sum(sign(stat4)),
• "ASIQ" sum(sign(stat8)),
• "EAS" sum(sign(stat16)),
• "PB" sum(sign(stat32))
• 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.
• update survey 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("ltASEgt")
• "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"
• -gt (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 "S012"
• or code like "SY12"
• or code like "SY02"
• 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 "_at_sql)

_at_sql "code like 'Y012' or code like 'S012'
or code like 'SY12' or code like 'SY02' or
code like 'SY01' "
39
Finding Similar Character Strings
Sample stored procedure

• create proc find_similar_string
• (_at_str varchar(30))
• as
• declare _at_sql varchar(1000), _at_i int
• select _at_i1
• while _at_iltchar_length(_at_str)
• begin
• select _at_sql _at_sql "code like '"
• stuff(_at_str,_at_i,1,"") "' or "
• select _at_i _at_i1
• end
• select _at_sql substring(_at_sql,1,char_length(_at_sql)
  -3)
• exec("select from table1 where "_at_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 lt 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 "S012"
• 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
B3
B2
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 _at__at_nestlevel

47
Recursive Procedure for Expanding Tree Structure
Stored procedure to expand the structure

• create proc expand (_at_parent_id char(2))
• as
• declare _at_child_id char(2)
• declare csr cursor
• for select child_id from trees where
  parent_id _at_parent_id
• for read only
• open csr
• fetch csr into _at_child_id
• while _at__at_sqlstatus!2
• begin
• print "Level 1! 2!", _at__at_nestlevel,
  _at_child_id
• exec expand _at_child_id
• fetch csr into _at_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
view1
table1
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
51
Recursive Procedure for Expanding Tree Structure
Sample stored procedure

• create proc sp_expand_procs
• (_at_proc varchar(30))
• as
• declare _at_id int, _at_depid int, _at_type varchar(2)
• select _at_idobject_id(_at_proc)
• if (_at_idnull) return 1
• declare csr cursor
• for select depid from sysdepends where id_at_id
• for read only
• open csr
• fetch csr into _at_depid

52
Recursive Procedure for Expanding Tree Structure
Sample stored procedure

• -- continued
• while _at__at_sqlstatus!2
• begin
• select _at_proc name, _at_type type from
  sysobjects
• where id _at_depid
• print "1! (2!) 3!", _at__at_nestlevel, _at_type,
  _at_proc
• if (_at_type in ("P","V")) exec sp_expand_procs
  _at_proc
• fetch csr into _at_depid
• end
• close csr
• deallocate cursor csr
• return

53
Comparing the Contents of Two Tables

• Comparing the Contents of Two Tables

54
Comparing the Contents of Two Tables
Comparing the values in two tables

• 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?

table1 table1 table1



table2 table2 table2



same?
55
Comparing the Contents of Two Tables
Utilizing UNION

• 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

56
Comparing the Contents of Two Tables
Caveats

• 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

57
Comparing the Contents of Two Tables
Which rows are different?

• 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.

sales1
sales2
PK
PK
id date price qty
10 Aug 4 20.50 20
12 Aug 4 9.85 32
15 Aug 5 59.95 5
20 Aug 6 12.25 11
id date price qty
10 Aug 4 20.50 20
12 Aug 4 9.75 32
15 Aug 5 59.95 5
20 Aug 6 12.25 NULL
58
Comparing the Contents of Two Tables
Identifying rows having different contents

• 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() gt 1
• go
• --------
• 12
• 20

59
Exploring Transact-SQL for Business Applications

• Thanks!