Case expression using % 1

[sunixadm]

Hi masters of SQL,

I can't seem to get this query right and would appreciate any help you might offer.

I have an application that stores log data in a 10g db and want to get information regarding process starts and the completion status of the process.

Here is what I have tried:

select name, time, case events
when '%initiated%' then initiated
when '%success%' then success
when '%failure%' the failured
else 'none'
end as result
from log where (events like '%initiated%' or events like '%success%' or events like '%failure%')
order by name desc, time;

My results look like this

NAME TIME RESULT
-------- -------------------- ------------------
NWAB27G 28-03-2008 07.44.33 none
GREI99N 28-03-2008 07.54.27 none
.
.
.
.
.
This is an example of a simple query for similar data

select events from log where events like '%initiated%';

EVENTS
-------------------------------------------------
your process 11189 was initiated at connection
.
.
.
.
Here is an example for a "success"

select events from log where events like '%success%';

EVENTS
-------------------------------------------------
your process 11189 completed with status success
.
.
.
.
Here is an example for a "failure"

select events from log where events like '%failure%';

EVENTS
-------------------------------------------------
your process 22730 completed with status failure
.
.
.
.

Thanks in advance for your help.
-Joe

 

[Dagon]

I'm not quite sure what problem you are getting, but I guess it isn't handling the % in the CASE statement. You would have to write it as:

select name, time,
case
when events like '%initiated%' then initiated
when events like '%success%' then success
when events like '%failure%' then failured
else 'none'
end as result

 

[sunixadm]

thanks Dagon.

That hit the nail on the head.
Cheers,
-Joe