Buildind a IN clause in a Formula Column

[petakoti]

Hello,

I'm trying to build a SQL in a Formula Column, the query is this :


select count(*)
into resul1
from dcarpro dc
WHERE dc.codagruest in P_AGRUEST);


_AGRUEST is a character parameter.

Here in _AGRUEST I get a string like this ('01','02','03')

This query return zero and it should return a value greater than this.

I've tried passing '01','02','03' instead of ('01','02','03') and I get the same result.

I've also tried changing the sentence above with this other :

select count(*)
into resul1
from dcarpro dc
WHERE dc.codagruest in ('01','02','03');

and it works.

How can I build the sentence correctly???

 

[sem]

You should use LEXICAL parameter rather than BIND variable:

select count(*)
into resul1
from dcarpro dc
WHERE dc.codagruest in (&P_AGRUEST);

You should also provide DEFAULT value for P_AGRUEST to validate your query during design time.


Regards, Dima