Wednesday, January 16, 2008

OBIEE IsNumeric

I was searching for an 'IsNumeric' function in OBIEE and couldn't find it so I wrote two alternatives. If anybody has better suggestions feel free to report them!

(the report builders didn't have the proper authorisation to make there own dbFunctions)

Version 1 (all OBIEE):

CASE WHEN
LENGTH(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TBLTRUCKS.LICENSEPLATE,'1','
'),'2', ' '),'3', ' '),'4', ' '),'5',' '),'6', ' '),'7',' '),'8',' '),'9','
'),'0',' '),'+',' ' ),'-',' '),'.',' '))) <> 0 THEN 'Non-Numeric' ELSE
'Numeric' END

Version 2 (Doing it on the database):


CASE WHEN EVALUATE('LENGTH(TRIM(TRANSLATE(%1,%2,%3)))' AS DOUBLE PRECISION,
TBLTRUCKS.LICENSEPLATE, '+-.0123456789', ' ' ) IS NULL THEN 'Numeric' ELSE
'Non-Numeric' END

Until next time....

1 comment:

Jeremy said...

Hello John,

This post really got me out of a jam! Thanks a ton, it was exactly what I needed as I couldn't find an "IsNumeric" function myself.

Cheers,

Jeremy