Convert Descriptive Flex Fields to Numbers

Often, data in Oracle applications is stored in extended columns known as Descriptive Flex Field (DFF) columns. These are character columns which you can map to store any kind of information. On the front end, it is possible to apply validations like numeric, date, or character but the ultimate values that get stored inside these flex columns are usually just character strings.

This causes trouble when it comes time for reporting. Though it’s not a problem if you just want to display the values stored in the column, the pain starts when you want to do some logic on top of those numeric values.

This problem can be ameliorated by using the to_number function that Oracle provides?however, this assumes that the column contains only numeric values and no “white space,” or, “non-numeric” characters. If the column contains non-numeric characters, the to_number function will bomb right in your face. Descriptive Flex Fields are context dependent, which means that each attribute column can store different data types for a given row. So, for row1, the attribute1 column might store a numeric value, row2 might store a date value and so on.

To deal with the above situation, create a new generic function:

function get_number(p_val varchar2) return number isl_value number;beginl_value := to_number(p_val);return l_value;exceptionwhen others thenreturn -1;end;

The above wrapper function tries to convert the character value into a number. If it succeeds, then it returns the value. If it fails, it returns a -1.

You can modify this function to return any value depending on the requirement?for example, to return a zero value instead of -1. This example uses -1 to highlight any data issues. If you return a zero value, there is a possibility that the user might overlook the data error. Depending on the situation, you should return a value which will tell the user that the data is not correct?like a huge negative value.

You can also enhance this function to strip out non-numeric values (like spaces, etc.) using the ltrim, rtrim, or replace functions but take care not to overload the wrapper function with more logic and calculations as this will effect the performance of your reports.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin


The Latest

iOS app development

The Future of iOS App Development: Trends to Watch

When it launched in 2008, the Apple App Store only had 500 apps available. By the first quarter of 2022, the store had about 2.18 million iOS-exclusive apps. Average monthly app releases for the platform reached 34,000 in the first half of 2022, indicating rapid growth in iOS app development.

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a