Thanks all for your assistance. I managed to get it to work using Andy's suggestion of a Unibasic SELECTINDEX Statement -: SELECTINDEX "RECORD_ID", @ID FROM AUDIT.HIST :-
And then just looping through to find the earliest record. Your assistance was much appreciated Bjorn PS: David. IF you know a better way, please feel free to advise. -----Original Message----- From: owner-u2-us...@listserver.u2ug.org [mailto:owner-u2-us...@listserver.u2ug.org] On Behalf Of Bjorn Behr Sent: 29 April 2009 11:28 AM To: u2-users@listserver.u2ug.org Subject: RE: [U2] I-Descriptor Help David You are correct with what I require. I am trying to get it to work using an I-Type because - It is on the QUOTES File, referencing the AUDIT.HIST File which has a relationship of one to many. - It is required to be on a field on the Quotes File as it is used to extract data for the MIS system for reporting. - If you can suggest another way, I am more than open to suggestions. Thanks Bjorn -----Original Message----- From: owner-u2-us...@listserver.u2ug.org [mailto:owner-u2-us...@listserver.u2ug.org] On Behalf Of Hona, David Sent: 29 April 2009 11:00 AM To: 'u2-users@listserver.u2ug.org' Subject: RE: [U2] I-Descriptor Help Bjorn Executing queries via an I-type simply doesn't work or if it does - you'll get strange results. Your requirements are a little unclear...but I am guessing here that you're wanting to select the first timestamped record by the specified unique RECORD_ID? Do you have I-types for the Date and Time elements of the @ID - if so, you could: SELECT AUDIT_HIST WITH RECORD_ID = '1234' BY DATE BY TIME FIRST 1 Or something similar - any particular reason you need to have an I-type? Regards David -----Original Message----- From: owner-u2-us...@listserver.u2ug.org [mailto:owner-u2-us...@listserver.u2ug.org] On Behalf Of Bjorn Behr Sent: Wednesday, 29 April 2009 6:05 PM To: u2-users@listserver.u2ug.org Subject: RE: [U2] I-Descriptor Help Kishor Sorry, should not be BY RECORD_ID, should be BY @ID The key is RECORD_ID:DATE:TIME, RECORD_ID is in Field <1> in the record and refers the the @ID of another file There can be many RECORDS in the AUDIT.HIST File with the same RECORD_ID, but there DATE and/or TIME will differ. Regards Bjvrn Behr SUMMIT CORE Developer Tel No: +27 12 673 3251 Fax No: +27 12 673 4681 E-Mail: be...@frstia.co.za -----Original Message----- From: owner-u2-us...@listserver.u2ug.org [mailto:owner-u2-us...@listserver.u2ug.org] On Behalf Of kis...@parmars.com Sent: 29 April 2009 09:46 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] I-Descriptor Help Hi Bjorn, Why are you using the BY RECORD_ID clause when you are selecting on RECORD_ID = '1537044' ? Should this be @ID for the first record on the AUDIT_HIST file? Regards, Kishor ************** IMPORTANT MESSAGE ***************************** This e-mail message is intended only for the addressee(s) and contains information which may be confidential. If you are not the intended recipient please advise the sender by return email, do not use or disclose the contents, and delete the message and any attachments from your system. Unless specifically indicated, this email does not constitute formal advice or commitment by the sender or the Commonwealth Bank of Australia (ABN 48 123 123 124) or its subsidiaries. We can be contacted through our web site: commbank.com.au. If you no longer wish to receive commercial electronic messages from us, please reply to this e-mail by typing Unsubscribe in the subject line. ************************************************************** ------- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ The information in or attached to this e-mail message is confidential and may be subject to legal privilege and client confidentiality. If you are not the intended recipient of this e-mail or the authorised representative of the recipient then please notify the sender of this e-mail immediately by return e-mail and delete this message from your system. You may not print, store, forward or copy this message or any part thereof or disclose or cause information in this message to be disclosed to any other person. FRSTIA cannot be held liable for any harm or loss resulting from malicious software code or viruses in this message or attachments, including data corruption resulting there from. Employees or representatives of FRSTIA are not permitted to send unsolicited messages ("spam") and FRSTIA cannot be held liable for their sending of such mail. If you regard this message as spam, please inform us immediately by sending an email to i...@frstia.co.za. If this message contains offensive, derogatory or defamatory statements or materials, it means the message has been sent outside the sender's scope of employment with FRSTIA and only the sender can be held liable in his/her personal capacity. ------- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ The information in or attached to this e-mail message is confidential and may be subject to legal privilege and client confidentiality. If you are not the intended recipient of this e-mail or the authorised representative of the recipient then please notify the sender of this e-mail immediately by return e-mail and delete this message from your system. You may not print, store, forward or copy this message or any part thereof or disclose or cause information in this message to be disclosed to any other person. FRSTIA cannot be held liable for any harm or loss resulting from malicious software code or viruses in this message or attachments, including data corruption resulting there from. Employees or representatives of FRSTIA are not permitted to send unsolicited messages ("spam") and FRSTIA cannot be held liable for their sending of such mail. If you regard this message as spam, please inform us immediately by sending an email to i...@frstia.co.za. If this message contains offensive, derogatory or defamatory statements or materials, it means the message has been sent outside the sender's scope of employment with FRSTIA and only the sender can be held liable in his/her personal capacity. ------- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/