Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 17 Oracle Analytics Lounge
- 218 Oracle Analytics News
- 44 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 81 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
RPD. CAST in BMM
Hi,
What is wrong with this formula:
CASE WHEN "WORKFORCE".""."HRDWH"."DimRating_MD"."EP_RATING" = 999 THEN 'Not Rated' ELSE CAST("WORKFORCE".""."HRDWH"."DimRating_MD"."EP_RATING" AS CHAR(1)) END
that it do not work when placed in RPD BMM as derived from physical mappings?
In OBIEE still 999 is displayed instead of 'Not related'. Other values are from 1 to 5.
Requirement is that this dimension will be used as filter in Dashboard prompt. I have also tried to write similar code as SQL:
SELECT CASE WHEN "Ratings"."Rating" = 999 THEN 'Not Rated' ELSE CAST("Ratings"."Rating" AS CHAR(1)) END FROM "WORKFORCE"
for default value for filter, but it gives error.
Answers
-
Hi,
Cast is string function basically. so try the below:
CASE WHEN CAST("WORKFORCE".""."HRDWH"."DimRating_MD"."EP_RATING" AS VARCHAR(4)) = '999' THEN 'Not Rated' ELSE CAST("WORKFORCE".""."HRDWH"."DimRating_MD"."EP_RATING" AS CHAR(1)) END
because first conversion is from number to character and in second, you are converting the entire column to character.
Hope now it will works
0 -
Any reason why in OBIEE (report) it works fine without CAST for first part?
Cast is string function basically. so try the below:CASE WHEN CAST("WORKFORCE".""."HRDWH"."DimRating_MD"."EP_RATING" AS VARCHAR(4)) = '999' THEN 'Not Rated' ELSE CAST("WORKFORCE".""."HRDWH"."DimRating_MD"."EP_RATING" AS CHAR(1)) ENDbecause first conversion is from number to character and in second, you are converting the entire column to character.I get error:
Error Codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000)State: HY000. Code: 43119. [nQSError: 43119] Query Failed: (HY000)State: HY000. Code: 17001. [nQSError: 17001] Oracle Error code: 25137, message: ORA-25137: Data value out of range at OCI call OCIStmtFetch. (HY000)State: HY000. Code: 17012. [nQSError: 17012] Bulk fetch failed. (HY000), doesnt matter if I will update formula as Derived from physical mapping or as Derived from Existing column. Type of new column is VARCHAR(9).
And formula without CAST for first part, is returning same error.
0 -
Hi,
your second cast implies that all EP_RATINGS are one digit apart from 999 - is this really the case?
Also, why do you store your 3 digit number in a 4 length varchar?
Why not; -
CASE WHEN "WORKFORCE".""."HRDWH"."DimRating_MD"."EP_RATING" <= 5 THEN CAST("WORKFORCE".""."HRDWH"."DimRating_MD"."EP_RATING" AS CHAR(3) ELSE 'Not Rated') END
Also is your EP_RATING is definitely stored as a number?
0 -
Hi,
Yes in DB, EP_RATING is number.
From 0 to 5 (0, 1, 1.5, 2, 2.5, 3, 3.5, 4, 4.5, 5) and 999.
0 -
aPsikus wrote:Yes in DB, EP_RATING is number.
And what is it in your RPD? Check the physical column (wouldn't be the first time that a number become a varchar or something else )
0 -
Thanks Gianni, I was asking about rpd datatype, but this obviously was not obvious to aPsikus...
0 -
The reason I asked on why put it in a varchar2(4) is that extra space may cause it to be '999 ' or similar, hence I would either go for the 3 characters or I would trim it...
0 -
you can put varchar(3). absolutely there is nothing wrong with it.
0 -
What I am telling you though, is your varchar2(4) is probably part of your problem, because I have observed that frequently cast puts extra characters that you did not want / anticipate
0 -
In RPD it is DOUBLE.
0
