Case, If or something else?

[woodman]

Situation: I need to return a value based upon two different fields from the same table. Here is the scenario:

If table1.prstatus = 1 then task_status = ‘project’
If table1.prstatus = 2 then task_status = ‘phase’
If table1.prstatus = 3 then task_status = ‘task’
If table1.prismilestone > 0 then task_status = ‘milestone’

I tried a CASE/WHEN and the first three options for ‘prstatus’ work great:

SELECT
CASE prstatus
when 0 then 'Project'
when 1 then 'Stage'
when 2 then 'Phase'
/*
when prismilestone > 0 then 'Milestone'
*/
END AS task_status
FROM table1

Where I get goofed up is on the last item where I change the originating field to ‘prismilestone.’ Any suggestions????

Thanks in advance.

[skhanal]

Try this

SELECT
CASE
when prstatus = 0 AND prismilestone <> 0 then 'Project'
when prstatus = 1 AND prismilestone <> 0 then 'Stage'
when prstatus = 2 AND prismilestone <> 0 then 'Phase'
when prismilestone > 0 then 'Milestone'
END AS task_status
FROM table1

[woodman]

PERFECT!!! Thanks a lot!!