Decodes and paramterised queries

[cidney]

I have a table transactions, which has a column called Type. Type can be one of four values DEBT,INV,CRED or SUM.

I am writing a query to select only certain types dependant on a parameter passed to my query. For example if i pass DEBT i want to select only those transactions with a type of DEBT.

the problem i have is that i don't know how to do it if i only want to select 2 types (e.g CRED & DEBT ) of transaction. I can do it if i want all transactions or a single type using the following code:

type = DECODEp_type,'ALL',type, _type)

Any suggestions?

 

[karluk]

Are you aware that you can assign default values to the input parameters of a stored procedure? If you do so, the parameters are optional when the procedure is called.

It seems to me that you could create a procedure with four optional transaction_type parameters. You would assign them default values that don't exist in your table. You would call the procedure supplying only those transaction types you want to select on. Your cursor would be written to select on all four input parameters but, because the default values don't exist, you would find only the ones supplied during the procedure call.

Naturally you would also have to insert logic in your procedure if you want to preserve the "ALL" option, which is an artificial code that selects on all four valid transaction types.

 

[cidney]

Cheers that worked a treat. thanks for your help.