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.
+1 ++ Thomas Dodds.
If you want go your own then column->prop->data format->Custom then ####-##-##
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.
This is not possible I can't educate user's to do all these steps
Yes you are correct when I try to load more records, error appears.
So the left solution is Thomas's one ?
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!!