I'm getting an odd error when I try to run a select statement in my
stored procedure. I need to select records where one field = the first
host variable, and the second host variable is between the values of
two other columns. The problem seems to occur when I check all three
conditions. When I have only two of the conditions, the procedure runs
to completion and I get a resultset.
Interestingly, I created a MS Access database with a link to the table
referenced below (via OLEDB). I can get many (if not all) of the
statements below to work in an Access query.
Can anyone help me figure out the true problem here? TIA!
The error message is:
A database manager error occurred.[IBM][CLI Driver][AS] SQL0303N A
value cannot be assigned to a host variable in the SELECT, VALUES, or
FETCH statement because the data types are not compatible.
SQLSTATE=22001
Here are the statements I've tried so far:
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRESS AS ADDR WHERE
ADDR.ADR_POLICY_NO = ? AND (ADDR.ADR_THRU_SEQ_NO >= ' || POLSEQ || ')
AND (ADDR.ADR_FROM_SEQ_NO <= ' || POLSEQ || ')';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRESS AS ADDR WHERE
ADDR.ADR_POLICY_NO = ? AND (ADDR.ADR_THRU_SEQ_NO >= ' || POLSEQ || ')
AND (INT(ADDR.ADR_FROM_SEQ_NO) <= ' || POLSEQ || ')';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRESS AS ADDR WHERE
ADDR.ADR_POLICY_NO = ? AND ' || POLSEQ || ' BETWEEN
ADDR.ADR_FROM_SEQ_NO AND ADDR.ADR_THRU_SEQ_NO';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRESS AS ADDR WHERE
ADDR.ADR_POLICY_NO = ? AND ' || POLSEQ || ' <= ADDR.ADR_THRU_SEQ_NO AND
' || POLSEQ || ' >= ADDR.ADR_FROM_SEQ_NO';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRESS AS ADDR WHERE
ADDR.ADR_POLICY_NO = ? AND ' || POLSEQ || ' >= ADDR.ADR_FROM_SEQ_NO AND
' || POLSEQ || ' <= ADDR.ADR_THRU_SEQ_NO';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRESS AS ADDR WHERE
ADDR.ADR_POLICY_NO = ? AND INT(' || POLSEQ || ') >=
ADDR.ADR_FROM_SEQ_NO AND INT(' || POLSEQ || ') <=
ADDR.ADR_THRU_SEQ_NO';
--SET stmADDR = 'SELECT ADDR.* FROM (SELECT ADDR.* FROM
TESTFILES.ADDRESS AS ADDR WHERE ADDR.ADR_POLICY_NO = ? AND
ADDR.ADR_THRU_SEQ_NO >= ' || POLSEQ || ') AS ADDR WHERE
ADDR.ADR_POLICY_NO = ? AND ADDR.ADR_FROM_SEQ_NO <= ' || POLSEQ;
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRESS AS ADDR WHERE
ADDR.ADR_POLICY_NO = ? AND ' || POLSEQ || ' BETWEEN
INT(ADDR.ADR_FROM_SEQ_NO) AND INT(ADDR.ADR_THRU_SEQ_NO)';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRESS AS ADDR WHERE
ADDR.ADR_POLICY_NO = ? AND INT(' || POLSEQ || ') BETWEEN
INT(ADDR.ADR_FROM_SEQ_NO) AND INT(ADDR.ADR_THRU_SEQ_NO)';
--SET stmADDR = 'SELECT tblADDRESS.* FROM (SELECT ADDR.* FROM
TESTFILES.ADDRESS AS ADDR WHERE ADDR.ADR_POLICY_NO = ? AND
ADDR.ADR_THRU_SEQ_NO >= ' || POLSEQ || ') AS tblADDRESS WHERE
tblADDRESS.ADR_FROM_SEQ_NO <= ' || POLSEQ;
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRESS AS ADDR WHERE
ADDR.ADR_POLICY_NO = ? AND CHAR(' || POLSEQ || ') BETWEEN
CHAR(ADDR.ADR_FROM_SEQ_NO) AND CHAR(ADDR.ADR_THRU_SEQ_NO)';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRESS AS ADDR WHERE
' || POLSEQ || ' >= ADDR.ADR_FROM_SEQ_NO AND ' || POLSEQ || ' <=
ADDR.ADR_THRU_SEQ_NO AND ADDR.ADR_POLICY_NO = ?';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRESS AS ADDR WHERE
ADDR.ADR_POLICY_NO = ? AND (ADDR.ADR_THRU_SEQ_NO >= ?) AND
(ADDR.ADR_FROM_SEQ_NO <= ' || POLSEQ || ')';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRESS AS ADDR WHERE
ADDR.ADR_POLICY_NO = ? AND (ADDR.ADR_THRU_SEQ_NO >= ?) AND
(ADDR.ADR_FROM_SEQ_NO <= ?)';
-- Lines below work, but don't check all three conditions
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRESS AS ADDR WHERE
ADDR.ADR_POLICY_NO = ? AND ADDR.ADR_THRU_SEQ_NO >= ' || POLSEQ;
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRESS AS ADDR WHERE
ADDR.ADR_POLICY_NO = ? AND ADDR.ADR_FROM_SEQ_NO <= ' || POLSEQ;
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRESS AS ADDR WHERE
' || POLSEQ || ' >= ADDR.ADR_FROM_SEQ_NO AND ' || POLSEQ || ' <=
ADDR.ADR_THRU_SEQ_NO';
PREPARE sADDR FROM stmADDR;
OPEN cADDR USING POLICYNO;
Additional Info:
I am using DB2 UDB v 8.1 running on an iSeries model 270.
I coded and tested my stored procedure using the DB2 Development
Center.