RPD. CAST in BMM

aPsikus

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.

Bose Pandian

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

aPsikus

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.

Robert Angel

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?

aPsikus

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.

Gianni Ceresa

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 )

Robert Angel

Thanks Gianni, I was asking about rpd datatype, but this obviously was not obvious to aPsikus...

Robert Angel

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...

Bose Pandian

you can put varchar(3). absolutely there is nothing wrong with it.

Robert Angel

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

aPsikus

In RPD it is DOUBLE.