I don't think you can do what you want to do. Write a stored procedure, or
do your check in your program. The WHERE clause is for checking the value
of some fields, not checking the value of your parameters.

I guess you could try
SELECT * FROM CadPFJ
WHERE (CodEmp = 1 AND CodPFJ BETWEEN :CodPFJ and :CodPFJ2 )
AND ((TipPFJ = :TipPFJ ) or ('ForCli' = :TipPFJ and TipPFJ in
('Fornec', 'Client', 'ForCli')))
ORDER BY CodPFJ
(i.e. use the :TipPFJ after the = sign), but I would be somewhat surprised
if that worked.

Please don't call this a bug, you're trying to do something that is not the
intended way to write your selects,
Set

At 11:18 23.01.2001 -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?
>
>PHA
>
>
>--- Original Message -----
>From: Helen Borrie <helebor@...>
>To: <IBObjects@egroups.com>
>Sent: Tuesday, January 23, 2001 10:11 AM
>Subject: Re: [IBO] Urgent - Bug in IBO Parameters ?
>
>
>> At 09:58 AM 23-01-01 -0200, you wrote:
>> >The Select:
>> >
>> >SELECT * FROM CadPFJ
>> >WHERE (CodEmp = 1 AND CodPFJ BETWEEN :CodPFJ and :CodPFJ2 )
>>
>> This isn't a bug in IBO parameters, it is a bug in your SQL statement.
>> Your problem is in this section:
>>
>> >AND ((TipPFJ = :TipPFJ ) or **** (:TipPFJ = 'ForCli' **** /* THE SQL
>ISN'T
>> >VALID */
>>
>> 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 think you are making the mistake of treating a parameter as a variable
>> and expecting IB to handle it as an expression somehow.
>>
>> Helen
>>
>>
>> >// Monitor //
>> >
>> >SELECT * FROM CadPFJ
>> >WHERE (CodEmp = 1 AND CodPFJ BETWEEN ? /* CodPFJ */ and ? /* CodPFJ2 */ )
>> >AND ((TipPFJ = ? /* TipPFJ */ ) or (? /* TipPFJ */ = 'ForCli' and TipPFJ
>> >in ('Fornec', 'Client', 'ForCli')))
>> >ORDER BY CodPFJ ASC
>> >PLAN (CADPFJ ORDER RDB$PRIMARY3)
>> >FIELDS = [ Version 1 SQLd 21 SQLn 1
>> >CADPFJ.CODEMP = <NIL> ]
>> >----*/
>> >
>> >/*---
>> >[ 23/01/2001 09:44:08 ]
>> >EXECUTE STATEMENT
>> >TR_HANDLE = 17178052
>> >STMT_HANDLE = 17195308
>> >PARAMS = [ Version 1 SQLd 4 SQLn 4
>> >[CODPFJ] = 0
>> >[CODPFJ2] = 2147483647
>> >[TIPPFJ] = 'ForCli'
>> >[TIPPFJ] = <n> '' ] // ==> The problem two parameters with the
>> >same name
>> >----*/
>> >
>> >The IBO is creating two parameters with the same name.
>> >PHA
>> >
>> >I´m using:
>> >
>> >Interbase 6.01 Super Server
>> >Servidor Linux Conectiva 5.2
>> >
>> >Windows 98 SE
>> >Delphi 5.0
>> >IBO 3.6Cd
>> >
>> >PHA
>> >
>> >
>> >
>> >
>> >[Non-text portions of this message have been removed]
>> >
>> >
>>
>> All for Open and Open for All
>> InterBase Developer Initiative · http://www.interbase2000.org
>> _______________________________________________________
>>
>>
>>
>>
>
>
>
>
>
>