[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 ggal...@wyanokegroup.com ph:856.848.9005 Ext 220 The Wyanoke Group http://www.wyanokegroup.com ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] SQL Equiv to Save-list?
INSERT INTO temptable (customerid) SELECT customerid FROM invoicefile You then have your temp table with customerIDs so can use as such select cust.name , cust.addr , cust.city from customerfile cust inner join temptable temp on temp.customerid = cust.id where cust.st = 'ny' So you can then do -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: 08 April 2010 15:09 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 ggal...@wyanokegroup.com ph:856.848.9005 Ext 220 The Wyanoke Group http://www.wyanokegroup.com ___ 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
Re: [U2] SQL Equiv to Save-list?
So for each listname you would need to create a new temp table, and clear the table prior to saving a list? Thanks for the info... George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users- boun...@listserver.u2ug.org] On Behalf Of Symeon Breen Sent: Thursday, April 08, 2010 10:55 AM To: 'U2 Users List' Subject: Re: [U2] SQL Equiv to Save-list? INSERT INTO temptable (customerid) SELECT customerid FROM invoicefile You then have your temp table with customerIDs so can use as such select cust.name , cust.addr , cust.city from customerfile cust inner join temptable temp on temp.customerid = cust.id where cust.st = 'ny' So you can then do -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: 08 April 2010 15:09 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? ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] SQL Equiv to Save-list?
If you mean UniVerse SQL - for a regular query in the same way as a retrieve statement, you can supply an active select list using the SLIST clause. SELECT BOOK_TITLES WITH GENRE = FICTION SELECT * FROM BOOK_TITLES SLIST 0 But if you're not using it in the same way as a retrieve statement i.e. not as a key filter, or using a different SQL variant you will need to create a temporary table to join or a sub query (which often creates an internal temporary table anyway). Brian -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: 08 April 2010 4:03 PM To: U2 Users List Subject: Re: [U2] SQL Equiv to Save-list? So for each listname you would need to create a new temp table, and clear the table prior to saving a list? Thanks for the info... George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users- boun...@listserver.u2ug.org] On Behalf Of Symeon Breen Sent: Thursday, April 08, 2010 10:55 AM To: 'U2 Users List' Subject: Re: [U2] SQL Equiv to Save-list? INSERT INTO temptable (customerid) SELECT customerid FROM invoicefile You then have your temp table with customerIDs so can use as such select cust.name , cust.addr , cust.city from customerfile cust inner join temptable temp on temp.customerid = cust.id where cust.st = 'ny' So you can then do -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: 08 April 2010 15:09 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? ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.801 / Virus Database: 271.1.1/2796 - Release Date: 04/07/10 19:32:00 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] SQL Equiv to Save-list?
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: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] 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 ggal...@wyanokegroup.com ph:856.848.9005 Ext 220 The Wyanoke Group http://www.wyanokegroup.com ___ U2-Users mailing list U2-Users@listserver.u2ug.org 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 U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
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 5678Web: 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: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] 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 ggal...@wyanokegroup.com ph:856.848.9005 Ext 220 The Wyanoke Group http://www.wyanokegroup.com ___ U2-Users mailing list U2-Users@listserver.u2ug.org 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 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
Re: [U2] SQL Equiv to Save-list?
That should be pretty straightforward in U2 SQL - I'd probably put a DISTINCT clause in - otherwise you'll probably get repeating customer records: SELECT DISTINCT customerid FROM invoicefile TO SLIST 9; SELECT cust.name , cust.addr , cust.city, etc FROM customerfile SLIST 9 WHERE cust.st='ny'; Stuart Boydell -Original Message- 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? ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
