Oracle Quick Tips #2

In this blog I will be explaining how to convert Numbers to Roman Numerals using a SQL query.

Existing Report: 

• I have an Interactive Report where I  have four columns (ID, Name, Salary and Rank).
• I am displaying the rank of the employees that is ordered by the salary in descending order. 

Requirement:

• My requirement is to display the rank in roman numbers. 

How can this be achieved using a simple SQL query? Follow the below steps to achieve this.

Let me explain this with an example:

1) Create a table

create table  t_employee
(
  emp_id       number,
  emp_name     varchar2(100),
  salary       number 
);

2) Add the required data into the table.

3) Execute the below SQL query 

select emp_id,
       emp_name,
       to_char(salary_rank, 'RN') as roman_sequence 
    from
        (
            select emp_id,
                   emp_name,
                   dense_rank() over(order by salary desc) salary_rank
                from t_employee    
        );

Note: In the above code you can replace 'RN' with 'rn' to display the roman letters in lower case.

Happy CODING !!!

Thank you :)