select statement using 'IN' and NVL 4

[JKrumins]

Hi
I want to write a query where the user can input either:
(a) a single customer_id, to return all records for that customer
or
(b) a list of several customer_ids, perhaps seperated by commas, to return all records for those customers
or
(c) Null, to return records for ALL customer_id's.

I've tried something like:
... where cust_id in NVL('&customer_id','%')
[that only works for (a) - nothing returned for (b) & (c)]

or maybe I should use LIKE instead of IN ?

Can anyone point me to a workable solution?

 

[aryeh1010]

I assume that cust_id is a numerical field.

I have a solution for you, that requires two inputs

where cust_id in (&num) or 1=&bool

for &num you can enter 1 values or a comma-separated list of values.

for a & b - &bool gets 0
for c - bool gets 1

In terms of performance, this is not ideal. If your tables are very large, I would consider splitting this into two different queries.

 

[JKrumins]

Thanks for the prompt answer.

I'm afraid that cust_id is not a numeric field. I think the fact that the IN statement usually takes the form
... WHERE CUST_ID IN ('A123','A125','B444')

with varchar values enclosed in quotes is part of the problem.

Maybe I will have to have two queries as you suggest.

 

[sem]

Something like:

column a old_val cnd

select decode('&customer_id','','','where cust_id IN ('''||replace('&customer_id',','||''',''')||''')') a from dual;

select * from customers &&cnd

The first statement allows to "calculate" new lexical variable cnd value. The second calculates this value (leaves it blank or creates correct IN condition). The third uses it as the whole where clause.





Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Dima,

Your cleverness never ceases to amaze me ! Your solution is simply elegant because it is elegantly simple. Purple star for you, Dude!

(If you are ever in the U.S. or I am in the Ukraine, we must visit. I went out to your

http://seminihin.narod.ru

website to see your lovely wife and adorable baby. My Russian is not as good as it should be, but how close are my English translations? (Website) "My Family in Pictures", Photo 1: "With parents", Photo 1: "Mom's holding", Photo 3: "Sad", Photo 4: "Let's Fly", Photo 5: "Prince", Photo 6: "With Papa", Photo 7: "Cool !", Photo 8: "Tasty", Photo 9: "In the arms of the Godmother", Photo 10: "In the arms of the Godfather", Photo 11: "In the arms of the Priest&quot

Congratulations on such a lovely family,

Mufasa
(aka Dave of Sandy, Utah, USA @ 15:12 (04Dec03) GMT, 08:12 (04Dec03) Mountain Time)

 

[avjoshi]

That's classic Dima!!

Anand.

 

[sem]

Thank you, Dave

I often wish to send a message of admire on your own answers, but each time feel that my English is to poor to express non-technical thoughts adequately. Although I hope you sence them on non-verbal level
Your translations are completely correct, at least within my knowledge of English.

As this is complete off-topic my ICQ is 133765237, please don't red-flag this immediately
..but now shall go to my lovely family

Regards, Dima

http://seminihin.narod.ru

 

[JKrumins]

Trouble is, I'm working within the confines of an ERP package where simple reports are single-command select statements.

It can do
[tt]select .... where cust_id in (select...)[/tt] etc

but it definitely can't do 3 statements or declaration of variables.

Any other ideas?

 

[SantaMufasa]

Dima,

Our firewall prevents ICQ connections, but I am "SantaMufasa" on Yahoo!Messenger. Does that work for you? If you do not yet have Yahoo!Messenger, it only takes a few minutes to download from Yahoo.com.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:21 (04Dec03) GMT, 10:21 (04Dec03) Mountain Time)

 

[sem]

JKrumins, can you pass parameters to that reports?

Regards, Dima

http://seminihin.narod.ru

 

[JKrumins]

Hi Sem

If I understand what you mean correctly, I guess the answer is no.
From within the application, a report is just a single sql select statement eg

[tt][blue]
select * from cust_tab where cust_id like NVL('&Enter_customer_no','%') and part_no in (select part_no from part_tab where part_no like '&Enter_part_no%')[/blue][/tt]

Can use any functions that apply within a Selct statement, but nothing more. The ERP application then process that into a windows framework within the user's environment.

 

[gunjan14]

Hi,
If in a single SQL statement, we can split the comma separated values then this can be written. For example if we have value like this 1,2,3 and we can have it like this
1
2
3
then the query will be
select * from cust_tab
where (cust_id IN decode('&Enter_customer_no', '', cust_id,
sign(instr('&Enter_customer_no',',')), 0, '&Enter_customer_no')
OR cust_id IN (substr('&Enter_customer_no',1,instr('&Enter_customer_no',',')-1),
substr('&Enter_customer_no',instr('&Enter_customer_no',',')+1,
length('&Enter_customer_no')
)
)
)

see the bold statement. I have written substr if it is for 2 customer id (say cust01, cust02). This is giving correct result in all the three cases.
Hope this helps

Gunjan

 

[sem]

One more idea, quite ugly but not uglier than ERP alowing only plain selects

You should create a type for storing a list of numbers and procedure parsing comma-delimited string. This is just a skeleton, you must refine it for production:

create or replace type emplist is table of integer
/
create or replace function parseList(p in varchar2)
return emplist
is
retval emplist := emplist();
nextPos integer := 0;
lastPos integer := 1;
ind binary_integer := 0;
begin
if p is null then
return null;
end if;

loop
nextPos := instr(p, ',' , lastPos);
exit when nextPos = 0;
ind := ind +1;
retval.extend;
retval(ind) := substr(p, lastPos, nextPos-lastPos);
lastPos := nextPos + 1;
end loop;
ind := ind +1;
retval.extend;
retval(ind) := substr(p, lastPos);
return retval;
end;
/

Then your query may be:

select * from cust_tab
where '&&customer_id' is null
or cust_id in
(select * from table( cast ( parseList('&&customer_id') as empList)))

Regards, Dima

http://seminihin.narod.ru

 

[gunjan14]

Hi,
one more feasible (crazy) thing. Here you will get all the three condition.
But first create a table with 1 column and have the datatype as integer. insert number between 1 ... 3000 or 4000 (depending upon the condition how many comma separated cust_id you are going to give multiplied by length of cust_id)

create table temp_gunj (col_num number);
insert into temp_gunj values (1);
insert into temp_gunj values (2);
..
..
insert into temp_gunj values (5000);

Another thing, when you are giving the comma separated values then start with a comma and end with a comma. Suppose you want to give cust01,cust02 then give
,cust01,cust02,

Now this is the query
select * from cust_tab
where (cust_id IN decode('&Enter_customer_no', '', cust_id,
sign(instr('&Enter_customer_no',',')), 0, '&Enter_customer_no')
or cust_id IN (select substr('&Enter_customer_no', n.col_num + 1, p.col_num - n.col_num)
from temp_gunj n, temp_gunj p
where n.col_num >= 1
and n.col_num < length('&Enter_customer_no')
and substr('&Enter_customer_no', n.col_num, 1) = ','
and p.col_num = (select min(a.col_num)-1
from temp_gunj a
where a.col_num BETWEEN 1 and length('&Enter_customer_no')
and substr('&Enter_customer_no', a.col_num, 1) = ','
and a.col_num > n.col_num
)
)
)


Hope this helps...

Gunjan

 

[JKrumins]

[SURPRISE] Wow! You've given me loads to work with, Gunjan and Dima. Thanks. I will post any comments when I have had a chance to try out and evaluate these suggestions.

Yesterday I played around with Gunjan's first coding. That worked very well for a list of two items separated by a
comma (id1,id2), but is limited to 2 items. I would certainly use that at some time, but in this particular situation I'm expecting a list of perhaps 4 or 5 items, so I will try the others.

 

[gunjan14]

Hi Krumins,

In that case, if u can create a table in the database then follow the second query and then you can give as many items as possible. But do remember to include a comma before and after the items.
Tell us the result.


Gunjan

 

[sem]

JKrumins, have you solved your problem?

Regards, Dima

http://seminihin.narod.ru

 

[gunjan14]

Hi,
A couple of points more. The above query is failing if I give the command like this
,cust01, cust02, cust03
So, just add ltrim, and rtrim. Here is the new query and this does not even require to add comma(,) before and after the comma separated value. Foe example just give cust01,cust02,cust03 and it will work

(Create your own table and give its name here in the query instead of temp_gunj(col_num))

select * from cust_tab
where (cust_id IN decode('&Enter_customer_no', '', cust_id,
sign(instr('&Enter_customer_no',',')), 0, '&Enter_customer_no')
or cust_id IN (select ltrim(rtrim(substr(',&Enter_customer_no,', n.col_num + 1, p.col_num - n.col_num)))
from temp_gunj n, temp_gunj p
where n.col_num >= 1
and n.col_num < length(',&Enter_customer_no,')
and substr(',&Enter_customer_no,', n.col_num, 1) = ','
and p.col_num = (select min(a.col_num)-1
from temp_gunj a
where a.col_num BETWEEN 1 and length(',&Enter_customer_no,')
and substr(',&Enter_customer_no,', a.col_num, 1) = ','
and a.col_num > n.col_num
)
)
)

 

[sysrsh]

Hi Dima,

I am facing the same problem in one of my plsql procedures. I created the parseString function. How should I pass the parameters to the stored proc as ? For instance I want to search on user_id IN('abc','def'). How should I be passing this to the stored proc ? Thanks.

sysrsh

 

[gunjan14]

Hi,

Just pass it as single string. For example if u have to search on user_id IN ('abc','def') then simple pass as
'abc,def'.

Gunjan