Issue with union query - select list not compatible

Been working on this query for some time now... Keep getting the error "Corrosponding select-list expressions are not compatible. I am selecting the same # of columns in each select statement.

create volatile table dt as (
SELECT 

            gcv.I_SYS_IDV,
             gcv.i_pln,
             gcv.c_typ_cov,
             gcv.d_eff,
             gcv.d_eff_pln,
          开发者_StackOverflow中文版   gcv.c_sta,
            gcv.d_sta,
            gcv.c_mde_bft_fst,
            gcv.a_bft_fst,
            gcv.c_mde_bft_sec,
             gcv.a_bft_sec,
            gcv.c_mde_bft_trd,
             gcv.a_bft_trd,
             gcv.p_cre_hom,
             gcv.c_cl_rsk,
             gpv.c_val,
             gpv.i_val,
             gcv.c_pol,
             gpv.i_prv



            FROM Pearl_P.tltc906_gcv gcv,
             pearl_p.tltc912_gpv gpv

              WHERE  gcv.i_pln > 0
            AND gcv.i_pln = gpv.i_pln
            and gpv.i_prv = '36'
            and gcv.c_pol between 'lac100001' and 'lac100004'

         UNION

             SELECT
             gcv.I_SYS_IDV,
             gcv.i_pln,
             gcv.c_typ_cov,
             gcv.d_eff,
             gcv.d_eff_pln,
             gcv.c_sta,
             gcv.d_sta,
             gcv.c_mde_bft_fst,
             gcv.a_bft_fst,
             gcv.c_mde_bft_sec,
             gcv.a_bft_sec,
             gcv.c_mde_bft_trd,
             gcv.a_bft_trd,
             gcv.p_cre_hom,
             gcv.c_cl_rsk,
             gcv.c_pol,
             gpv.i_val,
            gpv.i_pln,
             gpv.i_prv


        FROM Pearl_P.tltc906_gcv gcv,
        pearl_p.tltc912_gpv gpv


            where NOT EXISTS(
            SELECT 1
            FROM pearl_p.tltc906_gcv gcv,
             pearl_p.tltc912_gpv gpv

            WHERE  gcv.i_pln > 0
            AND gcv.i_pln = gpv.i_pln
            and gpv.i_prv = '36'
            )
        ) with data 
PRIMARY INDEX (i_sys_idv)
on commit preserve rows;

You should check the data types of each column. The data types must be compatible between each SELECT statement.

The last 4 values of your second select statement don't match the ones in your first statement. Try naming(using aliases) those columns the same thing(pairing them). To union you need to have the same columns between the sets.

Check out: http://msdn.microsoft.com/en-us/library/ms180026.aspx

The following are basic rules for combining the result sets of two queries by using UNION:

The number and the order of the columns must be the same in all queries.

The data types must be compatible.

Are the data types of each column the same in both portions of the query?

If the first character of the column name indicates the data type (i = integer, c = character, etc.) I'm guessing that the problem is with the second to last column in the select list. The first query is selecting gcv.c_pol, the second query is selecting gpv.i_pln.

Start commenting-out lines until it works. I.e., comment out all of the fields in each select list, and then one-by-one, un-comment the first one out, then the second, etc. You'll find it eventually.