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.ADDRE SS 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.ADDRE SS AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND (ADDR.ADR_THRU_ SEQ_NO >= ' || POLSEQ || ')
AND (INT(ADDR.ADR_F ROM_SEQ_NO) <= ' || POLSEQ || ')';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND ' || POLSEQ || ' BETWEEN
ADDR.ADR_FROM_S EQ_NO AND ADDR.ADR_THRU_S EQ_NO';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND ' || POLSEQ || ' <= ADDR.ADR_THRU_S EQ_NO AND
' || POLSEQ || ' >= ADDR.ADR_FROM_S EQ_NO';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND ' || POLSEQ || ' >= ADDR.ADR_FROM_S EQ_NO AND
' || POLSEQ || ' <= ADDR.ADR_THRU_S EQ_NO';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND INT(' || POLSEQ || ') >=
ADDR.ADR_FROM_S EQ_NO AND INT(' || POLSEQ || ') <=
ADDR.ADR_THRU_S EQ_NO';
--SET stmADDR = 'SELECT ADDR.* FROM (SELECT ADDR.* FROM
TESTFILES.ADDRE SS AS ADDR WHERE ADDR.ADR_POLICY _NO = ? AND
ADDR.ADR_THRU_S EQ_NO >= ' || POLSEQ || ') AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND ADDR.ADR_FROM_S EQ_NO <= ' || POLSEQ;
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND ' || POLSEQ || ' BETWEEN
INT(ADDR.ADR_FR OM_SEQ_NO) AND INT(ADDR.ADR_TH RU_SEQ_NO)';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND INT(' || POLSEQ || ') BETWEEN
INT(ADDR.ADR_FR OM_SEQ_NO) AND INT(ADDR.ADR_TH RU_SEQ_NO)';
--SET stmADDR = 'SELECT tblADDRESS.* FROM (SELECT ADDR.* FROM
TESTFILES.ADDRE SS AS ADDR WHERE ADDR.ADR_POLICY _NO = ? AND
ADDR.ADR_THRU_S EQ_NO >= ' || POLSEQ || ') AS tblADDRESS WHERE
tblADDRESS.ADR_ FROM_SEQ_NO <= ' || POLSEQ;
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND CHAR(' || POLSEQ || ') BETWEEN
CHAR(ADDR.ADR_F ROM_SEQ_NO) AND CHAR(ADDR.ADR_T HRU_SEQ_NO)';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
' || POLSEQ || ' >= ADDR.ADR_FROM_S EQ_NO AND ' || POLSEQ || ' <=
ADDR.ADR_THRU_S EQ_NO AND ADDR.ADR_POLICY _NO = ?';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS 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.ADDRE SS 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.ADDRE SS AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND ADDR.ADR_THRU_S EQ_NO >= ' || POLSEQ;
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND ADDR.ADR_FROM_S EQ_NO <= ' || POLSEQ;
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
' || POLSEQ || ' >= ADDR.ADR_FROM_S EQ_NO AND ' || POLSEQ || ' <=
ADDR.ADR_THRU_S EQ_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.