Convert Descriptive Flex Fields to Numbers

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:
XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

ransomware cyber attack

Why Is Ransomware Such a Major Threat?

One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing

data dictionary

Tools You Need to Make a Data Dictionary

Data dictionaries are crucial for organizations of all sizes that deal with large amounts of data. they are centralized repositories of all the data in organizations, including metadata such as