Exploring Transact-SQL for Business Applications

Document Sample
Exploring Transact-SQL for Business Applications Powered By Docstoc
					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

   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.
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
Variable Assignment with Update




    Tips for Variable Assignment with
             Update Statement
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
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)
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
                                  -700
                        1500
                                         +1200
                           2700
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
                                              - 0.64
                   Aug 5, 2003     14.10
                                             + 1.08
                   Aug 6, 2003     15.18
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
                                                 Add money type
          go
                                                 'change' column
                                                  filled with $0.0
          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
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
Variable Assignment with Update
How does it work?

   date        price     change
   ----------- --------- ---------
                                   The price of
   Aug 4, 2003     14.74      0.00 current row
   Aug 5, 2003     14.10     -0.64
   Aug 6, 2003     15.18      1.08              The price of
                                                              previous row
   declare @p money
   update #t1 set @p=price, change=isnull(price-@p,$0)
                        @p before                             @p after
                price                     price - @p
                        assignment                           assignment
    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
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.
Variable Assignment with Update
Finding missing numbers

   Finding missing numbers where continuity is expected.

                 select seqno   from table2
                 go
                  --------
                         1      1
                         2      1
                         3
                                2
                         5
                         6      1
                         9      3

• 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
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
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
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.
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
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
Utilizing Bitwise Operators




   Bitwise Operators can Manage Data
         Having Only Two Values
Utilizing Bitwise Operators
Storing data with only two values

   Example: Interactive GUI

            Your ID: SY012

                 Which Sybase products are you interested in?

                    ASE             ASA        Replication Server
                    ASIQ            EAServer   PowerBuilder



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

• Use one integer column as bitmap.
                                                                    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
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.
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))

                           = 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
Breaking bitmap down into bit, how?

• How do we know the product names from bitmap?


              survey                             items
      user_id          stat           id   name
      SY011            15             0    ASE
      SY012            11             1    ASA
      SY015             5             2    Replication Server
      SY020            33             3    ASIQ

      ...              ...            4    EAServer
                                      5    PowerBuilder
Utilizing Bitwise Operators
Transact-SQL bitwise operators

• Transact-SQL provides bitwise operators.

                    AND           OR      EXOR       NOT
                   10 & 6        10 | 6   10 ^ 6     ~10
          10     00001010 00001010 00001010        00001010
          6      00000110 00000110 00000110
       Result 00000010 00001110 00001100           11110101
                   =2      = 14     = 12               = -11


• Use '&' operator to check Nth (N=0,1,2, …) bit is 0 or 1.
Utilizing Bitwise Operators
What number bit is 0 or 1?

• 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
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                     No loop is
         ASA                     necessary
         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
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
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
                                  One item, one row
   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
                                  All items in one row
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"
Finding Similar Character Strings




              Finding Similar
              Character Strings
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.
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
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.
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
Finding Similar Character Strings
Pattern matching with like predicate

   Leverage like keyword with wildcard.

          select * from table1
          where code like "%Y012"
                                           Shifting the position
             or code like "S%012"
                                            of wildcard by one
             or code like "SY%12"
                                                 character
             or code like "SY0%2"
             or code like "SY01%"

          or

          select * from table1
          where code like "%SY0%"          Shifting the position
             or code like "%Y01%"           of consecutive N
             or code like "%012%"               characters
Finding Similar Character Strings
Utilizing dynamic SQL

   Compose a string to be used as predicate of where clause.

                  @sql =
                  "code like   '%Y012'   or
                   code like   'S%012'   or
                   code like   'SY%12'   or
                   code like   'SY0%2'   or
                   code like   'SY01%'    "

   Pass this variable to dynamic SQL

         exec("select code from table1 where "+@sql)
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
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         id column of
                                    sysobjects can be an
                          3
                                    alternative up to 19.
                          4
                        ...
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…
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.
Recursive Procedure for Expanding Tree Structure




         Expanding Tree Structure
Recursive Procedure for Expanding Tree Structure
Tree structure

   Sometimes we need to store data that form tree structure.


                           A1


                    B2             B3


                           C0               C1


                                            D2
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?
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
Recursive Procedure for Expanding Tree Structure
Stored procedure to expand the structure

create proc expand (@parent_id char(2))
as                                 Cursor to
    declare @child_id char(2)
                                   find child
    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                      Current level
    begin
        print "Level %1! : %2!", @@nestlevel, @child_id
        exec expand @child_id
        fetch csr into @child_id
    end
return                   Go down one level
                                by calling itself
Recursive Procedure for Expanding Tree Structure
Sample result and restriction

    Level
      0         1          2    3           exec expand "A1"
                                            go
     A1        B2
                                            Level 1 : B2
                                            Level 1 : B3
               B3          C0
                                            Level 2 : C0
                           C1   D2
                                            Level 2 : C1
                                            Level 3 : D2



    Nested procedure works until 16 levels.
     The procedure aborts when nest level exceeds 16.
Recursive Procedure for Expanding Tree Structure
Expanding nested structure of proc/view/table

   Another example:
    Expanding the nested structure of procedure and view.


                                                Nest level
                             proc1
                                                    1


                    table1            view1         2


                                     table2         3


   Get all the objects belong to proc1 and their nest level.
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
                   ---------    ---------        table1

    proc1          52801881     24500322         view1
                   52801881            36008417
                                                      table2
                   36008417            29603788
Recursive Procedure for Expanding Tree Structure
Sample stored procedure

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
Sample stored procedure

-- 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
Comparing the Contents of Two Tables




         Comparing the Contents
             of Two Tables
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?

                    table1                   table2

                                     same?


   The easiest way may be bcp out and OS diff.

   How do you check it in SQL?
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
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
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.

      PK
                   sales1            PK
                                                  sales2
      id    date      price   qty    id    date     price   qty
      10   Aug 4     $20.50   20     10   Aug 4    $20.50    20

      12   Aug 4     $9.85    32     12   Aug 4    $9.75     32

      15   Aug 5     $59.95   5      15   Aug 5    $59.95    5

      20   Aug 6     $12.25   11     20   Aug 6    $12.25   NULL


   Would like to know 12 and 20 have different contents.
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(*) > 1
          go
          --------
                12
                20
Exploring Transact-SQL for Business Applications




                   Thanks!