Your answer knocked loose what rocks were stuck in my head.

I simply changed the WHEN <> THEN SELECT <> to:
SELECT DISTINCT MY_KEY FROM MY_TABLE WHERE MY_KEY = KEY_IN_MY_TABLE AND OTHER_CONSTRAINTS
and it was like automagic...

I also appreciated the CAST(MULTISET(...)) example. I will have to play with that a bit more.

Regards

I had played around with using the CASE statement in the where clause to sql more dynamic but had also run into the same problem with needing multiple values returned for the in. Tried a whole host of methods using STRAGG and in-list functions but kept running into limitations Thanks for showing how I can do dynamic where clauses without using pl/sql.....

On a related note, if the requestor had been passing on value in or returning one value from the case statement couldn't he have used the case statement to either use the variable or use the column name, sort of like using the NVL OR EXPANSION where username = (nvl(:x,username)) but more readable.

variable x varchar2(30);

exec :x := 'SYSTEM';

select /*+
        gather_plan_statistics
    */
*
      from all_users
     where username = (case
                        when :x = 'default'
                        then username
                        else :x
                       end);

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM                                  5 16-NOV-06

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID  1v6vcy1r5vgx2, child number 0
-------------------------------------
select /*+         gather_plan_statistics     */ *       from all_users
where username = (case                         when :x = 'default'
         then username                         else :x                        end)

Plan hash value: 3780144807

------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS          |       |      1 |      1 |      1 |00:00:00.01 |      16 |
|   2 |   NESTED LOOPS         |       |      1 |      1 |      1 |00:00:00.01 |      13 |
|*  3 |    TABLE ACCESS FULL   | USER$ |      1 |      1 |      1 |00:00:00.01 |      10 |
|   4 |    TABLE ACCESS CLUSTER| TS$   |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  5 |     INDEX UNIQUE SCAN  | I_TS# |      1 |      1 |      1 |00:00:00.01 |       1 |
|   6 |   TABLE ACCESS CLUSTER | TS$   |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  7 |    INDEX UNIQUE SCAN   | I_TS# |      1 |      1 |      1 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(("U"."TYPE#"=1 AND "U"."NAME"=CASE :X WHEN 'default' THEN
              "U"."NAME" ELSE :X END ))
   5 - access("U"."DATATS#"="DTS"."TS#")
   7 - access("U"."TEMPTS#"="TTS"."TS#")


28 rows selected.

exec :x := 'default';

PL/SQL procedure successfully completed.

select /*+
        gather_plan_statistics
    */
*
      from all_users
     where username = (case
                        when :x = 'default'
                        then username
                        else :x
                       end);

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYS                                     0 16-NOV-06
SYSTEM                                  5 16-NOV-06
OUTLN                                  11 16-NOV-06
DIP                                    19 16-NOV-06
......etc

70 rows selected.

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); 

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  1v6vcy1r5vgx2, child number 0
-------------------------------------
select /*+         gather_plan_statistics     */ *       from all_users
where username = (case                         when :x = 'default'
         then username                         else :x                        end)

Plan hash value: 3780144807

------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS          |       |      1 |      1 |     70 |00:00:00.01 |     241 |
|   2 |   NESTED LOOPS         |       |      1 |      1 |     70 |00:00:00.01 |     160 |
|*  3 |    TABLE ACCESS FULL   | USER$ |      1 |      1 |     70 |00:00:00.01 |      14 |
|   4 |    TABLE ACCESS CLUSTER| TS$   |     70 |      1 |     70 |00:00:00.01 |     146 |
|*  5 |     INDEX UNIQUE SCAN  | I_TS# |     70 |      1 |     70 |00:00:00.01 |       6 |
|   6 |   TABLE ACCESS CLUSTER | TS$   |     70 |      1 |     70 |00:00:00.01 |      81 |
|*  7 |    INDEX UNIQUE SCAN   | I_TS# |     70 |      1 |     70 |00:00:00.01 |       6 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(("U"."TYPE#"=1 AND "U"."NAME"=CASE :X WHEN 'default' THEN
              "U"."NAME" ELSE :X END ))
   5 - access("U"."DATATS#"="DTS"."TS#")
   7 - access("U"."TEMPTS#"="TTS"."TS#")


28 rows selected.

SQL> 

I'm going to play around with your method and see how far I can stretch it, so far it looks like it performs well, but I have one report where they have 15 different predicates that they may or may not use depending on the users response.

I may have to try harder to get them to let me send the sql statment to a procedure to get some speed out of it but maybe your solution will work.

Thanks again for the great work

Tom,

