Hi Brian and others that posted suggestions.
I finally got around to concentrating more on my problem with u_tvs (see
below...). I must admit that I was somewhat blinded by the "forest & trees"
bit, having worked on this and other problems for way too long!
As a result of the replies and other suggestions, I finally ended up with 4
(dropped level 5) datastores (n_ds), each pre-retrieved to supply the
required data for each level. Level 1 is an external DW manually populated.
(Thanks Brian for pointing out the non-requirement for Primary keys - my
assumption based on u_tv more than trying it with u_tvs!) Each of the others
have of_SetAppend set to TRUE and a quick switch of txn objects pulls data
from both databases. Filter arguments then manage the linking of the various
levels in the treeview. Level 5 is now DW linked to the selectionchanged
event for Level 4.
Although at this stage I am testing the server DB against DB2 V5.2 on NT
(haven't got my remote connection to the AS/400 setup for testing yet),
performance is pretty good.
I also tried a single DB/DW call and looped through the resultset populating
each level manually which, while acceptably fast enough, was ultimately a
pain in the butt.
One question I have for Brian - you mentioned doing a Union across
transaction objects - is this from embedded SQL or DW?, can't seem to figure
this one out but it sounds interesting!
Thanks again to everybody that replied
Best regards
+---------------------------------------------------------------------------
------------------------------+
Bruce Nordstrand
KSL Consulting Pty Ltd
P.O. Box 4852, North Rocks, NSW 2151, Australia
Phone: +61 2 9873-6256 Fax: +61 2 9873-6216
Email: [EMAIL PROTECTED] Web: http://www.ksl.com.au
US Fax: (419) 791-3469 - By efax.com (http://www.efax.com)
+---------------------------------------------------------------------------
------------------------------+
-----Original Message-----
From: Brian Grimm [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 08, 1999 5:00 AM
To: Bruce Nordstrand
Cc: [EMAIL PROTECTED]
Subject: Re: PFCSIG RE: u_tvs Driving me Nuts!
Bruce,
The PFC 6.5 u_tvs should not require key columns anymore. U_tv does,
however.
We were able to take away that restriction in 6.0 with the exposure of the
datawindow's internal identity column. Thus, external datasources are ok.
Based on my limited understanding of your problem, I would create a
datastore
for each level of your tree. The level 1 datastore would have 2 records in
it,
Local and Remote. The level 2 datastore would need to be a crossdatabase (2
transaction objects) SQL union type statement. You could first, retrieve
each
database DIVMINOR seperately, and denote the location the DIVMiNOR came from
as
the source column (0 = remote, 1 = local) and combine all the records
(rowsmove/rowscopy) into 1 datastore and use that datastore as the source
for
level 2 with the datasource as the retrieval argument. Your SQL would look
something like this: Select 0, * from remote.DIVMINOR, and select 1, * from
local.DIVMINOR. To combine all the rows, all column types much match...
You would need to do this for each level because of the way you are doing
it.
Each level would need keys to the previous level carried down with it (a
good
practice to do whenever using a treeview). Thus, your level 3 datastore
would
need the 0/1 key, the divminor key in the result set, and these would be the
retrieval arguments to level 3.
I cannot tell for sure, but it sounds to me like if you may already have the
entire result set you want to put into the tree in non-tree form. If this
is
the case, you may want to look into the recursive aspect of the tree, or use
the
same datasource as each level (1-5) using filtering args (the last arguments
to
of_register) to minimize the returned rows. You may run into a PFC
problem/bug
using the same dataobject name, and if so, you can get around it by saving
the
dataobject 5 times naming it something different each time. Then you would
need
5 separate datastores, but could populate the other 4 with a rowscopy vs a
database retrieve.
A final note, if you use a non SQL/Retrieve registration style, the data
from
your source is actually cached in the treeview/listview so it doesn't
encounter
a null object reference. These registration styles actually use the filter
columns arguments to simulate the retrieval arguments. Look into the
inv_levelsource.of_register() function comments for info on how the filter
arguments work and the inv_levelsource.of_retrieve() function.
Good luck. Let us (the list) know what happens.
Brian Grimm
Bruce Nordstrand <[EMAIL PROTECTED]> on 12/06/99 05:57:00 PM
To: "[EMAIL PROTECTED] (E-mail)" <[EMAIL PROTECTED]>, "[EMAIL PROTECTED]
(E-mail)" <[EMAIL PROTECTED]>
cc: (bcc: Brian Grimm/HOU/ECT)
Subject: PFCSIG RE: u_tvs Driving me Nuts!
Thanks for the answers but it (in my opinion) is not as simple as retrieval
arguments. It may also be a case of working on this for too long and I
cannot see the forest for the trees! I will try and explain to give a little
more insight. (content warning - this is a long post! and I hope all the
little tree diagrams come out ok!).
The database has the tables I need, structured as follows (key columns
only):
DIVMINOR pkid, majordiv_pkid - 5 Records
DIVCAT minordiv_pkid, cat_pkid - 13 Records
CATEGORY pkid - 13 Records
PRODUCT pkid - 341 Records
PROMOTION pkid - 3 Records
PROMPRODUCT promotion_pkid, product_pkid - 3 Records
NOTE: the record counts are only what's in the Local DB now.
The actual tree display would look something like this:
Local Database
Division 1
Category 1
Product 1
Promotion
Promotion
Product 2
Promotion
Category 2
etc
etc
Division 2
etc
etc
Server Database
Division 1
Category 1
Product 1
Promotion
Promotion
Product 2
Promotion
Category 2
etc
etc
Division 2
etc
etc
If I wanted to display all the items from all tables at all levels, then
retrieval arguments would be fine. But the actual controlling datasource is
the joined result of PROMOTION & PROMPRODUCT and from the diagram above
displying 341 Products is not an option, the 3 Promoted Products are from 2
CATEGORIES which corelate to 2 DIVMINOR's. Now, also throw in a column
(sourcedb) in the PROMOTION table that defines (through 0/1) which database
the record is coming from (I am connecting to 2 databases - Local ASA 6.0
and Server DB2/400). Consider also that PRODUCT's may appear on multiple
PROMOTIONS at different dates.
In effect I am looking at this (based on the DB content)
Database
Division 1
Category 1
Product 1
Promotion 3
Product 2
Promotion 3
Division 2
Category 2
Product 3
Promotion 3
As you can see there is an indirect relationship with the higher level
tables that must be derived from data in PROMPRODUCT. I am actually joining
PROMOTION, PROMPRODUCT and PRODUCT to give me prom_pkid, prod_pkid and
cat_pkid. Extending the SQL further to link the upper levels is possible
but I cannot see the point in executing it multiple times, once for each
level just to get description columns for the upper levels. I have tried
this, but on certain Accounts it can take up to 20 secs to execute yet on
others it takes under 1 sec (pass, next question!!) but that is another
issue.
So what I have is an nvo with datastores for each level (except DIVCAT which
is only a joing table). I retrieve the Level 5 datastore
(Promotion/Proproduct join)from each of the databases (of_SetAppend() is
TRUE) which gives me the keys for the 2 levels above indirectly). I then
extract the cat_pkid column and do a Find(...) on cached Category data to
give me the Minor Division key (CATEGORY table is joined with DIVCAT), which
I then use to lookup the name using cached Minor Division data. I do also
this with the product_pkid to give me the Product Name. (By the way this is
similiar to the way it is done in VB where this app is being converted
from.)
As I see it, u_tvs populates each level without regard to the content of the
lower levels and uses retrieval arguments to "filter" only the lower levels
which does not work in my case. u_tvs also requires Key columns to be
present so an external DW for Database level cannot be used. There inlies
the problem.
SO, I hope you didn't get bored while reading this and things are now a
ltiile clearer.
Best regards
+---------------------------------------------------------------------------
------------------------------+
Bruce Nordstrand
KSL Consulting Pty Ltd
P.O. Box 4852, North Rocks, NSW 2151, Australia
Phone: +61 2 9873-6256 Fax: +61 2 9873-6216
Email: [EMAIL PROTECTED] Web: http://www.ksl.com.au
US Fax: (419) 791-3469 - By efax.com (http://www.efax.com)
+---------------------------------------------------------------------------
------------------------------+
> [EMAIL PROTECTED] HOSTED BY IIGG, INC. FOR HELP WITH LIST SERVE COMMANDS, ADDRESS
> A MESSAGE TO [EMAIL PROTECTED] WITH THE FOLLOWING MESSAGE: help pfcsig
> SEND ALL OTHER INQUIRES TO [EMAIL PROTECTED]
