8 Replies Latest reply: Apr 10, 2018 9:26 AM by Bill Markham RSS

    Deriving distinct columns from a table

    Cherl Zehnder

      i'm creating a qvd from a fact table that has multiple date dimensions associated with it.

      I want to do a couple of things.

      •Pull only the dates I will be loading from the fact table which I can do with the following query.

      •Display only the distinct Years from those dates to the users.

      The problem is that these are actually different dimension keys so I will get duplicate years.

      Is there a way to extract just the unique years but still have the dim keys to associate with the model?

      select distinct wpdd.plcy_dt_dim_key, PLCY_DT_YR_YY as "Policy Year",wpdd.*

        From AWH.WR_MTD_COMB_DTL_F wmcdf, AWH.WR_VAL_DT_D, awh.wr_plcy_dt_d wpdd

      where wmcdf.VAL_DT_DIM_KEY = WR_VAL_DT_D.VAL_DT_DIM_KEY

         and wpdd.plcy_dt_dim_key = wmcdf.plcy_dt_dim_key

         and AWH.WR_VAL_DT_D.VAL_DT_DAY_MDY >= '01-JAN-2013'

         and AWH.WR_VAL_DT_D.VAL_DT_DAY_MDY <= '01-JAN-2018'

        order by 2