I want to replace some string values in a column with numbers and then i want to use this column in a join relation to join with another table. so basically this is about two columns called rating_cd which is in dimension table and orig_perf_rating which is in fact table. Both these columns have a varchar datatype. so i used the below query to replace the values.
'NR','0')) as RATING_CD, ROW_WID, RATING_NAME, RATING_DESC, ACTIVE_FLAG,
SRC_EFF_FROM_DT, SRC_EFF_TO_DT, W_INSERT_DT, W_UPDATE_DT, DATASOURCE_NUM_ID, INTEGRATION_ID, ETL_PROC_WID, TENANT_ID, X_CUSTOM
But problem here is the values in orig_perf_rating column in fact table w_wrkfc_evt_month_f are like (1.0000000, 5.00000000, 3.000000000, 0.00000000000). so now i would like to convert the rating_cd values which are in dimension in to a format such that there can be a join relation with orig_perf_rating. i was able to join these two with out converting rating_cd but just by replacing the values as shown in the above query. but the problem here is the data is coming blank since those two data formats doesn't match. i also used cast function like shown below. but it is of no use.
i want to use the first query in a database view. infact this is a view created for dimension wc_perf_ratings_d and it is an exact replica of it. but the only difference is i want to change the string values in rating_cd to specific numbers as above and change the datatype of rating_cd to match orig_perf_rating. as i already mentioned, initially both these columns are varchar but the data representation is different. so i want to build a materialized view for dimension and i want to import it in to rpd. then i want to join this view with fact table. as of now i can replace these values but i do not know what datatype to assign for the rating_cd column. i do not want the fact table field orig_perf_rating to be changed and i only want to change the dimension field which is rating_cd. this is the scenario and hope i explained it better. So could anybody help me in solving this? This is all about creating a join between the dimension and fact table.