We had a requirement in which the a column had either pure numbers such as 123 or pure alphabets such as QWED. The requirement was to display 'Y' in a column if the value was a number and display 'N' if it was a character string.
While we can anyways have a case statement for each of the numbers i.e. 1,2,3,4,5,6,7,8,9,0, I found an easy way out by using the ASCII function.
ASCII function in OBIEE returns the ASCII code of the 1st character in the string.
Let me show you what I am talking about. Have a look at the screenshot below
The 2nd column is the customer number and the 1st column is the ASCII value of customer number.
So we see that the ASCII value in OBIEE for 999, 998 and 997 is 57. Why? Because they are start with 9 and OBIEE gets the ASCII value of the 1st character using its ASCII function.
The ASCII value of 0 is 48 and the ASCII value of 9 is 57. So if we have a column that has pure numbers or pure characters and we wish to apply a logic based on whether the value is a number or a character, we can use the following in column formula.
CASE WHEN ASCII(<column_name>) between 48 and 57 then <some_logic> else <some_other_logic> END
Till next time ..
No comments:
Post a Comment