Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2013
    Posts
    12

    Question Unanswered: with - what does it do?

    Could somebody explain how and when "with" is used. Thank you.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see

    select-statement - IBM DB2 9.7 for Linux, UNIX, and Windows

    Code:
    select-statement
    
    >>-+-----------------------------------+--fullselect--●--------->
       |       .-,-----------------------. |                  
       |       V                         | |                  
       '-WITH----common-table-expression-+-'                  
    
    >--+------------------+--●--+---------------------+--●---------->
       +-read-only-clause-+     '-optimize-for-clause-'      
       '-update-clause----'                                  
    
    >--+------------------+--●-------------------------------------->
       '-isolation-clause-'      
    
    >--+-------------------------------------+--●------------------><
       '-concurrent-access-resolution-clause-'
    Code:
    common-table-expression
    
    >>-table-name--+---------------------------+-------------------->
                   |    .-,-----------.        |   
                   |    V             |    (1) |   
                   '-(----column-name-+--)-----'   
    
    >--AS--(--fullselect--)----------------------------------------><
    and
    INSERT - IBM DB2 9.7 for Linux, UNIX, and Windows

    Code:
    Syntax
    
    >>-INSERT INTO--+-table-name-------+---------------------------->
                    +-view-name--------+   
                    +-nickname---------+   
                    '-(--fullselect--)-'   
    
    >--+-----------------------+--+---------------------+----------->
       |    .-,-----------.    |  '-| include-columns |-'   
       |    V             |    |                            
       '-(----column-name-+--)-'                            
    
                 .-,----------------------------.              
                 V                              |              
    >--+-VALUES----+-+-expression-+-----------+-+----------+-------->
       |           | +-NULL-------+           |            |   
       |           | '-DEFAULT----'           |            |   
       |           |    .-,--------------.    |            |   
       |           |    V                |    |            |   
       |           +-(----+-expression-+-+--)-+            |   
       |           |      +-NULL-------+      |            |   
       |           |      '-DEFAULT----'      |            |   
       |           '-row-expression-----------'            |   
       '-+-----------------------------------+--fullselect-'   
         |       .-,-----------------------. |                 
         |       V                         | |                 
         '-WITH----common-table-expression-+-'                 
    
    >--+--------------+--------------------------------------------><
       '-WITH--+-RR-+-'   
               +-RS-+     
               +-CS-+     
               '-UR-'
    fullselect is
    fullselect - IBM DB2 9.7 for Linux, UNIX, and Windows

    Code:
    fullselect
    
    >>-+-subselect---------+---------------------------------------->
       +-(fullselect)------+   
       '-| values-clause |-'   
    
       .----------------------------------------------.   
       V                                              |   
    >----+------------------------------------------+-+------------->
         '-+-UNION---------+--+-subselect---------+-'     
           +-UNION ALL-----+  +-(fullselect)------+       
           +-EXCEPT--------+  '-| values-clause |-'       
           +-EXCEPT ALL----+                              
           +-INTERSECT-----+                              
           '-INTERSECT ALL-'                              
    
    >--+-----------------+--+--------------------+------------------>
       '-order-by-clause-'  '-fetch-first-clause-'   
    
    >--+------------------+----------------------------------------><
       '-isolation-clause-'   
    
    values-clause
    
               .-,--------------.   
               V                |   
    |--VALUES----| values-row |-+-----------------------------------|
    
    values-row
    
    |--+-+-expression-----+-------+---------------------------------|
       | +-NULL-----------+       |   
       | '-row-expression-'       |   
       |    .-,--------------.    |   
       |    V                |    |   
       '-(----+-expression-+-+--)-'   
              '-NULL-------'
    Last edited by tonkuma; 03-07-13 at 17:58.

  3. #3
    Join Date
    Mar 2013
    Posts
    12
    Thank you.

  4. #4
    Join Date
    Mar 2013
    Posts
    12

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by w1n30 View Post
    The referenced URL was for Microsoft SQL Server.

    So, if you want to learn DB2 SQL, you should consult DB2 documentations.

    For example, in the page(already I mentioned)...
    select-statement - IBM DB2 9.7 for Linux, UNIX, and Windows
    you can find descriptions of common table expression including recursive common table expression, like...
    ...
    ...

    A common table expression permits defining a result table with a table-name that can be specified as a table name in any FROM clause of the fullselect that follows.
    Multiple common table expressions can be specified following the single WITH keyword.
    Each common table expression specified can also be referenced by name in the FROM clause of subsequent common table expressions.

    ...
    ...

    The common table expression is also optional prior to the fullselect in the CREATE VIEW and INSERT statements.

    A common table expression can be used:
    •In place of a view to avoid creating the view (when general use of the view is not required and positioned updates or deletes are not used)
    •To enable grouping by a column that is derived from a scalar subselect or function that is not deterministic or has external action
    •When the desired result table is based on host variables
    •When the same result table needs to be shared in a fullselect
    •When the result needs to be derived using recursion
    •When multiple SQL data change statements need to be processed within the query

    If the fullselect of a common table expression contains a reference to itself in a FROM clause,
    the common table expression is a recursive common table expression.
    Queries using recursion are useful in supporting applications such as bill of materials (BOM), reservation systems, and network planning.

    The following must be true of a recursive common table expression:

    ...
    ...

    When developing recursive common table expressions, remember that an infinite recursion cycle (loop) can be created.
    Check that recursion cycles will terminate. This is especially important if the data involved is cyclic.
    A recursive common table expression is expected to include a predicate that will prevent an infinite loop.
    The recursive common table expression is expected to include:
    •In the iterative fullselect, an integer column incremented by a constant.
    •A predicate in the where clause of the iterative fullselect in the form "counter_col < constant" or "counter _col < :hostvar".
    A warning is issued if this syntax is not found in the recursive common table expression (SQLSTATE 01605).

    Recursion example: bill of materials

    ...
    ...
    Last edited by tonkuma; 03-09-13 at 12:58.

  6. #6
    Join Date
    Mar 2013
    Posts
    12

    Thumbs up

    Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •