Convertion of WID column to date column in BMM layer

Helan Kumar

TO_DATETIME(CAST ("Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_DAY_D_Common"."CAL_WEEK_START_DT_WID" AS CHAR), 'YYYY-MM-DD')

is failing with below error(screenshot). The report query generated will work in SQLDevloper if i remove distinct from the sql. Please guide me in handling this.

Basically i have an wid column (format 20160407 i.e., YYYYMMDD) need to be displayed in date format and this has to be done in RPD, because user needs it for adhoc reporting.

Thomas Dodds

Why aren't you using the W_DAY_D date column based on the lookup from the WID?  Basically a simple alias of the W_DAY_D can get you the date column (it's a self-join) ; then you have no conversion to do ... conversion is clunky and slow.  Let the DB handle it in a physical join:

"Dim_W_DAY_D_Common"."CAL_WEEK_START_DT_WID" joins to "Dim_W_DAY_D_WeekStart"."ROW_WID"   then model it from there ... and use the date in a logical column called "Week Start Date" sourced from the "Dim_W_DAY_D_WeekStart" alias.

SriniVEERAVALLI

+1 ++ Thomas Dodds.

If you want go your own then column->prop->data format->Custom then ####-##-##

JustTheFacts

Thomas has the best design idea, but to address the question you are asking (and maybe help with future issues troubleshooting SQL), I would point out that SELECT DISTINCT requires processing of all rows, to ensure a distinct result. Without DISTINCT, Oracle can just start feeding rows (the first 50 in your example) as it gets them. My guess is that somewhere after row 50, there is a WID that is not valid for the TO_DATE() function. Select MIN(CAL_WEEK_START_DT_WID) and MAX(CAL_WEEK_START_DT_WID) to look for outliers. Again, just a guess, based on it failing when you add DISTINCT.

After that, build an alias of the DAY table and join to it, as Thomas suggested.

Helan Kumar

This is not possible I can't educate user's to do all these steps

Helan Kumar

Yes you are correct when I try to load more records, error appears.

So the left solution is Thomas's one ?

SriniVEERAVALLI

Helan,

Its up to you. No one is forcing you to go with a specific solution. grab whats suites you, thats it!!

Dont forget to mark on suggestion!!