DB2 FOR LUW / DB2FORLUW / A10 - DB2

You might get error SQL0901N with Reason "column number of 
range" when you execute an SQL statements which has both: 
1) a WHERE clause that uses the CONTAINS function, and: 
2) a FROM clause that includes more than one view, if the DDL 
for both views includes UNION ALL. 
 
For example: 
 
CREATE VIEW view1 
AS 
SELECT ... FROM tabA ... 
UNION ALL 
SELECT ... FROM tabB ... 
; 
 
CREATE VIEW view2 
AS 
SELECT ... FROM tabC ... 
UNION ALL 
SELECT ... FROM tabD ... 
; 
 
SELECT 
 ... 
FROM 
 view1 JOIN view2 ... 
WHERE 
 CONTAINS ... 
; 
 
 
The call stack is as follows: 
 
sqlnq_ftb::num2fcs 
sqlnq_handle_resolved_col_ref 
sqlnr_ftslpd_thr_union 
sqlnr_text_func_prdpd 
sqlnr_optprep 
sqlnr_optprep_action 
sqlnr_comp 
sqlnr_seq 
sqlnr_rcc 
sqlnr_exe 
sqlnn_cmpl 
sqlnn_cmpl 
sqlra_compile_var 
sqlra_find_var 
sqlra_get_var 
sqlrr_prepare

**************************************************************** 
* USERS AFFECTED:                                              * 
* UNKNOWN                                                      * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to db2_v101fp3                                       * 
****************************************************************

As a workaround, you can amend the SQL statement so that the 
CONTAINS function is no longer in the main WHERE clause but in 
the WHERE clause of subselect and applies directly to the the 
tables in a view instead, like this: 
 
SELECT ... 
FROM 
( 
 SELECT * FROM tabA WHERE CONTAINS ... 
 UNION ALL 
 SELECT * FROM tabB WHERE CONTAINS ... 
) AS view1 
JOIN view2 ... 
;

DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

03.04.2013
28.10.2013
28.10.2013