At 11:18 AM 23-01-01 -0200, you wrote:

> > A parameter represents a VALUE, not a column
> >
> > Replace with
> >
> > AND ((TipPFJ = :TipPFJ ) or **** (TipPFJ = 'ForCli' ****
> >
> > >and TipPFJ in ('Fornec', 'Client', 'ForCli')))
> > >ORDER BY CodPFJ
>
> > Even so, you should remove it because the next subclause is doing the same
> > test, anyway, i.e. and TipPFJ in ('Fornec', 'Client', 'ForCli') is the
>same as
> > and ((TipPFJ='Fornec') or (TipPFJ='Client') or (TipPFJ='ForCli'))
>
>I can´t make the changes you´ve suggested because the ** TipPFJ2 = 'ForCli'
>** isn´t the same as ** :TipPFJ2 = 'ForCli' **, what I really need is:
>If the parameter value is equal 'ForCli' I want that all records equal at
>'Fornec', 'Client', 'ForCli' be returned, but if the parameter value is
>different I want just the records who are equal to the parameter value to be
>returned.
>
>Why when I use the statement below it works fine?
>
>SELECT * FROM CadPFJ
>WHERE (CodEmp = 1 AND CodPFJ BETWEEN :CodPFJ and :CodPFJ2 )
>AND ((TipPFJ = :TipPFJ ) or (*** :TipPFJ2 *** = 'ForCli' and TipPFJ in
>('Fornec', 'Client', 'ForCli')))
>ORDER BY CodPFJ
>
>Any suggestion?

This statement is different - you now have two parameters. However, it
will not return the conditional result set you describe here:

You said:

> what I really need is:
> If the parameter value is equal 'ForCli' I want that all records equal at
> 'Fornec', 'Client', 'ForCli' be returned, but if the parameter value is
> different I want just the records who are equal to the parameter value to be
> returned.

You need an SQL CASE statement but, unfortunately, InterBase doesn't yet
support it. In order to get a conditional set, you will need a selectable
stored procedure.

create procedure Get_CadPFJ (CodPFJ1 integer, CodPFJ2 integer, in_TipPFJ
varchar(20))
as
begin
IF (in_TipPFJ = 'ForCli') then
FOR
select field1, field2, ....TipPFJ, CodPFJ
from CadPFJ
where ((CodEmp = 1 AND CodPFJ BETWEEN :CodPFJ1 and :CodPFJ2)
and (TipPFJ in ('Fornec', 'Client', 'ForCli')))
order by CodPFJ
into :field1, :field2, ... :TipPFJ, :CodPFJ
DO
suspend;
ELSE
FOR
select field1, field2, ....TipPFJ, CodPFJ
from CadPFJ
where ((CodEmp = 1 AND CodPFJ BETWEEN :CodPFJ1 and :CodPFJ2)
and (TipPFJ = :TipPFJ))
order by CodPFJ
into :field1, :field2, ... :TipPFJ, :CodPFJ
DO
suspend;
end;

Helen





All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________