At 08:00 PM 5/10/2006, you wrote:

>Hello there,
>
>This is FYI, I'll solve the problem by changing the SQL statement to a
>more "sane"
>version.
>Here is what I encountered with the following SQL, anyways:
>
>select
> u.id, u.name, u.u_us,
> case when( exists(select a.* from
> add_agents a where a.id_main=:id_add and a.id_user=:id_user) ) then
> "I" else
> case when( exists(select a.* from add_agents a where
> a.id_main=:id_add) ) then
> "T" else
> "F"
> end
> end as has_agents from
>sys_users u where u.id=:id_salesman
>
>I need to get three different kinds of information that I have squeezed into
>this single statement:
>- Am I the agent for the address?
>- If not, does the address have other agents assigned?
>- What's the name of the supplied user? (No relation to any address here)
>
>This might not be the most noble way to do it, but that's not the matter
>here. The problem I encountered is that the parameter-values for
>id_add and id_salesman are mixed up. The query does not work
>as expected when I do the following:
>
> C.ParamByName('id_add' ).AsInteger := AdressId;
> C.ParamByName('id_user' ).AsInteger := UserId;
> C.ParamByName('id_salesman').AsInteger := SalesmanId;
>
>But it works fine when I exchange the values for id_add and id_salesman:
>
> C.ParamByName('id_salesman').AsInteger := AdressId;
> C.ParamByName('id_user' ).AsInteger := UserId;
> C.ParamByName('id_add' ).AsInteger := SalesmanId;
>
>I tried the same query with IBPP, a lower-level Interbase/Firebird API and
>it worked as expected without mixing any values, so I highly suspect
>IBObjects as the culprit.
>
>If you need more background information to reproduce the problem,
>please let me know.

I'm not familiar with the IBPP implementation so I can't surmise
whether what you claim is true or not. However, given that IBPP is
no more than a wrapper for the IB/Fb API, I fancy that your "worked
as expected" was a matter of coincidence.

What I can say is that your Delphi usage of parameters is making the
common (but wrong) assumption that parameters are variables. They
are not. They are placeholders for collections of data
attributes. "Assigning" to a parameter involves applying a
compatible constant value to *that* parameter. You need one
parameter for each replaceable piece in you statement - you can't
"recycle" a parameter and re-use it elsewhere in the statement.

Change your statement to:

select
u.id, u.name, u.u_us,
case when( exists(select 1 from
add_agents a where a.id_main=:id_add and a.id_user=:id_user) ) then
"I" else
case when( exists(select 1 from add_agents a where
a.id_main=:id_add1) ) then
"T" else
"F"
end
end as has_agents from
sys_users u where u.id=:id_salesman

Then, for your assignments:

C.ParamByName('id_add' ).AsInteger := AdressId;
C.ParamByName('id_add1' ).AsInteger := AdressId;
C.ParamByName('id_user' ).AsInteger := UserId;
C.ParamByName('id_salesman').AsInteger := SalesmanId;

Helen