CODEWITHSUNDEEP
javasqldatabaseoracle-databasehibernate
Haxed
Haxed

Reputation: 2917

Modifying the contents of a result returned by a Oracle Sql query

One of our clients have requested information about customers.

I have written the query below, to extract the list of customers.

select * from customer;

Id  |   Name   |  Status
----+----------+-----------
1       Azam        0
2       Kapil       1
3       Osama       2

But the issue is the "Status" column, which is an enum from the java code(We have used hibernate for ORM). Therefore, it is stored in the database as digits. The problem is, I have to replace the digits in the "Status" column with constants before sending it to the customer, since the customer does not understand digits. I do this by generating an excel file of the result set and amending the Status column values.

E.x. In the Status column: 0 means Prospect, 1 means Active and 2 means Existing.

Question:

Is there a way to modify the result set returned from a query to obtain the following from Oracle alone:

select * from customer;

Id  |   Name   |   Status
----+----------+------------
1       Azam      Prospect
2       Kapil     Active
3       Osama     Existing

Upvotes: 0

Views: 47

Answers (3)

Predrag Maric
Predrag Maric

Reputation: 24423

In addition to other answers, if you want to store string values of Enum constants in the database, use this mapping

@Enumerated(value=EnumType.STRING)
private MyEnum myEnum;

Upvotes: 1

Giova
Giova

Reputation: 1127

I think you can use the decode function in the following way:

select id,name,decode(status,0,'Prospect',1,'Active',2,'Existing) from customer;

Regards Giova

Upvotes: 2

Boneist
Boneist

Reputation: 23578

If you have a table containing the status details, then it should just be a matter of joining to that table and outputting the status description.

If you don't, and you know the status numbers/description won't change, then you could use a case statement:

select id, name, case when status = 0 then 'Prospect'
                      when status = 1 then 'Active'
                      when status = 2 then 'Existing'
                      ...
                      else null -- can be omitted if null is the desired default, or change the null to the output required
                 end status
from customer;

Upvotes: 1

Related Questions