SQL Query - modifying return values

[whitsey]

How can I modify return values?

i.e. I perform a query:

SELECT STATUS
FROM USERS

RESULTS
=======
1
2
1
1
1
2
2
3
4

I want to replace the results to display:

RESULTS
=======
Active
Inactive
Active
Active
Active
Inactive
Inactive
Trial
Demonstration

Is there a way to do this within the SQL statement???

[whitsey]

On May 23, 10:27 pm, swami <[e-mail]:

db2 =select case s1 when 0 then 'Active' when 1 then 'InActive' end
"STATUS" f
rom statustable

STATUS
--------
InActive
InActive
InActive
Active
Active
Active
Active
InActive

8 record(s) selected.

Thanks swami

[Joachim Banzhaf]

Hi whitsey,

if you have a table, that relates the numbers to strings, use this table to
join. If not, you could use a common table expression "result" and join it
like this:

with result( num, str ) as
(
values (1, "Active"),
(2, "Inactive"),
(3, "Trial"),
(4, "Demo")
)
select result.str
from users, result
where users.status = result.num

Not tested, might need minor adjustments, but you get the idea?

HTH,

Joachim Banzhaf
whitsey wrote:

How can I modify return values?

i.e. I perform a query:

SELECT STATUS
FROM USERS

RESULTS
=======
1
2
1
1
1
2
2
3
4

I want to replace the results to display:

RESULTS
=======
Active
Inactive
Active
Active
Active
Inactive
Inactive
Trial
Demonstration

Is there a way to do this within the SQL statement???