How can I map numbers to their string equivalents in Perl?

Question

I am writing a script using DBI to execute a select query to an Oracle db. I have successfully able to capture the data but I need help to change the output.

Below is the sample output.

Type
2
6

I want to display 2=>Good and 6=>Bad

Can anyone please suggest me the Perl code to map the output?

Answer 1

Usually the easiest way is to change directly the values outputted by the SQL query. With Oracle you can use DECODE.

SELECT DECODE(MY_TYPE, 2, 'TWO', 6, 'SIX', 'DEFAULT_VALUE') FROM MY_TABLE

The standard SQL way is to use a CASE conditional expression. It is a little more verbose, but more powerful and more portable. It works for example in Oracle, PostgreSQL and MS-SQL.

SELECT 
    CASE 
        WHEN MY_TYPE = 2 THEN 'TWO'
        WHEN MY_TYPE = 6 THEN 'SIX'
        ELSE 'DEFAULT_VALUE'
    END CASE 
FROM MY_TABLE

If you still want to do it in Perl, you might create a Hash. The code sample is quite trivial, and well documented in the link I provided.

Answer 2

# Create a hash of the values you want to output
my %human_text = (2 => 'Good', 6 => 'Bad');

# and then access the hash values like this:
print $human_text{2}; #will output 'Good'
print $human_text{6}; #will output 'Bad'

Answer 3

Create a lookup table in your RDBMS which maps 2 to Good and 6 to Bad. Create an INNER JOIN (or LEFT JOIN if you anticipate having values that will not match the lookup) with your SQL statement (or create a VIEW which returns the JOINed tables). Trying to use Perl or SQL SELECT statements to replace database design is probably a bad idea.

Answer 4

$inWords = ("","very good","good","satisfactory","sufficient","poor","bad")[$number];