Create view with Union give error in IBM i

Question

HI I am trying to add one table data to another to create the needed data set. I am doing a UNION via the green screen STRSQL. This gives an error message: COLUMN LIST REQUIRED' in CPYF add, you don't have to do anything, whne the legnths and attibutes of each fields are the same. but here not sure what to do.

                 create view astccdta.eoddetails as                                
                 SELECT LINTOT, ODORD#, ODSEQ#, ODLSTC, ODORDD, ODORDT, ODPRLC,    
                 ODPRT#, ODSHP#, ODNTU$, ODRQSD FROM astccdta.eoddetaila UNION     
                 SELECT LINTOT, OUORD#, OUSEQ#, OULSTC, OUORDD, OUORDT, OUPRLC,    
                 OUPRT#, OUSHP#, OUNTU$, OURQSD FROM astccdta.eoddetailh           

Answer 1

The first thing to try is to add the column lists:

create view astccdta.eoddetails(LINTOT, ODORD#, ODSEQ#, ODLSTC, ODORDD, ODORDT, ODPRLC, ODPRT#, ODSHP#, ODNTU$, ODRQSD)                            
      SELECT LINTOT, ODORD#, ODSEQ#, ODLSTC, ODORDD, ODORDT, ODPRLC,    
             ODPRT#, ODSHP#, ODNTU$, ODRQSD
      FROM astccdta.eoddetaila
      UNION     
      SELECT LINTOT, OUORD#, OUSEQ#, OULSTC, OUORDD, OUORDT, OUPRLC,    
             OUPRT#, OUSHP#, OUNTU$, OURQSD
      FROM astccdta.eoddetailh  ;

Perhaps this is required for STRSQL.

DB2 definitely accepts views without explicit column names.

EDIT:

You may also be able to do this:

create view astccdta.eoddetails as                            
      SELECT LINTOT, ODORD#, ODSEQ#, ODLSTC, ODORDD, ODORDT, ODPRLC,    
             ODPRT#, ODSHP#, ODNTU$, ODRQSD
      FROM astccdta.eoddetaila
      UNION     
      SELECT LINTOT, OUORD# as ODORD#, OUSEQ# as ODSEQ#, OULSTC as ODLSTC,
             OUORDD as ODORDD, OUORDT as ODORDT, OUPRLC as ODPRLC,    
             OUPRT# as ODPRT#, OUSHP# as ODSHP#, OUNTU$ as ODNTU$, OURQSD as ODRQSD
      FROM astccdta.eoddetailh  ;