I do it in a similar way... SELECT @ID FROM EINVOICES WHERE iInvoiceRef IN (SELECT EVAL "@RECORD" FROM &SAVEDLISTS& 'INV.REF.LIST');
... But I agree there should be a more "natural" way of doing this. Perry Taylor -----Original Message----- From: owner-u2-us...@listserver.u2ug.org [mailto:owner-u2-us...@listserver.u2ug.org] On Behalf Of Boydell, Stuart Sent: Monday, January 26, 2009 11:40 PM To: u2-users@listserver.u2ug.org Subject: [U2] [UV] SQL select subquerying a saved list. Hi, I'm wondering if someone can show me a more elegant way to do a SQL subquery. I have a saved select list with a list of values which I want to test for in a SQL select IN clause. Eg. SELECT @ID FROM EINVOICES WHERE iInvoiceRef IN (SELECT EVAL 'TRANS('&SAVEDLISTS&","INV.REF.LIST","-1","")' FROM VOC WHERE @ID = 'WHO'); This works, but I don't like the format of the subquery. I have done this type of thing before but when the list was a list of keys to another file I could create a subquery like SELECT @ID FROM OTHER.FILE SLIST INV.REF.LIST. In this case iInvoiceRef doesn't link to another file. I have used the TRANS/VOC arrangement which seems like a hack to me, it's also relatively slow. Does anyone know a 'nicer' way to get back a select list to use with the IN clause? Regards, Stuart Boydell ********************************************************************** This email message and any files transmitted with it are confidential and intended solely for the use of addressed recipient(s). If you have received this communication in error, please reply to this e-mail to notify the sender of its incorrect delivery and then delete it and your reply. It is your responsibility to check this email and any attachments for viruses and defects before opening or sending them on. Spotless collects information about you to provide and market our services. For information about use, disclosure and access, see our privacy policy at http://www.spotless.com.au Please consider our environment before printing this email. ********************************************************************** ------- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ 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 To unsubscribe please visit http://listserver.u2ug.org/
