Download
1 / 58
580 likes | 746 Views
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.. About my company: Itochu Techn
E N D
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
