DB2 FOR LUW / DB2FORLUW / 970 - 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:                                              * 
* Users of DB2 for Linux, UNIX and Windows                     * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* .                                                            * 
****************************************************************

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 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem was first fixed in Version 9.7 Fix Pack 8. 
At a minimum, this fix should be applied on the server.

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

forerunner  : APAR is sysrouted TO one or more of the following: IC91301 
follow-up : 

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

17.12.2012
15.04.2013
15.04.2013

9.7.FP8