On May 7, 3:54 pm, Tonkuma <tonk...@[EMAIL PROTECTED]
> wrote:
> I couldn't understand your issue(might be by my poor English
> capability).
>
> Here are some thoughts which are inspired by your article.
> 1) Although it is not do***ented, you can specify row comparison
> predicate in(on?) DB2 for LUW 9.1.
> For example:
> ------------------------------ Commands Entered
> ------------------------------
> SELECT empno, workdept
> , firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
> , edlevel
> FROM employee
> WHERE (workdept, edlevel) =3D ('D11', 16)
> ;
>
--------------------------------------------------------------------------=
----
>
> EMPNO WORKDEPT FULLNAME EDLEVEL
> ------ -------- ------------------------------ -------
> 000060 D11 IRVING F STERN 16
> 000150 D11 BRUCE ADAMSON 16
> 000170 D11 MASATO**** J YO****MURA 16
> 000190 D11 JAMES H WALKER 16
> 000200 D11 DAVID BROWN 16
> 200170 D11 KIYO**** YAMAMOTO 16
>
> 6 record(s) selected.
>
> And you can specify full-select (including VALUES clause) in predicate
> on DB2 for LUW prior V9.1.
> Here are two examples:
> ------------------------------ Commands Entered
> ------------------------------
> SELECT empno, workdept
> , firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
> , edlevel
> FROM employee
> WHERE (workdept, edlevel) =3D ANY (VALUES ('D11', 16) )
> ;
>
--------------------------------------------------------------------------=
----
>
> EMPNO WORKDEPT FULLNAME EDLEVEL
> ------ -------- ------------------------------ -------
> 000060 D11 IRVING F STERN 16
> 000150 D11 BRUCE ADAMSON 16
> 000170 D11 MASATO**** J YO****MURA 16
> 000190 D11 JAMES H WALKER 16
> 000200 D11 DAVID BROWN 16
> 200170 D11 KIYO**** YAMAMOTO 16
>
> 6 record(s) selected.
>
> ------------------------------ Commands Entered
> ------------------------------
> SELECT empno, workdept
> , firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
> , edlevel
> FROM employee
> WHERE (workdept, edlevel) IN (VALUES ('D11', 16), ('D11', 17) )
> ;
>
--------------------------------------------------------------------------=
----
>
> EMPNO WORKDEPT FULLNAME EDLEVEL
> ------ -------- ------------------------------ -------
> 000060 D11 IRVING F STERN 16
> 000150 D11 BRUCE ADAMSON 16
> 000160 D11 ELIZABETH R PIANKA 17
> 000170 D11 MASATO**** J YO****MURA 16
> 000180 D11 MARILYN S SCOUTTEN 17
> 000190 D11 JAMES H WALKER 16
> 000200 D11 DAVID BROWN 16
> 000210 D11 WILLIAM T JONES 17
> 200170 D11 KIYO**** YAMAMOTO 16
>
> 9 record(s) selected.
>
> 2) One usage of generated column is to create a functional
index.http://gr=
oups.google.com/group/comp.databases.ibm-db2/browse_frm/thre...
For point 2: I know of this, unfortunately, it breaks the relational
model, a base table contains a column that is not a column; an index
(functional or not) should not impact a table structure, generated
columns do, in french they call it "une fausse bonne id=E9e" (an idea
that looks good but isn't)
Point 1: what about ( c1,c2,..) >=3D (value1,value2, ...) in where
clause ?
Bernard Dhooghe
|
