SQL & PL/SQL

Environment:

Oracle EE 10.2.0.2 on AIX 5.3

I am trying to use a CASE statement in a SQL predicate to fill in the values needed in an 'IN' clause.

Here is my test case:

create table tt (model varchar2(10), brand varchar2(10));

insert into tt (model, brand) values ('modela','ajax');
insert into tt (model, brand) values ('modelb','ajax');
insert into tt (model, brand) values ('modelc','acme');
insert into tt (model, brand) values ('modeld','acme');
insert into tt (model, brand) values ('modele','acme');
insert into tt (model, brand) values ('modelf','zeus');
insert into tt (model, brand) values ('modelg','zeus');
insert into tt (model, brand) values ('modelh','mojo');
insert into tt (model, brand) values ('modeli','mojo');
insert into tt (model, brand) values ('modelj','mama');


Here is what I'm attempting to do. For a given starting letter for a brand I would like all the models printed out whose brand starts with the entered letter.

Here is what I started with:

select model, (case '&brandfirstletter'
when 'a' then '(''ajax'',''acme'')'
when 'z' then '(''zeus'')'
when 'm' then '(''mojo'')'
end) pred
from tt
where brand in
(case '&brandfirstletter'
when 'a' then '(''ajax'',''acme'')'
when 'z' then '(''zeus'')'
when 'm' then '(''mojo'')'
end);


When executed I get 'no rows selected'

I wanted to print out the results of my CASE statement just to be sure it was doing what I thought I wanted.

When I comment out the WHERE clause to see what the PRED values are I get:

gww@dwt1> /
Enter value for brandfirstletter: a
old 1: select model, (case '&brandfirstletter'
new 1: select model, (case 'a'
Enter value for brandfirstletter: a
old 8: -- (case '&brandfirstletter'
new 8: -- (case 'a'


MODEL PRED
---------- ---------------
modela ('ajax','acme')
modelb ('ajax','acme')
modelc ('ajax','acme')
modeld ('ajax','acme')
modele ('ajax','acme')
modelf ('ajax','acme')
modelg ('ajax','acme')
modelh ('ajax','acme')
modeli ('ajax','acme')
modelj ('ajax','acme')



I believe this is what I want the results of the CASE statement to look like when I select 'a' for the substitute variable but I don't get the output I would like from the table.

What am I missing?

Thanks very much!!

-gary