Subject Re: [IBO] Is this a limitation
Author Helen Borrie
At 11:24 AM 10/07/2004 +0800, you wrote:
>In IB_query i have this kind of sql statement.
>select m.*,

slightly incorrect..

> (select "Name" from "customer" d

(select d."Name" from "customer" d

>where d."Cust ID" = m."customerid")
>as "Name"

as Name1

>from "customerinvoicerequirement" m
>And Iam not able to add the "Name" field into the orderinglinks. Is
>there a way to solve this thing?

No, you can't order on an expression field, per se. In Firebird 1.5 you
can order on an expression field provided you use either the degree number
of the field in the output set. This would be tricky to achieve with IBO,
but I expect it could be done by overriding the default behaviour at the
point where IBO constructs the new ORDER BY clause in response to a
reordering request. I think there are other conceptual issues here,
though, regarding the control you are trying to use it with.

>I have try this kind of sql instead.
>select m.*, d."Name"
> from "customerinvoicerequirement" m left join "customer" d on d."Cust
>ID" = d."customerid"
>With this kind of sql I have add the "Name" field into the ordering
>links. But this statement doesn't work well when setting up a

No, because *each* item in a lookup must be unique. Uniqueness in this set
isn't possible because the left join logically generates "flat"
(de-normalized) sets with nulls in the right-hand stream. The only way to
guarantee uniqueness in a joined set is to use an inner join. However,
that doesn't mean "any old inner join" for your purposes - you would need
to define a set that was capable of producing unique key values to
correspond to the "polling" key.

Your ".*" field selection doesn't seem appropriate to a lookup
relationship, which must be pinned down to a single VALUE corresponding to
the lookup key. The cleanest lookup dataset has two fields: the unique
key and the corresponding value.

What matters for the Keysource/Lookup relationship are the relationship
between the "polling" key in the parent (Keysource) dataset and the
"responding" key (which must be unique) in the lookup dataset. This
Keysource/Lookup relationship is established by the paired KeyLinks.

You haven't explained, but it seems you might be trying to use the
ib_lookupcombo as the UI for the detail side of a master/detail
relationship. It's not designed for that and it won't work. (I'm sure you
and I have had this conversation before....)