sql - how to use distinct on two columns in union query

I would like to perform a "distinct" on the following columns but cannot seem to get the formatting right:

C_TYP_SYS_IDV and I_SYS_IDV

any ideas?

THANKS!

create volatile table dt as (
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,
             gpv.c_val,
             gpv.i_val,
             gcv.c_pol


            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 betwee开发者_如何学Pythonn '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,
              ''

        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 can't do DISTINCT on just a few fields out of your list.

Those DISTINCT values represent rows. If you want to only use those DISTINCT rows, you need to use GROUP BY clause. Can you explain further what you are after?