Bernard Dhooghe wrote:
> 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) = ('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) = 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://groups.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ée" (an idea
> that looks good but isn't)
Let's not get all excited about this relational part.
The REASON for the avoidance of functionally dependent columns is the
risk of inconsistency. expression-generated columns assert consistency.
Thus there is no problem. So let's not get hung up by the letter of the
law and stick with it's spirit.
This good idea has since been copied by both MS SQL Server and Oracle
(which has expression based indexes, so they must have seen some
goodness in it beyond mere indexing).
I can't help but being proud :-)
> Point 1: what about ( c1,c2,..) >= (value1,value2, ...) in where
> clause ?
Believe it or not. Actually making some progress towards your pet peeve.
Don't give up hope.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
|
