Function to return a list for an IN() 2

[roberthagan]

I'm trying to break up a client list by zipcode in several stored procedures where I have to provide a list of zips. I tried writing a function that would supply the list.

the query (simplified):
SELECT *
FROM CLIENT
WHERE ZIP IN (Manh_Split_Where_FuncSITE))

The function:
CREATE OR REPLACE FUNCTION Manh_Split_Where_Func (SITE IN CLIENT.SITE_CD%TYPE)
RETURN VARCHAR2

IS
searchwhere VARCHAR2(100);
BEGIN

IF SITE = 'MANHS' THEN
SEARCHWHERE := ' ' ||CHR(39)|| 10034 ||CHR(39)|| ',' ||CHR(39)|| 10040 ||CHR(39)|| ',' ||CHR(39)|| 10045 ||CHR(39)|| '' ;
ELSIF SITE = 'MANHN' THEN
SEARCHWHERE := ' ' ||CHR(39)|| 10025 ||CHR(39)|| 10025 ||CHR(39)|| ' ' ;
END IF;

RETURN SEARCHWHERE;
END Manh_Split_Where_Func;
/

(I found the idea of using CHR(39) here. Thanks.)
The function runs and returns the list of zips, but when I call it from the query, it runs and the query returns nothing. Any idea of what might be going on?

Thanks
Bob Hagan

 

[Dagon]

Unfortunately, that method won't work. The IN will treat the output of the function as a single value rather than a proper list. To do an IN list dynamically, you'd have to use dynamic SQL (for which you'd probably need the dbms_sql package in 8i). If you're using SQL*Plus, you might be able to do it through substitution variables.

col inlist new_value inlist
select '''a'',''b'',''c'',''d''' as inlist from dual;
select * from dual where 'a' in (&inlist);

 

[Thargy]

The illustrious Mr Kyte has what I believe to be relevant chapter and verse on this, to wit:-

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061

Regards

T

Grinding away at things Oracular

 

[SantaMufasa]

Robert,

Each of the above respresent useful solutions to your need. I infer, however, from your examples, that your actual need is probably larger than the "simplified" example you posted. (I envision that you probably have many sites, each with many associated ZIP Codes.)

If my inference is correct, then you probably do not want to hard code ZIP Codes/Site values into your application; you are better off placing such values in a standard Oracle table. Once you choose to do so, then your query becomes simple to the extent that you do not even need the Manh_Split_Where_Func procedure/code:

select * from zipgroup;

SITE              ZIP
---------- ----------
MANHS           10034
MANHS           10040
MANHS           10045
MANHN           10025

select * from client;

        ID        ZIP
---------- ----------
         1      84092
         2      10034
         3      10040
         4      94025
         5      10025
         6      10045

select * from client
 where zip in (select zip from zipgroup where site = 'MANHN');

        ID        ZIP
---------- ----------
         5      10025

select * from client
 where zip in (select zip from zipgroup where site = 'MANHS');

        ID        ZIP
---------- ----------
         2      10034
         3      10040
         6      10045

Let us know if this is useful.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[roberthagan]

Ultimately, Santa you are correct. We are trying to design a new application where sites and the zips they cover would be defined in a table.

For the moment, we are trying to fend off feature creep in our old application by making only the changes we have to. Our Manhattan office just split so for the moment I have only MANH North and South to deal with. I was hoping I could plug some reusable code into a bunch of queries. Surprise.

I'll take a look at the other ideas, and am also experimenting with adding a staffing table which specifies worker sites into the queries.

Thanks all