This discussion is archived
1 Reply Latest reply: Jan 30, 2013 3:08 AM by Srini VEERAVALLI RSS

Replacing string values with numbers and applying data conversion

986915 Newbie
Currently Being Moderated
Hi,
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.
SELECT
(REPLACE(
REPLACE(
REPLACE(
REPLACE(rating_cd,'BEP','1'),
'EP','5'),
'VP','3'),
'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
FROM wc_perf_ratings_d;

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.

SELECT
CAST(REPLACE(
REPLACE(
REPLACE(
REPLACE(rating_cd,'BEP','1'),
'EP','5'),
'VP','3'),
'NR','0')as FLOAT) 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
FROM wc_perf_ratings_d;

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.


Thank you.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points