Alan, All of your posts have been posted. Keep up the good work. Jeff Young Sr. Programmer Analyst Dynax Solutions, Inc. A wholly owned subsidiary of enherent Corp. IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions V5R2 IBM Certified Specialist- e(logo) server i5Series Technical Solutions Designer V5R3 IBM Certified Specialist- e(logo)server i5Series Technical Solutions Implementer V5R3 ----- Original Message ---- From: Alan Shore <AlanShore@xxxxxxxx> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> Sent: Wednesday, December 6, 2006 3:07:42 PM Subject: Re: SQL Delete question This is just a test. I have responded to this original question a few times, but I have yet to see my response posted. Alan Shore NBTY, Inc (631) 244-2000 ext. 5019 AShore@xxxxxxxx midrange-l-bounces@xxxxxxxxxxxx wrote on 12/06/2006 03:02:28 PM:
I assume you have the SQL licensed product - then you also have Query Manager. Run the STRQM command, take option 1, if "Query creation mode" says Prompt, press F19 to set it to SQL. Change the library if you want. Put a 1 in the opt column and put in a name, say MYQRY. Press enter. Put the following statement into the editor: select distinct * from mylib/myfile At this point you don't even have to create the QMQRY - you can test it using F5. The first time, choose display (1) for output, to see that the result is what you want. Then exit and do F5 again - this time choose file (3) for output - you will get a prompt for the output file. Put in the same name as the one in the select statement - mylib/myfile. This will replace the contents of mylib/myfile with the result of the SELECT statement - in place - with no warning. This is dangerous - always make a backup first. Or verify as suggested above. If you do not have the STRQM command, you can CRTSRCPF yourlib/QQMQRYSRC rcdlen(91), add a member, put the above statement into it, execute the CRTQMQRY command against that source member, use STRQMQRY source to run the statement and choose the output type you want - similar to above. HTH Vern -------------- Original message -------------- From: "Jim Essinger" <dilbernator@xxxxxxxxx>Greetings! I have a file that, among other fields, has Account_Number, Date,
number. In the file there are multiple occurrences of those three fields.
Account_Number Date Sequence_number 1 20061206 01 1 20061206 01 1 20061206 01 1 20061206 02 1 20061206 02 1 20061206 02 1 20061206 03 1 20061206 03 1 20061206 03 1 20061206 04 1 20061206 04 1 20061206 04 What I am looking for is a way to delete all but one occurrence of the repeated data to get a file that looks like; Account_Number Date Sequence_number 1 20061206 01 1 20061206 02 1 20061206 03 1 20061206 04 Is this something I can do with SQL? If so, what would the SQL
look like? Jim -- Jim Essinger Senior Programmer/Analyst SLFIMA PO Box 1000 Fruitland, ID 83619 208-452-4058 x133 -- This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.-- This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.
This mailing list archive is Copyright 1997-2013 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact