Results 1 to 9 of 9

Thread: where predicate

  1. #1
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11

    Unanswered: where predicate

    db2 ese 9.5 on p/linux
    as I have never seen a statement like this and the result produced is correct, I was wondering if this is allowed and how is this translated

    select ... from taba,tabb
    where
    (taba.col1,taba.col2) = (tabb.col1,tabb.col2) ......
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think that the syntax was not documented, but supported at least from DB2 9.1 for LUW.

    Please see
    comp.databases.ibm-db2 | Google Groups

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    although being 60 and having worked with db2 for almost 30years.. never seen this before..
    thanks for the update
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    This is standard SQL stuff. It uses a <explicit row value constructor> (defined in Subclause 7.1 of the SQL 2003 standard).

    In DB2 you could also do something like this:
    Code:
    SELECT ...
    FROM ...
    WHERE (col1, col2) = ( SELECT a, b FROM ... WHERE ...)
    or this:
    Code:
    SELECT ...
    FROM ...
    WHERE (col1, col2) =ANY ( SELECT a, b FROM ... WHERE ...)
    I would guess that "(taba.col1,taba.col2) = (tabb.col1,tabb.col2)" is working because of those quantified predicates or so...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I checked Information Center of different platforms.

    Then I found that DB2 for z/OS and for iSeries suppot equal("=") or not equal("<>") of two (row-value-expression).

    While DB2 for LUW doesn't.
    The syntax include comparison of (row-value-expression) and (fullselect) in "Quantified predicate",
    but not (row-value-expression) and (row-value-expression).


    DB2 UDB for z/OS Version 8 > DB2 reference information > DB2 SQL > Language elements > Predicates

    Basic predicate
    Code:
    >>-+-expression-+-=-------+-expression--------------------------+-><
       |            |    (1)  |                                     |
       |            +-<>------+                                     |
       |            +-<-------+                                     |
       |            +->-------+                                     |
       |            +-<=------+                                     |
       |            '->=------'                                     |
       |                             (1)                            |
       '-(row-value-expression)--+-=------+--(row-value-expression)-'
                                 '-<>-----'

    IBM i 7.1 Information Center > Database > Reference > SQL reference > Language elements > Predicates
    --------------------------------------------------------------------------------

    Basic predicate
    A basic predicate compares two values or compares a set of values with another set of values.

    Code:
    >>-+-expression--+- = --+--expression---------------------------------+-><
       |             +- <> -+                                             |   
       |             +- < --+                                             |   
       |             +- > --+                                             |   
       |             +- <= -+                                             |   
       |             '- >= -'                                             |   
       +-(--row-value-expression--)--+- = --+--(--row-value-expression--)-+   
       |                             '- <> -'                             |   
       +-(--fullselect--)--+- = --+--(--row-value-expression--)-----------+   
       |                   '- <> -'                                       |   
       '-(--row-value-expression--)--+- = --+--(--fullselect--)-----------'   
                                     '- <> -'


    DB2 Version 9.7 for Linux, UNIX, and Windows
    --------------------------------------------------------------------------------
    Basic predicate
    Code:
    >>-expression--+- = ------+----expression----------------------><
                   |      (1) |                   
                   +- <> -----+                   
                   +- < ------+                   
                   +- > ------+                   
                   |      (1) |                   
                   +- <= -----+                   
                   |      (1) |                   
                   '- >= -----'
    DB2 Version 9.7 for Linux, UNIX, and Windows
    --------------------------------------------------------------------------------
    Quantified predicate

    Code:
    >>-+-expression1--+- = ------+--+-SOME-+--(fullselect1)------+-><
       |              |      (1) |  +-ANY--+                     |   
       |              +- <> -----+  '-ALL--'                     |   
       |              +- < ------+                               |   
       |              +- > ------+                               |   
       |              +- <= -----+                               |   
       |              '- >= -----'                               |   
       |    .-,---------------.                                  |   
       |    V                 |                                  |   
       '-(------expression2---+--)-- = --+-SOME-+--(fullselect2)-'   
                                         '-ANY--'

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    on the page : Quantified predicate - starting example 5 : they show this kind of syntax..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 5 is a form of
    (row-value-expression) = SOME (fullselect)

    Example 6 is a form of
    (row-value-expression) = ANY (fullselect)

    Both are documented in syntax diagram of DB2 for LUW.

    But, (row-value-expression) = (row-value-expression) is not ducumented.
    Also, (row-value-expression) <> (row-value-expression) is not ducumented.

    Equivalent predicate which conform to syntax of DB2 for LUW may be
    (row-value-expression) = SOME (VALUES (row-value-expression) )


    DB2 Version 9.7 for Linux, UNIX, and Windows
    --------------------------------------------------------------------------------
    Quantified predicate

    ...

    Example 5

    SELECT * FROM TBLAB
    WHERE (COLA,COLB+10) = SOME (SELECT COLX, COLY FROM TBLXY)

    ...

    Example 6

    SELECT * FROM TBLAB
    WHERE (COLA,COLB) = ANY (SELECT COLX,COLY-10 FROM TBLXY)
    ...

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The form using "=SOME|ANY (fullselect)" is documented for DB2 LUW. Comparing 2 row value expressions is not - or I couldn't find it.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Stolze,

    Yes. That was I want to say in
    Quote Originally Posted by tonkuma View Post
    I think that the syntax was not documented, but supported at least from DB2 9.1 for LUW.

    Please see
    comp.databases.ibm-db2 | Google Groups

    Example 1: Conform to syntax of DB2 for LUW like...
    (row-value-expression) = SOME (fullselect), though the term row-value-expression was not used.

    Note: "VALUES ('D11' , 16)" is a fullselect in DB2 for LUW. Please see fullselect - IBM DB2 9.7 for Linux, UNIX, and Windows
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT empno 
         , firstnme || RTRIM(' ' || midinit) || ' ' || lastname AS fullname 
         , workdept
         , edlevel
     FROM  employee
     WHERE (workdept , edlevel) = SOME (VALUES ('D11' , 16) ) 
     ORDER BY
           empno
    ;
    ------------------------------------------------------------------------------
    
    EMPNO  FULLNAME                       WORKDEPT EDLEVEL
    ------ ------------------------------ -------- -------
    000060 IRVING F STERN                 D11           16
    000150 BRUCE ADAMSON                  D11           16
    000170 MASATOSHI J YOSHIMURA          D11           16
    000190 JAMES H WALKER                 D11           16
    000200 DAVID BROWN                    D11           16
    
      5 record(s) selected.
    Example 2: I couldn't find a conformed syntax of DB2 for LUW. But, the result was same as Example 1.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT empno 
         , firstnme || RTRIM(' ' || midinit) || ' ' || lastname AS fullname 
         , workdept
         , edlevel
     FROM  employee
     WHERE (workdept , edlevel) = ('D11' , 16)
     ORDER BY
           empno
    ;
    ------------------------------------------------------------------------------
    
    EMPNO  FULLNAME                       WORKDEPT EDLEVEL
    ------ ------------------------------ -------- -------
    000060 IRVING F STERN                 D11           16
    000150 BRUCE ADAMSON                  D11           16
    000170 MASATOSHI J YOSHIMURA          D11           16
    000190 JAMES H WALKER                 D11           16
    000200 DAVID BROWN                    D11           16
    
      5 record(s) selected.
    Last edited by tonkuma; 01-20-12 at 15:52. Reason: Add Note for Example 1.

Posting Permissions

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