i am trying to join physical tables wc_perf_ratings_d and w_wrkfc_evt_month_f with condition as
"Oracle Data Warehouse"."Catalog"."dbo"."Dim_WC_PERF_RATINGS_D"."RATING_CD" = to_char(round( "Oracle Data Warehouse"."Catalog"."dbo"."Fact_W_WRKFC_EVT_MONTH_F"."ORIG_PERF_RATING",2)). But i am getting the below syntax error while applying this.
[nQSError:27002] Near <(>: Syntax error [nQSError:26012]
i used the same condition on database and i got results.
to_char(round(orig_perf_rating,2)) as orig_perf_rating,
from wc_perf_ratings_d, w_wrkfc_evt_month_f
where wc_perf_ratings_d. rating_cd= to_char(round(w_wrkfc_evt_month_f.orig_perf_rating,2));
Here i am trying to convert the format of ORIG_PERF_RATING field and trying to join with RATING_CD in physical layer.
so my question is, is it possible to join in obiee using such conditions? i do not want to convert the field format in ETL and i wanted to do that at obiee level. Could anybody please suggest how to do it?
You can do that using Complex join in Physical layer.
You have to use cast instead of to_char. use the below
"Oracle Data Warehouse"."Catalog"."dbo"."Dim_WC_PERF_RATINGS_D"."RATING_CD" =
cast(round( "Oracle Data Warehouse"."Catalog"."dbo"."Fact_W_WRKFC_EVT_MONTH_F"."ORIG_PERF_RATING",2) as char)
Let me know for any errors. If works mark as correct.
Edited by: Srini VEERAVALLI on Jan 24, 2013 2:11 PM
Thanks for ur inputs. I tried as per your suggestion using cast function instead of to_char but it is throwing me an error saying
[nQSError:22019] Function Round does not support non numeric types.
The datatype of both physical table columns orig_perf_rating and rating_cd is varchar. the values in rating_cd column are like (2.9,2.95,2.96,3,1,4.......) and the values in orig_perf_rating are like (2.90000000000000, 2.950000000000000, 2.9600000000000000,3.00000000000000 .......). so i used to_char function along with round func for orig_perf_raing and joined these two columns in database.
I am using obiee 11g. i used expression builder to build this expression in obiee. just to know, how to build a complex join in physical layer in obiee 11g? in 10g while building physical diagram we have two two individual joins to create. But in 11g we have only one common join. when i am selecting multiple columns of two physical tables while creating join, the join type is automatically changing to complex join in 11g. otherwise it is taking it as normal join. however, the round function is not supporting non numeric type as its operand.
Thanks for the link. i was trying another method in joining the two fields (rating_cd, orig_perf_rating) at database level. As i said both these fields are of varchar datatype. Earlier i tried to convert orig_perf_rating field from number to string. But i want to convert string data in (rating_cd) to number. so i have values in it like (1,2,3,4,bep, ep, vp, nr). Actually i have a requirement to create a database view for the dimension table (wc_perf_ratings_d) having rating_cd field with new datatype (number) instead of string and then join this field with orig_perf_rating in fact table which already has numbers. Along with the number rating fields, there are four particular string fields in rating_cd (bep, ep, vp, nr) as i mentioned. so while converting, i also have to convert these specific string fields in to partcular numbers. (bep=1, ep=5, nr=0, vp=3). Now, all this requirement is at database level as i am creating a db view. so my question is how to apply the to_number conversion function for converting the rating_cd field values (bep, ep, vp, nr) from string to specific mentioned numbers. Could you please help?
for eg: create or replace view as select to_number(rating_cd....................