the number of values assigned is not the same as the number of

[iren]

I have broken my head trying to understand the reason for the Error message below. It really seems to me that I have the same # of columns in create and execute part!
Actually I already created and uploaded MortalExc table to H data mart into Data warehouse and it is populated with the data. I was able to check it through MS Acccess with ODBC. However when I tried to join Enroll table I got an error like the one below.
Could you please give me a hand? Thank you in advance,
Iren

proc sql;
connect to odbc as Hconnect(datasrc=&datasrc user=&uid password=&pwd);

163 /* Get Current Mem_Num and drop anyone not found in Enroll or where security_ind=Y */
164
165 execute( drop table &tmpschema..MortalExc_ ) by Hconnect;
166
167 execute ( create table &TmpSchema..MortalExc_
168 (id char (29),
169 mc_sub_num char (13),
170 idl char (29),
171 dm_sub_num char (13),
172 mem_key integer,
173 cov_term_dt date,
174 ce_end_dt date,
175 ptag char (8),
176 tg char (30)
177 )
178 in "&twotblspc"
179 not logged initially
180 ) by Hconnect;
181
182 execute ( insert into &TmpSchema..MortalExc
183 select distinct
184 rb.Id,
185 rb.mc_sub_num,
186 rb.idl,
187 rb.dm_sub_num,
188 en.mem_key,
189 en.cov_term_dt,
190 rb.ce_end_dt,
191 rb.ptag,
192 rb.tg
193 from &TmpSchema..MortalExc rb
194 join HPRD.Enroll en
195 on rb.Hedis_Id = en.Hedis_id
196 where en.Security_Ind = 'N'
197 ) by Hconnect;
ERROR: CLI execute error: [IBM][CLI Driver][DB2/AIX64] SQL0117N The number of values assigned is not the same as the number of specified or implied columns. SQLSTATE=42802

 

[Truusvlugindewind]

Looks good from the outside. All the col's in the temp-table are defined as not null. How about the select which is supposed to populate that table? Never NULL's? Try to encapsulate all the columns with the coalesce clause to be sure.
And the column formats cannot be judged form the outside, so why do not you cast the formats to the same format as defined in the temp table.

If that works, remove the extra code column by column so you'll find the error-colomn automaticly.

 

[LKBrwnDBA]

Maybe there are TWO tables:

... etc ...
    execute ( create table &TmpSchema..MortalExc_
-- Check this out ------------------------------^
182 execute ( insert into  &TmpSchema..MortalExc

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[rasETL]

I believe your message is due to the following line...

on rb.Hedis_Id = en.Hedis_id

There is no Hedis_Id on your newly created &TmpSchema..MortalExc Table that your refer to.