Case statement in Where clause / Informix

[zombie_man23]

I need a little help with these case statments in my where clause. I don't need to display the results of the case statements, but I do need to put them in my where clause. Any help??

select distinct(r.studentid), r.studentname, r.building,
case when r.currcode like'D%' or r.currcode like 'W%' then rc.dual1 else r.currcode end Currcode,
case when r.currcode like'D%' or r.currcode like 'W%' then rc.dual2 else '' end Dual2
from reg r, bldgtype b, regtb_curr_code rc
where
r.status = 'A' and
b.type = 1 and
r.building = b.bldg and
r.currcode = rc.code and
(
case when r.grade in ('KA','KP','KF') then 'KG' else r.grade end or
case when r.grade in ('PA','PP','PK') then 'PK' else r.grade end
)
(r.building || '*' || trim(r.grade) || '*' || trim(r.currcode)) not in
(select s.bldg || '*' || trim(s.grade) || '*' || trim(s.prog) from seats s where seats >0)

[wildfrog]

case when r.grade in ('KA','KP','KF') then 'KG' else r.grade end
case when r.grade in ('PA','PP','PK') then 'PK' else r.grade end

Does any of these evaluate to someting you need in a where clause?

- petter

[zombie_man23]

Yes. The results of those case statements goes into the next line of the query for the r.grade comparison:
-----------------------here--------------------------------
(r.building || '*' || trim(r.grade) || '*' || trim(r.currcode)) not in
(select s.bldg || '*' || trim(s.grade) || '*' || trim(s.prog) from seats s where seats >0)

[wildfrog]

But neither the "CASE", nor the "NOT IN" expression are valid...

The [NOT] IN should be on the form:

test_expression [ NOT ] IN 
    ( 
        subquery 
        | expression [ ,...n ] 
    )

Maybe you could explain in plain english what you're trying to accomplish?

- petter

[zombie_man23]

When the case statements are taken out the query works fine. What I am trying to do is compare the building, grade, and program between two tables. What the case statements is being used for is, if the student is in KA, KP, or KF the r.grade value should be KG. Or, if the student is in PA, PP, or PK the r.grade value needs to be PK, otherwise, the r.grade value is the original from the database.

[zombie_man23]

I have resolved my problem. Thanks for the help though!!!

Here is what I came up with.

(r.building || '*' || trim(case when r.grade in ('KA','KP','KF') then 'KG' when r.grade in ('PA','PP','PK') then 'PK' else r.grade end) || '*' || trim(r.currcode)) not in
(select s.bldg || '*' || trim(s.grade) || '*' || trim(s.prog) from seats s where seats >0)