Yeah, my way would be overkill where the saved list has keys to the file against which we want to query. It does come in very handy when you have a list of keys from a related file that you want to select a particular field against...
Let's say you have an ORDERS file that has a sequential key and you want to select all the ORDERS records which belong to number of customers from a list of those customer numbers... Maybe... SELECT TO SLIST 0 FROM CUSTOMERS WHERE STATE = 'AZ'; SAVE.LIST CUSTOMER.LIST SELECT * FROM ORDERS WHERE CUSTOMER.ID IN (SELECT EVAL "@RECORD" FROM &SAVEDLISTS& 'CUSTOMER.LIST'); To do this Retrieve you would have to either... LIST ORDERS WITH CUSTOMER.ID '111' '121' '1231'... etc having to key them explicitly -0r- LIST ORDERS WITH CUSTOMER.STATE = "AZ" Where CUSTOMER.STATE is a translate to the CUSTOMERS file. The downside of the second Retrieve solution is the lookup of CUSTOMER.STATE for every ORDERS record resulting in a lot of random I/O, possibly many times for the same customers. The SQL method allows as sinqle sequential pass thru the CUSTOMERS file to find all those who are located in AZ, then a single pass thru the ORDERS file without the translate. Perry -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Clifton Oliver Sent: Thursday, April 08, 2010 3:25 PM To: U2 Users List Subject: Re: [U2] SQL Equiv to Save-list? How about SELECT CUSTNAME, CUSTADDR, CUSTCITY FROM CUSTOMERFILE SLIST 'saved_list-name'; Regards, Clif -- W. Clifton Oliver, CCP CLIFTON OLIVER & ASSOCIATES Tel: +1 619 460 5678 Web: www.oliver.com On Apr 8, 2010, at 1:28 PM, Perry Taylor wrote: > You might try... > > select custname, custaddr, custcity from customerfile where > custid in (select EVAL "@RECORD" from &SAVEDLISTS& > 'the_saved_list_name'); > > Perry > > -----Original Message----- > From: [email protected] > [mailto:[email protected]] On Behalf Of George Gallen > Sent: Thursday, April 08, 2010 8:09 AM > To: U2 Users List > Subject: [U2] SQL Equiv to Save-list? > > Is there a way in SQL without resorting to subqueries to select a table, > then save the resulting field, > Then use that list for a future SQL query? > > ex. select customerid from invoicefile; > save the list of customerid for later use > > <later> > > get that list of customerid > select cust.name , cust.addr , cust.city, etc from customerfile > where cust.id=(list of customerid) and cust.st='ny' > > > Or is this one of those pick "ain't it great we can do this" features? > > > George Gallen > Senior Programmer/Analyst > Accounting/Data Division, EDI Administrator > [email protected] > ph:856.848.9005 Ext 220 > The Wyanoke Group > http://www.wyanokegroup.com > > > > _______________________________________________ > U2-Users mailing list > [email protected] > http://listserver.u2ug.org/mailman/listinfo/u2-users > > CONFIDENTIALITY NOTICE: This e-mail message, including any > attachments, is for the sole use of the intended recipient(s) > and may contain confidential and privileged information. Any > unauthorized review, use, disclosure or distribution is > prohibited. ZirMed, Inc. has strict policies regarding the > content of e-mail communications, specifically Protected Health > Information, any communications containing such material will > be returned to the originating party with such advisement > noted. If you are not the intended recipient, please contact > the sender by reply e-mail and destroy all copies of the > original message. > _______________________________________________ > U2-Users mailing list > [email protected] > http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users
