I forgot to mention I'm using firebird 1.5.5

1) The integer was a typo.
2) The stored procedure in your reply, it's looping through every record correct?

I guess my question wasn't very clear. What I'm trying to do is have conditional logic in the where clause.

pseudo code:

Select * from table T
Where
Case :input_code
when 'A' then
T.somefield = 'A'
when 'B' then
t.somefield = 'B'
else /* else we want both types */
t.somefield = 'A' or t.somefield = 'B'
end

or

select * from table T
if :input_code = 'A' then
where t.somefield = 'A'
else if :input_code = 'B' then
where t.somefield = 'B'
else
where t.somefield = 'A' or t.somefield = 'B'

This is just a simplified example. There are multiple input parameters and the where clause will get more complex. Do I have to live with having the stored procedure check every record in my example?


Daniel


-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Alan J Davies
Sent: Wednesday, October 31, 2012 10:19 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] sp case statement

No.
You have some errors in the logic and data types.
You can try this. You will get one record returned for every record in your table.

CREATE OR ALTER PROCEDURE GET_DATA (
Input_code char(1) )
RETURNS (
Output_code char(1) ) /* you have an integer which will fail */
as
begin
for select
case
when :input_code='A'
then 'A'
when :input_code='B'
then 'B'
else 'C'
end
from table
into :output_code
do suspend;
end


Alan J Davies
Aldis

On 31/10/2012 21:38, Matchey,Brian wrote:
>
>
> Is there a way to achieve the logic I'm trying to accomplish with the
> CASE statement in the WHERE clause below?
>
> CREATE OR ALTER PROCEDURE GET_DATA (
> Input_code char(1) )
> RETURNS (
> Output_code integer )
> as
> begin
> FOR SELECT T.Output_code
> FROM Table T
> WHERE T.Input_code =
> case :Input_code
when 'A' then 'A'
> when 'B' then 'B'
> else (('A') or ('B'))
> end
> INTO :Output_code
> DO
> SUSPEND;
> end;
>
> Vertafore
> Unleash your potential
>
> Daniel Raith
> Software Engineer II
> 26550 W. Mondovi St, Eleva, WI 54738
> T 800 433 2550 Ext 2917 | F 805 553 9308 | draith@...
> <mailto:draith%40vertafore.com><mailto:draith@...
> <mailto:draith%40vertafore.com>>
> vertafore.com
>
> [Non-text portions of this message have been removed]
>
>


------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links