ca.portal.admin

This should be simple but..

Discussion created by ca.portal.admin on Feb 26, 2010
Latest reply on Feb 26, 2010 by ca.portal.admin
I have what at first glance appears to be a really simple problem. =
However I can't get it right.
=20
I have 3 record types in a normal NON-SQL IDMS database. It is a simple =
hierarchy.
=20
PWOR is a CALC record which owns PTRA in set PWOR-PTRA .
=20
PTRA is stored VIA PWOR-PTRA. PTRA owns PCOD in set PTRA-PCOD.
=20
PCOD is stored VIA PTRA-PCOD.
=20
Both sets are MA.
=20
It is possible that a PWOR can exist which has no PTRA members.
=20
It is possible that a PTRA can exist which has no PCOD members.
=20
I want to code an SQL statement which will return a result table which =
has all combinations derived from the hierarchy. If the sets are empty =
then I still want a row back with nulls in it.
=20
So the result table would be a combination of these;
=20
=20
PWOR PTRA PCOD
=20
or
=20
PWOR PTRA null
=20
or=20
=20
PWOR null null
=20
There are no foreign keys so it has to be done on the set relationships. =
Consider this.
=20
SELECT * FROM PWOR,PTRA,PCOD WHERE ""PWOR-PTRA"" AND ""PTRA-PCOD"";
=20
That will give all the rows back which look like the first example line =
but if a PWOR owns a PTRA which has no PCOD then there will be no row =
returned and if a PWOR has no PTRA then no row will be returned.
=20
It would seem likely that a Preserve is required but that is =
problematic. If I code: =20
=20
SELECT * FROM PWOR,PTRA,PCOD WHERE ""PWOR-PTRA"" AND ""PTRA-PCOD"" PRESERVE =
PTRA;
=20
Then I will get the second variation. I will get a row for each PWOR =
PTRA combination with no PCOD. The PCOD will be null - great. But the =
Preserve is then generating rows for all the cartesian combinations up =
and down the hierarchy. Because I have preserved PTRA it will return a =
row for each of the PWOR or PCOD combinations regardless of the set =
connection - which in principle is correct but not what I want.
=20
Any ideas?=20
=20
______________________________________________________________
=20
Chris Trayler, IXD
Bank Julius Baer & Co. Ltd.
P. O. Box, CH-8010 Z=FCrich, Switzerland
Telephone +41 (0)58 887 4332, Fax +41 (0)58 887 4969
www.juliusbaer.com <http://www.juliusbaer.com/>=20
=20
______________________________________________________________
=20
*****JuliusBaer Disclaimer***** This e-mail is for the intended =
recipient only and may contain confidential or privileged information. =
If you have received this e-mail by mistake, please contact us =
immediately and completely delete it (and any attachments) and do not =
forward it or inform any other person of its contents. If you send us =
messages by e-mail, we take this as your authorization to correspond =
with you by e-mail, however, we will not accept the electronic =
transmission of orders/instructions without a specific agreement being =
in place to govern the same. If you do not wish to receive any further =
e-mail correspondence please let us know. E-mail transmission cannot be =
guaranteed to be secure or error-free as information could be =
intercepted, amended, corrupted, lost, destroyed, arrive late or =
incomplete, or contain viruses. Neither the Julius Baer Group nor the =
sender accept liability for any errors or omissions in the content of =
this message which arise as a result of its e-mail transmission. Please =
note that all e-mail communications to and from the Julius Baer Group =
may be monitored. This communication is for informational purposes only. =
It is not intended as an offer or solicitation for the purchase or sale =
of any financial instrument or as an official confirmation of any =
transaction.
"
IDMS Public Discussion Forum
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Re: This should be simple but..
"Hi Chris,

One solution is to define two views:

CREATE VIEW sqlschema.PWOR_PTRA (ROWID, col_1,,,col_n) AS
SELECT PTRA.ROWID, col_1,,,col_n FROM nonsql.PWOR,nonsql.PTRA
WHERE ""PWOR-PTRA"" PRESERVE PWOR;

CREATE VIEW sqlschema.PTRA_PCOD (ROWID, col_a,,,col_z) AS
SELECT PTRA.ROWID, col_a,,,col_z FROM nonsql.PTRA, nonsql.PCOD
WHERE ""PTRA-PCOD"" PRESERVE PTRA;

Note that views should be defined in a nonsql schema because a view is not
allowed in a sql schema.

Then use ROWID of PTRA to join the two views with PRESERVE.

SELECT * FROM sqlschema.PWOR_PTRA A, sqlschema.PTRA_PCOD B
WHERE A.ROWID = B.ROWID PRESERVE A;

Onni Kukkonen
Vegasoft

Outcomes