Subject Re: keylinks keysource and select sp
Author jeffplata
> > > Something in this manner:
> > >
> > > mainquery:
> > > sql='select gl, sl, dramount, cramount from rp_tbsl(:period)';
> > >
> > > lookupquery:
> > > sql='select gl, description from accounts';
> > > keylinks=' // what goes here?
> > > keysource=ib_datasource1
> > >
> > > ib_datasource1:
> > > dataset=mainquery;
> > >
> >
> >If this kind of lookup relationship is not possible, what is the
> >way of achieving this effect?
> It's more a question of whether it makes any logical sense.
> The purpose of the keylinks relationship is to provide update
> for the linked key in the keysource. But here ---
> 1) your main set is not an updatable dataset, it is a virtual
> dataset (doesn't exist in the database)
> 2) the linked value is the closest thing you have got to a keylink
> in the main set. What are you trying to do? - use the lookup value
> to change the gl value in a table somewhere? Do you have
> parameterised EditSQL, InsertSQL and DeleteSQL statements in main
> dataset to achieve this? And, if you have, do you understand how
> this will affect the validity of your virtual output?
> So it would assist greatly if you would just explain exactly what
> you're trying to achieve here.
> Helen

Thank you Helen.

There are no edits involved in these queries. In fact, mainquery is a
tib_cursor that pulls data from an sp intended for a report. The
actual query is --
select gl, sl, description, begbal, curdr, curcr, endbal
from rp_tbsl(:period)
-- description here is pulled from the sl table (not gl). Since I
need description from the gl table as well, might have written
mainquery.sql like so --
select gl, (select g.description from gltable g where
as gl_description, description, begbal, curdr, curcr, endbal
from rp_tbsl(:period) r
-- it works, except that I have just pulled a 40 char long column
repeatedly for the total number of rows returned. I said to myself a
secondary scrollable query keylinked/keysourced to mainquery might be
more efficient, but I realized that such is allowed only for real
tables in the db. At the moment, I am using these two components but
have to manually locate the gl code from the gltable.

I have also considered a master-detail relationship, but then there
is no unique identifier returned by rp_tbsl sp.

Can you say, please, if I am on the right track?