10 Replies Latest reply: Mar 21, 2013 12:55 PM by Veeresh Rayan RSS

    Convert DateWID in fact to a date

    987521
      All,

      I have a requirement to convert a date_wid into a date in one of my fact tables. Below is the formula that i am trying to use

      CASE WHEN "Oracle Data Warehouse"."Catalog"."dbo"."Fact_W_WRKFC_EVT_MONTH_F_Snapshot"."X_CONTRACT_START_DT_WID" = 0 THEN NULL ELSE Evaluate('TO_DATE(%1,%2)' AS DATE , "Oracle Data Warehouse"."Catalog"."dbo"."Fact_W_WRKFC_EVT_MONTH_F_Snapshot"."X_CONTRACT_START_DT_WID",'YYYYMMDD') END

      It throws me an exception saying 'Incompatible Types' but i have used the same formula in one of my dimension it has worked. Below is the formula that worked for me in the Dimension.

      CASE WHEN "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_EMPLOYEE_D"."CONTRACT_ST_DT_WID" = 0 THEN NULL ELSE Evaluate('TO_DATE(%1,%2)' AS DATE , "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_EMPLOYEE_D"."CONTRACT_ST_DT_WID",'YYYYMMDD') END

      Kindly let me know if i am doing anything wrong, it would be deeply appreciated if anyone could help me.

      Thanks & Regards