RE: "IN" clause via a single parameter in a SP
It's definitely treating your variable as one long condition then (not
three distinct ones)
I assume in the last example, it's looking for a state with quote marks
around it. The trick is going to be to manipulate the engine to divide
the variable up into it's separate parts.
If you can't get the engine to do it for you, you could always do it
manually.
Something like
define i smallint;
define j smallint;
define param array[3] of string; --Not sure about this syntax
let j = 0;
for i = 1 to length(string) --pseudo code
if string[i] = "|"
let j = j + 1;
else
let param[j] = param[j] || string[i];
end if;
end for;
{You get the idea}
Then call it as:
execute procedure test_in("AR|AZ|AL")
In the end, this might be more trouble than three distinct parameters.
But if you have to have a variable number of states, this could work.
You'd just have to define the array as 50 to make sure.
HTH
Let me know how it works out.
-----Original Message-----
From: Curtis Bennett [mailto:die_kluge@hotmail.com]
Sent: Monday, March 27, 2000 5:15 PM
To: sblack@elsouth.com
Cc: informix-list@iiug.org
Subject: RE: "IN" clause via a single parameter in a SP
If I call
execute procedure test_in("'AR', 'AZ', 'AL'") ;
I get 'no rows found'
But if I call a separate procedure that takes 3 arguments of char(2)
execute procedure test_in('AR', 'AZ', 'AL') ;I get 3 states.
Subsequently, if I call the first example and just pass it one state
execute procedure test_in("AR") ;I get a row back.
If I run
execute procedure test_in("'AR'") ; I get nothing.
Weird.
From: Scott Black <sblack@elsouth.com>
Subject: RE: "IN" clause via a single parameter in a SP
Date: Mon, 27 Mar 2000 13:40:13 -0500
I'm guessing it has something to do with quotes. Since it works for:
where state_code in (state_1, state_2, state_3....)
but not for:
where state_code in (state_str)
The engine is probably treating state_str as one whole value. Have you
tried playing around with the quotes i.e.
execute procedure test_in("'state1', 'state2', 'state3'");?
I think the trick will be to make the engine see state_str as separate
values, instead of one. And I'm not sure there's a way to do it... but
I'd like to hear about if you figure it out.
HTH
-----Original Message-----
From: Curtis Bennett [mailto:die_kluge@hotmail.com]
Sent: Monday, March 27, 2000 1:11 PM
Posted To: informix
Conversation: "IN" clause via a single parameter in a SP
Subject: "IN" clause via a single parameter in a SP
I'm tring to write a stored procedure that takes a parameter of a
string, say size char(200) or so. And with pseudo-code, this is what
I'm trying to do :
create procedure test_in (state_str char(200))
returning char(50)
select state_nm
into st_nm
from states
where state_code in (state_str)
return st_nm
with resume ;
end procedure ;
It doesn't work. I mean, I can run it, but I get no rows returned. If
I pass it two or more parameters, and then do
...
where state_code in (state_1, state_2, state_3....)
it works. But not the above example. I'd really rather not have to
take 50 parameters into this procedure to get it to do this. Am I
overlooking something here?
Thanks,
--
Curtis Bennett
CIBER, INC
Overland Park, KS
Sent via Deja.com http://www.deja.com/
Before you buy.
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com