Re: Has anyone ever done this?

OMG, that works. You're a genius. Ok, ok... I thought I knew a lot about SQL, and then you come along with this thing that completely blows my mind. What are the parenthesis for around the variable name? Is that just a convention? I've not seen that before. I didn't know you could reverse the column and the variable name. Is this kind of thing in any of the books anywhere? I've not come across anything like this. Thank you. You've saved me a lot of headache and coding. Rudy Fernandes <rferdy@americasm01.nt.com> wrote: > This should work : > > create procedure find_states(state_str char(255)) returning char(20);> define description like states.state_desc; > foreach > select state_desc into description > from states > where (state_str) matches '*' || state_code || '*' > return description with resume ; > end foreach ; > end procedure; > > Needless to say, state_str should contain delimiters i.e it should look > like "CA,ON,BC", rather than "CAONBC". > Rudy > > Curtis Bennett wrote: > > > Ok, my requirement is to write a procedure which takes n number of > > states as a parameter. So, my original assertion was that I could just > > use a large variable of states and use it like an "in" clause. > > > > ie (pseudocode) > > create procedure find_states(state_str as char(255)) returningchar(20); > > foreach state_cursor > > select state_desc into description > > from states > > where state_code in (state_str) > > return description with resume ; > > end foreach ; > > end procedure; > > > > ... > > -- Curtis Bennett CIBER, INC Overland Park, KS Sent via Deja.com http://www.deja.com/ Before you buy.

Curtis Bennett wrote: > OMG, that works. You're a genius. Ok, ok... I thought I knew a lot > about SQL, and then you come along with this thing that completely blows > my mind. What are the parenthesis for around the variable name? Not required. Copied over from your example :-). BTW, to clearly differentiate database columns from procedure variables, it would be preferable to add the table name to the database columns. Something like this : select state_desc into description from states where state_str matches '*' || state_desc.state_code || '*' A naming convention that distinguishes the two generally helps, too. (I add "l_" to variables). > Is > that just a convention? I've not seen that before. I didn't know you > could reverse the column and the variable name. Is this kind of thing > in any of the books anywhere? Not explicitly, I guess. But it does not say that you can't, either :-) Rudy