where predicate

przytula_guy · #1 (**permalink**) 01-19-12, 08:46

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

tonkuma · #2 (**permalink**) 01-19-12, 09:35

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

przytula_guy · #3 (**permalink**) 01-19-12, 09:44

although being 60 and having worked with db2 for almost 30years.. never seen this before..
thanks for the update

stolze · #4 (**permalink**) 01-20-12, 04:31

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

tonkuma · #5 (**permalink**) 01-20-12, 07:45

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--'

przytula_guy · #6 (**permalink**) 01-20-12, 08:04

on the page : Quantified predicate - starting example 5 : they show this kind of syntax..

tonkuma · #7 (**permalink**) 01-20-12, 08:27

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) )

Quote:

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

stolze · #8 (**permalink**) 01-20-12, 13:47

The form using "=SOME|ANY (fullselect)" is documented for DB2 LUW. Comparing 2 row value expressions is not - or I couldn't find it.

tonkuma · #9 (**permalink**) 01-20-12, 14:38

Stolze,

Yes. That was I want to say in

Quote:

Originally Posted by tonkuma

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech