I don't think I've done this before

have this as part of the select statement

_fkBanks = :BankID OR COALESCE(_fkBanks,-1) = -1,


and I'm getting

ElevateDB Error #700 An error was found in the statement at line 2 and column 12 (Invalid expression ? found, dynamic parameter references not allowed)


I know you can't put field names as parameters but I would have thought this was OK.

Roy Lambert

On 6/16/2019 11:31 AM, Roy Lambert wrote:
> I don't think I've done this before
>
> have this as part of the select statement
>
> _fkBanks = :BankID OR COALESCE(_fkBanks,-1) = -1,
>
>
> and I'm getting
>
> ElevateDB Error #700 An error was found in the statement at line 2 and column 12 (Invalid expression ? found, dynamic parameter references not allowed)
>
>
> I know you can't put field names as parameters but I would have thought this was OK.

I just tried it with a test table and works OK for me in EDB Manager - i
can preaprem enter param value and execute and get results and no error.

edb manager 2.31 b5

Maybe double-check entire select and see if anything else might trigger
the error

Raul

Raul


Just tried

select
(_fkBanks = :BankID) OR (COALESCE(_fkBanks,-1) = -1)
from
transactions

and get

ElevateDB Error #700 An error was found in the statement at line 2 and column 13 (Invalid expression ? found, dynamic parameter references not allowed)

when I try and prepare it

Also running 2.31 b5 and trying in EDBManager

Roy Lambert

On 6/17/2019 2:49 AM, Roy Lambert wrote:
> select
> (_fkBanks = :BankID) OR (COALESCE(_fkBanks,-1) = -1)
> from
> transactions

That's bit different - i was running a normal select which works ok
which is why i asked for SQL.

So it works fine for where clause

select * from transactions  where (_fkBanks= :BankID) OR
(COALESCE(_fkBanks,-1) = -1)

You are trying to do a parameterized select field condition which
returns boolean value. it kind of feels like this would also fall into
the "field names in select" limitation.

Tim would need to comment on this for appropriate answer.

However would not an equivalent query be

select true from transactions where (_fkBanks= :BankID) OR
(COALESCE(_fkBanks,-1) = -1)


Raul

On 6/17/2019 9:11 AM, Raul wrote:
> However would not an equivalent query be
>
> select true from transactions where (_fkBanks= :BankID) OR
> (COALESCE(_fkBanks,-1) = -1)

Actually should have said

select True from transactions where _fkBanks is null
union
select True from transactions where _fkBanks= :BankID

Raul

Raul

It was when I was trying to find out why the WHERE clause wasn't returning what I expected that I tried to put the equivalent into the SELECT clause. Sorry for putting statement which looking back at it is confusing.


>You are trying to do a parameterized select field condition which
>returns boolean value. it kind of feels like this would also fall into
>the "field names in select" limitation.

I think I agree apart from the to me it falls into a constant to be tested against rather than a field to be tested against area.

Bloody weird this SQL stuff

Roy

On 6/17/2019 10:50 AM, Roy Lambert wrote:
>
> I think I agree apart from the to me it falls into a constant to be tested against rather than a field to be tested against area.

Technically all parameters are constants (during execute stage)

During prepare that is not the case where i think this falls down - EDB
has to treat this as dynamic result.

This might be a type of special case Tim maybe can see about adding
support for.

Raul