Can you please explain about sys.odcinumberlist

Thanks,
Ravi

Hello Tom,

I'm trying to do exactly what is demonstrated to work in 10g in your answer to the original question but in 8i(8.1.7.4.0).

I keep getting the error "ORA-22905: cannot access rows from a non-nested table item".

I created the following type in a package:

TYPE LocStatusList IS TABLE OF VARCHAR2(2);

And then try to use it as in you example:

<...query>
WHERE
<my field> IN(
SELECT
COLUMN_VALUE
FROM
TABLE(
CASE WHEN '&parameter' = 'EA' THEN <package name>.LocStatusList('EA', 'PA')
<other whens...>
END
)
)

As far as you know, is it supposed to work the same in 8i? If not, is there a workaround?

If you need more specific information to help me, I'll of course be glad to provide you with it. I just thought i'd ask this follow-up question in a general manner since there is unfortunately quite a lot of questions I had in the past (although not here) about 8i to which the answer was "Does not work in 8i."...

Thank you very much.

Alex

ops$tkyte@ORA817DEV> create or replace type myTabType as table of varchar2(30)
  2  /

Type created.

ops$tkyte@ORA817DEV> declare
  2          l_data myTabType := myTabType( user, 'SYS' );
  3  begin
  4          for x in (select * from all_users where username in
  5          (select * from TABLE( cast( l_data as myTabType ) )) )
  6          loop
  7                  dbms_output.put_line( x.username || ', ' || x.user_id );
  8          end loop;
  9  end;
 10  /
OPS$TKYTE, 772
SYS, 0

PL/SQL procedure successfully completed.

Hello Tom,

I'd like to update my previous follow-up question above.

I tried to cast the table as its own type as I read it was necessary for 8i.

However, I still get the ORA-22905 error!?


Here is how it looks now:

WHERE
<my field> IN(
SELECT
COLUMN_VALUE
FROM
TABLE(
CASE WHEN '&PARAMETER' = 'EA' THEN
CAST(<package name>.LocStatusList('EA', 'PA') AS <package name>.LocStatusList)
<other whens>
END
)
)

Thank you,

Alex

ops$tkyte@ORA817DEV> declare
  2          l_data myTabType := myTabType( user, 'SYS' );
  3  begin
  4          for x in (select * from all_users where username in
  5          (select * from TABLE( myTabType(user,'SYS') )) )
  6          loop
  7                  dbms_output.put_line( x.username || ', ' || x.user_id );
  8          end loop;
  9  end;
 10  /
OPS$TKYTE, 772
SYS, 0

PL/SQL procedure successfully completed.

Hello again Tom,

Another update for my follow-up question...

I read that I couldn't use a type declared in a package from SQL so I dropped it from the package and did a regular create type.

create or replace type LocStatusList is table of char(2);

Still got ORA-22905 when executing my query against it...

then I declared it in the following way, as seen in SQL reference examples for cast():

create or replace type LocStatus is object(status char(2));
create or replace TYPE LocStatusList IS TABLE OF LocStatus;

Still got ORA-22905 when executing my query against it...


Hope the supplementary information helps when you have some time to look into this...

Thanks,

Alex

Hello Tom,

This is my final follow-up "message" regarding my previous follow-up question entitled "Can I achieve the same in 8i?", for which I also posted a few updates during the day following my original posting, as I found in other threads here some element to solve my problem. I guess I wanted to show I was somewhat progressing...

Here is the updated version of a non-working example I should have provided initially (yes it is only sql, not pl/sql, it wasn't obvious since I was referring to using a type in a package while using sqlplus '&' type parameters...mea culpa):

create or replace type myTabType as table of varchar2(30);


SELECT
*
FROM
ALL_USERS
WHERE
USERNAME IN(
SELECT
COLUMN_VALUE
FROM
TABLE(
CASE
WHEN '&MYPARAM' = '1'
THEN CAST(MyTabType('SYS', USER) AS MyTabType)
ELSE CAST(MyTabType('SYSTEM', USER) AS MyTabType)
END
)
);

And here is the same example, modified to demonstrate the way it finally worked for me in 8i:

SELECT
*
FROM
ALL_USERS
WHERE
USERNAME IN(
SELECT
*
FROM
TABLE(CAST(
CASE
WHEN '&MYPARAM' = '1'
THEN MyTabType('SYS', USER)
ELSE MyTabType('SYSTEM', USER)
END
AS MyTabType))
)

As you can see, all I needed to do (after knowing I had to use a sql type and to cast the collection, of course...) was casting the whole case statement instead of casting each of its potential outputs separately.

Thank you for your time, Tom!