Compatibility issue between DDS and DDL

[FaStOnE]

Is there a Value Compatibility issue between a DDS and DDL?

I have an Original Table with a DDS FieldA defined as 15a
I have a new Table with DDL that has FieldA defined as Char(15)

Both tables are the same layouts (sans a few extra fields in the 2nd table)

Performing the following errors out:

Insert Into Table1
Select * from Table2
Where Condition = "what I want"

I get a SQL0408 Error: Value for column or variable FieldA not compatible.



What gives??

[tomholden]

Is there a Value Compatibility issue between a DDS and DDL?

I have an Original Table with a DDS FieldA defined as 15a
I have a new Table with DDL that has FieldA defined as Char(15)

Both tables are the same layouts (sans a few extra fields in the 2nd table)

Performing the following errors out:

Insert Into Table1
Select * from Table2
Where Condition = "what I want"

I get a SQL0408 Error: Value for column or variable FieldA not compatible.



What gives??

if table2 has additional columns then you should list the columns from table2 to insert. when you select * you are pulling in all columns in the order they are in the table. for example:

table1 has fieldA char(15), fieldb char(10) and table2 has fielda char(15), fieldc char(20), fieldb char(10) then select * would try to put table2.fieldc in the table1.fieldb
clear as mud?

[kitvb1]

Where are these new fields? At the end? or in the middle?

[FaStOnE]

Kinda all over the place Kit... I guess I'll just have to identify each field and do a detailed select... ARGH!!

[Denny]

Why don't you just use CPYF ... FMTOPT(*MAP)

[MichaelCatalani]

You could also use a cursor, specify data structures for each file, and do an eval-corr. This way, you would let the smart language (RPG) handle field mapping.

[B.Hauser]

If it is no one shot, you may also create a view built over the file with the greater number of rows, with all columns in the same sequence as in the other file.
Then execute

insert into myFile2
as (select * from MyView1); 

Birgitta

[FaStOnE]

I ended up doing what Michael was eluding to. Loop through the Header ... EVAL-CORR the values and Insert the DS into the Target Table. It actually ended up being the correct way to do things as I had a few other steps to perform at the same time... all things are good.

Thanks for the feedback...