It seems to me that the easiest way to create a "list of orders where the customers state is Arizona" is to first create a translation FROM the Order file INTO the Customer file which will return the customers state and then just select on that new dictionary entry
-----Original Message----- From: Rick Nuckolls <r...@lynden.com> To: 'U2 Users List' <u2-users@listserver.u2ug.org> Sent: Tue, Aug 27, 2013 4:01 pm Subject: Re: [U2] Select from one file with save-list from another file Your response supposes that the ORDER.ID IS is in the CUSTOMERS file/table, which is not the criteria of the original problem. The problem presented is a bit difficult to work around in RETRIEVE, and even harder if you are restricted to not programming, as Brandon evidently is. We wrote a utility that will take a preselected list and create a new list based on the values of an alternate key, which would functions as SELECT CUSTOMERS WITH STATE = 'AZ' SELECTINDEX ORDERS CUSTOMER.ID ## use the CUSTOMER.ID index of ORDERS to find those orders related to customers within the list; return a list of ORDERS ids. Alternately, in Brandon's example, it might work to create a translate field from the Y.INFO file to the Y.INFO status field, but that would probably involve a less efficient overall selection, and would not be practical if the data common to the two tables (packet.id), is the primary key to neither, in which case: SELECT X.INFO WITH status = 'ccc' SAVING [UNIQUE] packet.id SELECTINDEX Y.INFO packet.id Sorry that I am unable to send out the code for SELECTINDEX, but, a simple version is straightforward to program using the BASIC "SELECTINDEX" function. -Rick -----Original Message----- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jo Lester Sent: Tuesday, August 27, 2013 2:54 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Select from one file with save-list from another file SELECT CUSTOMERS WITH STATE = 'AZ' SAVING ORDER.ID LIST ORDERS > From: r...@lynden.com > To: u2-users@listserver.u2ug.org > Date: Tue, 27 Aug 2013 14:42:34 -0700 > Subject: Re: [U2] Select from one file with save-list from another file > > I found an old example on line that might get you started > > SELECT TO SLIST 0 FROM CUSTOMERS WHERE STATE = 'AZ'; > SAVE.LIST CUSTOMER.LIST > > SELECT * FROM ORDERS WHERE CUSTOMER.ID IN (SELECT EVAL " <at> RECORD" FROM > &SAVEDLISTS& 'CUSTOMER.LIST'); > > The extra syntax that might help is > > SELECT TO SLIST <savedlist #> FROM ORDERS .... > > If CUSTOMER.LIST is your X.INFO and ORDERS is your Y.INFO... > > Rick > > -----Original Message----- > From: u2-users-boun...@listserver.u2ug.org > [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Hilk, Brandon > Sent: Tuesday, August 27, 2013 1:24 PM > To: u2-users@listserver.u2ug.org > Subject: [U2] Select from one file with save-list from another file > > Hello! > > > > UniVerse 10.1, HP/UX, Pick. > > > > Is there a way I can build a save-list from a selection from one file > X.INFO (where the record ID is an 8 digit number) and use that list to > select from file Y.INFO (where that 8 digit number is not the record ID > but does exist as a "foreign key" elsewhere in the file). In SQL this > would be considered a join and would look something like this: > > > > select * > > from X.INFO, Y.INFO > > where X.INFO.packet.id=Y.INFO.packet.id > > and X.INFO.status='ccc'; > > > > I can run this in TCL and have it return the results I want but can't > build a save-list from it because I don't know the UniVerse/SQL syntax > to do so. > > > > So to summarize, is there a way to use a save-list built from X.INFO to > make another save-list comprised of the record ID's from Y.INFO? > > > > Thanks for any advice you can give. > > > > _______________________________________________ > U2-Users mailing list > U2-Users@listserver.u2ug.org > http://listserver.u2ug.org/mailman/listinfo/u2-users > _______________________________________________ > U2-Users mailing list > U2-Users@listserver.u2ug.org > http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
