Hi:

Metadata for two tables at bottom. Statements below:

This one works fine:

UPDATE MEMPAY2 M2
SET M2.CRDREM_LET = :InvoiceDate, M2.ID =
(SELECT M.ID FROM MEMBERS M
WHERE (M.ID = M2.ID AND
(M.STATUS_MEM = 'ACTIVE' OR M.STATUS_MEM = 'ASSOCIATE')))
WHERE (M2.CRDRPT_RNW IS NOT NULL AND M2.CRDRPT_PD IS NULL AND
M2.CRDREM_LET IS NULL AND
M2.CRDRPT_RNW >= '08/30/2000' AND M2.CRDRPT_RNW <= '11/13/2000')


This one fails both in code and IBConsole with error: Column
Unknown -206

UPDATE MEMPAY2 M2
SET M2.REMIND_LET = :InvoiceDate, M2.ID =
(SELECT M.ID FROM MEMBERS M
WHERE (M.ID = M2.ID AND M.RENEW_DTE = M2.RENEW_DATE AND
(M.STATUS_MEM = 'ACTIVE' OR M.STATUS_MEM = 'ASSOCIATE')))
WHERE (M2.RENEW_DATE IS NOT NULL AND M2.DATE_PAID IS NULL AND
M2.REMIND_LET IS NULL AND
M2.RENEW_DATE >= '08/30/2000' AND M2.RENEW_DATE <= '11/13/2000')

I have looked at this over and over and cannot see a problem with
column name.

Any suggestions, insight appreciated.


Best regards


/* Table: MEMBERS, Owner: SYSDBA */

CREATE TABLE "MEMBERS"
(
"ROA_ID" VARCHAR(5),
"LOAD" VARCHAR(1),
"REF_REV" DOUBLE PRECISION,
"STATUS_REC" VARCHAR(10),
"DATE_UP" DATE,
"ID" DOUBLE PRECISION,
"MEM_PIN" VARCHAR(10),
"STATUS_MEM" VARCHAR(13),
"INV_LET" DATE,
"WELCOM_LET" DATE,
"NEWS_MAIL" VARCHAR(1),
"NEWS_LET" DATE,
"MISUP_DATE" DATE,
"MIS_RPT" DATE,
"LBL_RPT" DATE,
"PRT_LBL" SMALLINT,
"MIS_CNT" SMALLINT,
"UPDATE_CNT" VARCHAR(1),
"TOT_CNT" DOUBLE PRECISION,
"NAMELAST" VARCHAR(40),
"MI" VARCHAR(1),
"NAMEFIRST" VARCHAR(30),
"DBA" VARCHAR(40),
"NUMBER" DOUBLE PRECISION,
"STREET" VARCHAR(35),
"POBOX" DOUBLE PRECISION,
"MEM_OFF" VARCHAR(1),
"CITY" VARCHAR(20),
"STATE" VARCHAR(4),
"ZIP" VARCHAR(12),
"CHG_ZIP" VARCHAR(1),
"DPBC_2" VARCHAR(2),
"DPBC_CHK" VARCHAR(1),
"ONE_PLUS" VARCHAR(1),
"PHONE" VARCHAR(14),
"FAX" VARCHAR(14),
"EMAIL_NAM" VARCHAR(30),
"EMAIL" VARCHAR(30),
"COMMENTS" VARCHAR(255),
"RENEW_DTE" DATE,
"URBANIZATION" VARCHAR(25)
);





/* Table: MEMPAY2, Owner: SYSDBA */

CREATE TABLE "MEMPAY2"
(
"YEARM2" SMALLINT,
"ID" DOUBLE PRECISION,
"RENEW_DATE" DATE,
"REMIND_LET" DATE,
"RENEW_DUES" DOUBLE PRECISION,
"RSTAT_DUE" DOUBLE PRECISION,
"DATE_PAID" DATE,
"CHKCSH_DUE" SMALLINT,
"NUMBER_DUE" DOUBLE PRECISION,
"TENREF_FEE" DOUBLE PRECISION,
"CRDRPT_FEE" DOUBLE PRECISION,
"CRDRPT_RNW" DATE,
"CRD_CANCEL" DATE,
"CRDREM_LET" DATE,
"CRDRPT_PD" DATE,
"CHKCSH_CRD" SMALLINT,
"NUMBER_CRD" DOUBLE PRECISION
);