Re: [U2] Selection Suggestions for using UV instead of SQL
Thank you. I thought I was going crazy when I couldn't find it again.
Robert Norman
On 10/18/2012 9:28 AM, Daniel McGrath wrote:
You can find it here:
https://u2devzone.rocketsoftware.com/accelerate/articles/u2-select/u2-select
Cheers,
Dan McGrath
Product Manager
Rocket Software
4600 S. Ulster Street **Suite 1100 **Denver, CO 80237 * USA
t: +1.720.475.8098 * m: +1.617.630.7392 * e:dmcgr...@rocketsoftware.com w:
rocketsoftware.com/u2
-Original Message-
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Robert
Sent: Thursday, October 18, 2012 10:26 AM
To: U2 Users List
Subject: Re: [U2] Selection Suggestions for using UV instead of SQL
There used to be a version of a SELECT verb on the Rocket website. I think it
was in the white papers or technical articles section. It provided source code
to a verb called SELECTK I think it was called.
If anyone can find it, let me know. I haven't been able to find it after the
first time I read it.
Robert Norman
On 10/17/2012 10:20 AM, George Gallen wrote:
Looks like either UV doesn't have LSELECT, or UV 10.0.2 does not.
George
-Original Message-
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Bill
Haskett
Sent: Wednesday, October 17, 2012 1:16 PM
To: U2 Users List
Subject: Re: [U2] Selection Suggestions for using UV instead of SQL
George:
How about something like:
3 Dev (0)- LSELECT ?
This will create a list of {FileName} keys for records whose field
definition value appears in a defined list.
Syntax:
LSELECT File_Name WITH Dict_Name IN LIST1 TO LIST2 [VERBOSE| -V]
Example: SELECT CUSTOMERS SAMPLE 500
SAVE.LIST MY.LIST
LSELECT SALES.ORDERS WITH CUST.NBR IN MY.LIST TO MY.LIST.2
Notes:
If a destination list is not supplied then keys will be left as the
active select list. If a source list is not supplied then the current
active select list will be used. Lists may be specified as select
buffers (0-8) or as named lists from SAVEDLISTS.
An example of this is as follows:
3 Dev (0)- SELECT MASTER 260605
2 records selected to list 0.
3 Dev (0)- SAVE-LIST MY.LIST
2 key(s) saved to 1 record(s).
3 Dev (0)- LSELECT ARTMASTER WITH CLIENTNO IN MY.LIST VERBOSE (Parsed
Command) LSELECT ARTMASTER WITH CLIENTNO IN MY.LIST TO 0
(Selected List)
List Source MY.LIST found 2 items.
(Create Temp File)
LSELECT0031 file created with MOD = 5
(Loaded List to Temp File) - 2 items.
977 key(s) saved to 1 record(s).
(Selected Result List)
UDT.COMMAND = select ARTMASTER WITH EVAL
TRANS('LSELECT0031',CLIENTNO,'@ID','X') GT TO 0 UDT.RESPONSE =
?977 records selected to list 0.??
Destination 0
Named List 0
(Delete Temp File)
LSELECT0031 file deleted...
3 Dev (0)
This now has a list of (/*ARTMASTER*/) records that contain a client#
in the original select list, MY.LIST.
HTH,
Bill
Untitled Page
___
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
___
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] Selection Suggestions for using UV instead of SQL
Not sure I can send the delete, not sure if it is actually delete the data. We could test, it and see. Support:James F Thompson Senior Systems Analyst Cypress Business Solutions 678.494.9353 x1510 supp...@cypressesolutions.com On Thursday, October 18, 2012 11:00:38 PM, George Gallen wrote: And we wonder where GIRLS came from??!? George Gallen Senior Programmer/Analyst Accounting/Data Division ggal...@wyanokegroup.com ph:856.848.9005 Ext 220 The Wyanoke Group http://www.wyanokegroup.com From: u2-users-boun...@listserver.u2ug.org [u2-users-boun...@listserver.u2ug.org] On Behalf Of Colin Alfke [alfke...@hotmail.com] Sent: Thursday, October 18, 2012 2:53 PM To: 'U2 Users List' Subject: Re: [U2] Selection Suggestions for using UV instead of SQL In my first programming class in high school (on apple IIe's) we liked to use G$ (with the $ pronounced as string) Colin -Original Message- From: Bill Haskett Sent: October 18, 2012 12:24 PM To: U2 Users List Subject: Re: [U2] Selection Suggestions for using UV instead of SQL Sort of a carry-over from VB when a string was given a variable name with a '$' in it, usually at the end, or the beginning. Habits die hard. :-) Bill ___ 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 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Selection Suggestions for using UV instead of SQL
On 17/10/12 16:40, George Gallen wrote: How would I get the contents of the Index? George Don't have the UV BASIC manual in front of me, but it's something like READINDEX variable FROM filedescriptor, indexname[, indexvalue] If you only pass filedescriptor and indexname, it will return a list of all the index values. If you also pass indexvalue, it will return a list of all keys for that value. I'll have a look later and see if I can find that routine - it's probably on my system somewhere, but my archives aren't that organised... Cheers, Wol -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: Wednesday, October 17, 2012 11:38 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL On 17/10/12 14:59, George Gallen wrote: Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could join the two, then do the select or use a subquery (I'm on UV 10.0.2 - not sure if sub querys work) Just curious how this could be done with UV Native, and without involving a program. I first tried SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID - NSELECT PEOPLE But that returned 0 - since the NSELECT only works off the current select list, not the whole file Yes, I could create a temporary file, then select off that Yes, I could read each people record, then loop through registration to see if it doesn't exist. Both of the above require programming I guess I could create a subroutine that does the looping through registration, and put that into an I descriptor - looking for another option. Hmmm... What I guess I'd do - create an index on file2 based on PID. Create an idescriptor on file1 that gets the contents of that index. I had a routine somewhere that got fed an index value and file, and returned the contents of the index. It was quite useful, iirc. Manipulate it from there. Probably selecting where there is no value matching ...+1+... Cheers, Wol ___ 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 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Selection Suggestions for using UV instead of SQL
I think you mean SELECTINDEX index.name[,key.val] FROM file.var [TO list.num] Martin -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: 18 October 2012 08:55 To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL On 17/10/12 16:40, George Gallen wrote: How would I get the contents of the Index? George Don't have the UV BASIC manual in front of me, but it's something like READINDEX variable FROM filedescriptor, indexname[, indexvalue] If you only pass filedescriptor and indexname, it will return a list of all the index values. If you also pass indexvalue, it will return a list of all keys for that value. I'll have a look later and see if I can find that routine - it's probably on my system somewhere, but my archives aren't that organised... Cheers, Wol -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: Wednesday, October 17, 2012 11:38 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL On 17/10/12 14:59, George Gallen wrote: Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could join the two, then do the select or use a subquery (I'm on UV 10.0.2 - not sure if sub querys work) Just curious how this could be done with UV Native, and without involving a program. I first tried SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID - NSELECT PEOPLE But that returned 0 - since the NSELECT only works off the current select list, not the whole file Yes, I could create a temporary file, then select off that Yes, I could read each people record, then loop through registration to see if it doesn't exist. Both of the above require programming I guess I could create a subroutine that does the looping through registration, and put that into an I descriptor - looking for another option. Hmmm... What I guess I'd do - create an index on file2 based on PID. Create an idescriptor on file1 that gets the contents of that index. I had a routine somewhere that got fed an index value and file, and returned the contents of the index. It was quite useful, iirc. Manipulate it from there. Probably selecting where there is no value matching ...+1+... Cheers, Wol ___ 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 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users Click https://www.mailcontrol.com/sr/MZbqvYs5QwJvpeaetUwhCQ== eUdxXwzJfD+jSPSM4kcQUHfMaOyCAuUs69aXEW7R1X!lw== to report this email as spam. Epicor Software (UK) is a limited company registered in England Wales. Registration Number: 2338274. Registered Office: 6th Floor, One London Wall, London EC2Y 5EB This e-mail and any attachments to it are confidential and is for the use of the intended recipient(s) only. If you have received this e-mail in error, please notify the sender immediately and then delete it. If you are not the intended recipient, you must not use, disclose or distribute this e-mail without the author's prior permission. We have taken precautions to minimize the risk of transmitting software viruses, but we advise you to carry out your own virus checks on any attachment to this message. We cannot accept liability for any loss or damage caused by software viruses. Any views and/or opinions expressed in this e-mail are of the author only and do not represent the views of Epicor Software (UK) Limited or any other company within its group. This message has been scanned for malware by Websense. www.websense.com ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Selection Suggestions for using UV instead of SQL
On 18/10/12 09:29, Martin Braid wrote: I think you mean SELECTINDEX index.name[,key.val] FROM file.var [TO list.num] Martin Followed by a READLIST ... Quite possibly - I can't remember. One *important* point to note - if you are planning to call this from an i-descriptor, as I did, don't forget you MUST save that to a non-default list, or any attempt to LIST the idescriptor will go horribly wrong... Cheers, Wol -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: 18 October 2012 08:55 To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL On 17/10/12 16:40, George Gallen wrote: How would I get the contents of the Index? George Don't have the UV BASIC manual in front of me, but it's something like READINDEX variable FROM filedescriptor, indexname[, indexvalue] If you only pass filedescriptor and indexname, it will return a list of all the index values. If you also pass indexvalue, it will return a list of all keys for that value. I'll have a look later and see if I can find that routine - it's probably on my system somewhere, but my archives aren't that organised... Cheers, Wol -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: Wednesday, October 17, 2012 11:38 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL On 17/10/12 14:59, George Gallen wrote: Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could join the two, then do the select or use a subquery (I'm on UV 10.0.2 - not sure if sub querys work) Just curious how this could be done with UV Native, and without involving a program. I first tried SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID - NSELECT PEOPLE But that returned 0 - since the NSELECT only works off the current select list, not the whole file Yes, I could create a temporary file, then select off that Yes, I could read each people record, then loop through registration to see if it doesn't exist. Both of the above require programming I guess I could create a subroutine that does the looping through registration, and put that into an I descriptor - looking for another option. Hmmm... What I guess I'd do - create an index on file2 based on PID. Create an idescriptor on file1 that gets the contents of that index. I had a routine somewhere that got fed an index value and file, and returned the contents of the index. It was quite useful, iirc. Manipulate it from there. Probably selecting where there is no value matching ...+1+... Cheers, Wol ___ 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 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users Click https://www.mailcontrol.com/sr/MZbqvYs5QwJvpeaetUwhCQ== eUdxXwzJfD+jSPSM4kcQUHfMaOyCAuUs69aXEW7R1X!lw== to report this email as spam. Epicor Software (UK) is a limited company registered in England Wales. Registration Number: 2338274. Registered Office: 6th Floor, One London Wall, London EC2Y 5EB This e-mail and any attachments to it are confidential and is for the use of the intended recipient(s) only. If you have received this e-mail in error, please notify the sender immediately and then delete it. If you are not the intended recipient, you must not use, disclose or distribute this e-mail without the author's prior permission. We have taken precautions to minimize the risk of transmitting software viruses, but we advise you to carry out your own virus checks on any attachment to this message. We cannot accept liability for any loss or damage caused by software viruses. Any views and/or opinions expressed in this e-mail are of the author only and do not represent the views of Epicor Software (UK) Limited or any other company within its group. This message has been scanned for malware by Websense. www.websense.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
Re: [U2] Selection Suggestions for using UV instead of SQL
Since the index files are actually type 25 files, you can always (though of course I would NEVER recommend this ... ) create a file pointer direct to the INDEX.nnn and read the entries as records ... saves grabbing a select list .. Brian -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: 18 October 2012 09:53 To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL On 18/10/12 09:29, Martin Braid wrote: I think you mean SELECTINDEX index.name[,key.val] FROM file.var [TO list.num] Martin Followed by a READLIST ... Quite possibly - I can't remember. One *important* point to note - if you are planning to call this from an i-descriptor, as I did, don't forget you MUST save that to a non-default list, or any attempt to LIST the idescriptor will go horribly wrong... Cheers, Wol -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: 18 October 2012 08:55 To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL On 17/10/12 16:40, George Gallen wrote: How would I get the contents of the Index? George Don't have the UV BASIC manual in front of me, but it's something like READINDEX variable FROM filedescriptor, indexname[, indexvalue] If you only pass filedescriptor and indexname, it will return a list of all the index values. If you also pass indexvalue, it will return a list of all keys for that value. I'll have a look later and see if I can find that routine - it's probably on my system somewhere, but my archives aren't that organised... Cheers, Wol -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: Wednesday, October 17, 2012 11:38 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL On 17/10/12 14:59, George Gallen wrote: Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could join the two, then do the select or use a subquery (I'm on UV 10.0.2 - not sure if sub querys work) Just curious how this could be done with UV Native, and without involving a program. I first tried SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID - NSELECT PEOPLE But that returned 0 - since the NSELECT only works off the current select list, not the whole file Yes, I could create a temporary file, then select off that Yes, I could read each people record, then loop through registration to see if it doesn't exist. Both of the above require programming I guess I could create a subroutine that does the looping through registration, and put that into an I descriptor - looking for another option. Hmmm... What I guess I'd do - create an index on file2 based on PID. Create an idescriptor on file1 that gets the contents of that index. I had a routine somewhere that got fed an index value and file, and returned the contents of the index. It was quite useful, iirc. Manipulate it from there. Probably selecting where there is no value matching ...+1+... Cheers, Wol ___ 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 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users Click https://www.mailcontrol.com/sr/MZbqvYs5QwJvpeaetUwhCQ== eUdxXwzJfD+jSPSM4kcQUHfMaOyCAuUs69aXEW7R1X!lw== to report this email as spam. Epicor Software (UK) is a limited company registered in England Wales. Registration Number: 2338274. Registered Office: 6th Floor, One London Wall, London EC2Y 5EB This e-mail and any attachments to it are confidential and is for the use of the intended recipient(s) only. If you have received this e-mail in error, please notify the sender immediately and then delete it. If you are not the intended recipient, you must not use, disclose or distribute this e-mail without the author's prior permission. We have taken precautions to minimize the risk of transmitting software viruses, but we advise you to carry out your own virus checks on any attachment to this message. We cannot accept liability for any loss or damage caused by software viruses. Any views and/or opinions expressed in this e-mail
Re: [U2] Selection Suggestions for using UV instead of SQL
I'll have to play with this later - thanks for the info... George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Brian Leach Sent: Thursday, October 18, 2012 6:50 AM To: 'U2 Users List' Subject: Re: [U2] Selection Suggestions for using UV instead of SQL Since the index files are actually type 25 files, you can always (though of course I would NEVER recommend this ... ) create a file pointer direct to the INDEX.nnn and read the entries as records ... saves grabbing a select list .. Brian -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: 18 October 2012 09:53 To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL On 18/10/12 09:29, Martin Braid wrote: I think you mean SELECTINDEX index.name[,key.val] FROM file.var [TO list.num] Martin Followed by a READLIST ... Quite possibly - I can't remember. One *important* point to note - if you are planning to call this from an i-descriptor, as I did, don't forget you MUST save that to a non-default list, or any attempt to LIST the idescriptor will go horribly wrong... Cheers, Wol -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: 18 October 2012 08:55 To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL On 17/10/12 16:40, George Gallen wrote: How would I get the contents of the Index? George Don't have the UV BASIC manual in front of me, but it's something like READINDEX variable FROM filedescriptor, indexname[, indexvalue] If you only pass filedescriptor and indexname, it will return a list of all the index values. If you also pass indexvalue, it will return a list of all keys for that value. I'll have a look later and see if I can find that routine - it's probably on my system somewhere, but my archives aren't that organised... Cheers, Wol -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: Wednesday, October 17, 2012 11:38 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL On 17/10/12 14:59, George Gallen wrote: Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could join the two, then do the select or use a subquery (I'm on UV 10.0.2 - not sure if sub querys work) Just curious how this could be done with UV Native, and without involving a program. I first tried SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID - NSELECT PEOPLE But that returned 0 - since the NSELECT only works off the current select list, not the whole file Yes, I could create a temporary file, then select off that Yes, I could read each people record, then loop through registration to see if it doesn't exist. Both of the above require programming I guess I could create a subroutine that does the looping through registration, and put that into an I descriptor - looking for another option. Hmmm... What I guess I'd do - create an index on file2 based on PID. Create an idescriptor on file1 that gets the contents of that index. I had a routine somewhere that got fed an index value and file, and returned the contents of the index. It was quite useful, iirc. Manipulate it from there. Probably selecting where there is no value matching ...+1+... Cheers, Wol ___ 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 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users Click https://www.mailcontrol.com/sr/MZbqvYs5QwJvpeaetUwhCQ== eUdxXwzJfD+jSPSM4kcQUHfMaOyCAuUs69aXEW7R1X!lw== to report this email as spam. Epicor Software (UK) is a limited company registered in England Wales. Registration Number: 2338274. Registered Office: 6th Floor, One London Wall, London EC2Y 5EB This e-mail and any attachments to it are confidential and is for the use of the intended recipient(s) only. If you have received this e-mail in error, please notify the sender immediately and then delete it. If you are not the intended recipient, you must not use, disclose or distribute this e-mail
Re: [U2] Selection Suggestions for using UV instead of SQL
George: I wrote a TREE.CHECK program that does the following: 2 Dev (0)- TREE.CHECK MASTER -IINDEX_2 Index Check: MASTER, INDEX_1 ?Key... Indexed Value. ?350 1432 1434 G ST CONDO ASSOC ?50 ABBOTT WOOD TOWNHOMES ASSOC ?70 ASPEN HEIGHT CONDO OWNERS ASSC ?80 AUTUMN RIDGE HOMEOWNERS' ASSOC ?90 BONNIE CUSACK ESTATES ASSOC ?110 BOSTON SQUARE CONDOMINIUMS ?115 BRIARCLIFF TOWNHOMES ASSOC ?130 BROOKSHIRE CONDOMINIUM ASSOC ?120 BROOKVIEW CONDOMINIUM ASSOC ?135 CAMPBELL CREEK DUPLEX OWNERS ?140 CAPE HATTERAS AT COMMODORE PRK ?170 CEDARCREST CONDO ASSOC ?175 CHANDELLE ACRES P/O ASSOC ?215 CHUGACH MEADOWS II CONDO ASSOC ?160 CHUGACH MEADOWS TOWNHOME ASSOC ?150 CHUGIAK HANGARS ASSOCIATION ?185 CLEAR CREEK CONDOMINIUM ASSOC ?190 CONCORD HILL HOMEOWNER ASSOC ?180 CREEKSIDE TWNHOMES CONDO ASSOC ?200 CREEKVIEW CONDO ASSOC ??? [N]ext Pg, [P]rev Pg, [F]irst Pg, [L]ast Pg, [A]ccept, [S]top ? I wonder if this would help? Bill By the way, I did post the LSELECT code to pickwiki, under UniQuerySource. - Original Message - *From:* ggal...@wyanokegroup.com *To:* U2 Users List u2-users@listserver.u2ug.org *Date:* 10/18/2012 6:15 AM *Subject:* Re: [U2] Selection Suggestions for using UV instead of SQL I'll have to play with this later - thanks for the info... George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Brian Leach Sent: Thursday, October 18, 2012 6:50 AM To: 'U2 Users List' Subject: Re: [U2] Selection Suggestions for using UV instead of SQL Since the index files are actually type 25 files, you can always (though of course I would NEVER recommend this ... ) create a file pointer direct to the INDEX.nnn and read the entries as records ... saves grabbing a select list .. Brian -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: 18 October 2012 09:53 To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL On 18/10/12 09:29, Martin Braid wrote: I think you mean SELECTINDEX index.name[,key.val] FROM file.var [TO list.num] Martin Followed by a READLIST ... Quite possibly - I can't remember. One *important* point to note - if you are planning to call this from an i-descriptor, as I did, don't forget you MUST save that to a non-default list, or any attempt to LIST the idescriptor will go horribly wrong... Cheers, Wol -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: 18 October 2012 08:55 To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL On 17/10/12 16:40, George Gallen wrote: How would I get the contents of the Index? George Don't have the UV BASIC manual in front of me, but it's something like READINDEX variable FROM filedescriptor, indexname[, indexvalue] If you only pass filedescriptor and indexname, it will return a list of all the index values. If you also pass indexvalue, it will return a list of all keys for that value. I'll have a look later and see if I can find that routine - it's probably on my system somewhere, but my archives aren't that organised... Cheers, Wol -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: Wednesday, October 17, 2012 11:38 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL On 17/10/12 14:59, George Gallen wrote: Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could join the two, then do the select or use a subquery (I'm on UV 10.0.2 - not sure if sub querys work) Just curious how this could be done with UV Native, and without involving a program. I first tried SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID - NSELECT PEOPLE But that returned 0 - since the NSELECT only works off the current select list, not the whole file Yes, I could create a temporary file, then select off that Yes, I could read each people record, then loop through registration to see if it doesn't exist. Both of the above require programming I guess I could create a subroutine that does the looping through registration, and put that into an I descriptor - looking for another option. Hmmm... What I guess I'd
Re: [U2] Selection Suggestions for using UV instead of SQL
There used to be a version of a SELECT verb on the Rocket website. I
think it was in the white papers or technical articles section. It
provided source code to a verb called SELECTK I think it was called.
If anyone can find it, let me know. I haven't been able to find it after
the first time I read it.
Robert Norman
On 10/17/2012 10:20 AM, George Gallen wrote:
Looks like either UV doesn't have LSELECT, or UV 10.0.2 does not.
George
-Original Message-
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Bill Haskett
Sent: Wednesday, October 17, 2012 1:16 PM
To: U2 Users List
Subject: Re: [U2] Selection Suggestions for using UV instead of SQL
George:
How about something like:
3 Dev (0)- LSELECT ?
This will create a list of {FileName} keys for records whose
field definition value appears in a defined list.
Syntax:
LSELECT File_Name WITH Dict_Name IN LIST1 TO LIST2 [VERBOSE| -V]
Example: SELECT CUSTOMERS SAMPLE 500
SAVE.LIST MY.LIST
LSELECT SALES.ORDERS WITH CUST.NBR IN MY.LIST TO MY.LIST.2
Notes:
If a destination list is not supplied then keys will be left as the active
select list. If a source list is not supplied then the current active
select
list will be used. Lists may be specified as select buffers (0-8) or as
named
lists from SAVEDLISTS.
An example of this is as follows:
3 Dev (0)- SELECT MASTER 260605
2 records selected to list 0.
3 Dev (0)- SAVE-LIST MY.LIST
2 key(s) saved to 1 record(s).
3 Dev (0)- LSELECT ARTMASTER WITH CLIENTNO IN MY.LIST VERBOSE
(Parsed Command)
LSELECT ARTMASTER WITH CLIENTNO IN MY.LIST TO 0
(Selected List)
List Source MY.LIST found 2 items.
(Create Temp File)
LSELECT0031 file created with MOD = 5
(Loaded List to Temp File) - 2 items.
977 key(s) saved to 1 record(s).
(Selected Result List)
UDT.COMMAND = select ARTMASTER WITH EVAL
TRANS('LSELECT0031',CLIENTNO,'@ID','X') GT TO 0
UDT.RESPONSE = ?977 records selected to list 0.??
Destination 0
Named List 0
(Delete Temp File)
LSELECT0031 file deleted...
3 Dev (0)
This now has a list of (/*ARTMASTER*/) records that contain a client# in
the original select list, MY.LIST.
HTH,
Bill
Untitled Page
___
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] Selection Suggestions for using UV instead of SQL
You can find it here:
https://u2devzone.rocketsoftware.com/accelerate/articles/u2-select/u2-select
Cheers,
Dan McGrath
Product Manager
Rocket Software
4600 S. Ulster Street **Suite 1100 **Denver, CO 80237 * USA
t: +1.720.475.8098 * m: +1.617.630.7392 * e:dmcgr...@rocketsoftware.com w:
rocketsoftware.com/u2
-Original Message-
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Robert
Sent: Thursday, October 18, 2012 10:26 AM
To: U2 Users List
Subject: Re: [U2] Selection Suggestions for using UV instead of SQL
There used to be a version of a SELECT verb on the Rocket website. I think it
was in the white papers or technical articles section. It provided source code
to a verb called SELECTK I think it was called.
If anyone can find it, let me know. I haven't been able to find it after the
first time I read it.
Robert Norman
On 10/17/2012 10:20 AM, George Gallen wrote:
Looks like either UV doesn't have LSELECT, or UV 10.0.2 does not.
George
-Original Message-
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Bill
Haskett
Sent: Wednesday, October 17, 2012 1:16 PM
To: U2 Users List
Subject: Re: [U2] Selection Suggestions for using UV instead of SQL
George:
How about something like:
3 Dev (0)- LSELECT ?
This will create a list of {FileName} keys for records whose field
definition value appears in a defined list.
Syntax:
LSELECT File_Name WITH Dict_Name IN LIST1 TO LIST2 [VERBOSE| -V]
Example: SELECT CUSTOMERS SAMPLE 500
SAVE.LIST MY.LIST
LSELECT SALES.ORDERS WITH CUST.NBR IN MY.LIST TO MY.LIST.2
Notes:
If a destination list is not supplied then keys will be left as the
active select list. If a source list is not supplied then the current
active select list will be used. Lists may be specified as select
buffers (0-8) or as named lists from SAVEDLISTS.
An example of this is as follows:
3 Dev (0)- SELECT MASTER 260605
2 records selected to list 0.
3 Dev (0)- SAVE-LIST MY.LIST
2 key(s) saved to 1 record(s).
3 Dev (0)- LSELECT ARTMASTER WITH CLIENTNO IN MY.LIST VERBOSE (Parsed
Command) LSELECT ARTMASTER WITH CLIENTNO IN MY.LIST TO 0
(Selected List)
List Source MY.LIST found 2 items.
(Create Temp File)
LSELECT0031 file created with MOD = 5
(Loaded List to Temp File) - 2 items.
977 key(s) saved to 1 record(s).
(Selected Result List)
UDT.COMMAND = select ARTMASTER WITH EVAL
TRANS('LSELECT0031',CLIENTNO,'@ID','X') GT TO 0 UDT.RESPONSE =
?977 records selected to list 0.??
Destination 0
Named List 0
(Delete Temp File)
LSELECT0031 file deleted...
3 Dev (0)
This now has a list of (/*ARTMASTER*/) records that contain a client#
in the original select list, MY.LIST.
HTH,
Bill
Untitled Page
___
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
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Selection Suggestions for using UV instead of SQL
What is the purpose of using a $ in one of your variable names? -Original Message- From: Bill Haskett wphask...@advantos.net To: U2 Users List u2-users@listserver.u2ug.org Sent: Thu, Oct 18, 2012 9:24 am Subject: Re: [U2] Selection Suggestions for using UV instead of SQL George: I wrote a TREE.CHECK program that does the following: 2 Dev (0)- TREE.CHECK MASTER -IINDEX_2 Index Check: MASTER, INDEX_1 ?Key... Indexed Value. ?350 1432 1434 G ST CONDO ASSOC ?50 ABBOTT WOOD TOWNHOMES ASSOC ?70 ASPEN HEIGHT CONDO OWNERS ASSC ?80 AUTUMN RIDGE HOMEOWNERS' ASSOC ?90 BONNIE CUSACK ESTATES ASSOC ?110 BOSTON SQUARE CONDOMINIUMS ?115 BRIARCLIFF TOWNHOMES ASSOC ?130 BROOKSHIRE CONDOMINIUM ASSOC ?120 BROOKVIEW CONDOMINIUM ASSOC ?135 CAMPBELL CREEK DUPLEX OWNERS ?140 CAPE HATTERAS AT COMMODORE PRK ?170 CEDARCREST CONDO ASSOC ?175 CHANDELLE ACRES P/O ASSOC ?215 CHUGACH MEADOWS II CONDO ASSOC ?160 CHUGACH MEADOWS TOWNHOME ASSOC ?150 CHUGIAK HANGARS ASSOCIATION ?185 CLEAR CREEK CONDOMINIUM ASSOC ?190 CONCORD HILL HOMEOWNER ASSOC ?180 CREEKSIDE TWNHOMES CONDO ASSOC ?200 CREEKVIEW CONDO ASSOC ??? [N]ext Pg, [P]rev Pg, [F]irst Pg, [L]ast Pg, [A]ccept, [S]top ? I wonder if this would help? Bill By the way, I did post the LSELECT code to pickwiki, under UniQuerySource. - Original Message - *From:* ggal...@wyanokegroup.com *To:* U2 Users List u2-users@listserver.u2ug.org *Date:* 10/18/2012 6:15 AM *Subject:* Re: [U2] Selection Suggestions for using UV instead of SQL I'll have to play with this later - thanks for the info... George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Brian Leach Sent: Thursday, October 18, 2012 6:50 AM To: 'U2 Users List' Subject: Re: [U2] Selection Suggestions for using UV instead of SQL Since the index files are actually type 25 files, you can always (though of course I would NEVER recommend this ... ) create a file pointer direct to the INDEX.nnn and read the entries as records ... saves grabbing a select list .. Brian -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: 18 October 2012 09:53 To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL On 18/10/12 09:29, Martin Braid wrote: I think you mean SELECTINDEX index.name[,key.val] FROM file.var [TO list.num] Martin Followed by a READLIST ... Quite possibly - I can't remember. One *important* point to note - if you are planning to call this from an i-descriptor, as I did, don't forget you MUST save that to a non-default list, or any attempt to LIST the idescriptor will go horribly wrong... Cheers, Wol -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: 18 October 2012 08:55 To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL On 17/10/12 16:40, George Gallen wrote: How would I get the contents of the Index? George Don't have the UV BASIC manual in front of me, but it's something like READINDEX variable FROM filedescriptor, indexname[, indexvalue] If you only pass filedescriptor and indexname, it will return a list of all the index values. If you also pass indexvalue, it will return a list of all keys for that value. I'll have a look later and see if I can find that routine - it's probably on my system somewhere, but my archives aren't that organised... Cheers, Wol -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: Wednesday, October 17, 2012 11:38 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL On 17/10/12 14:59, George Gallen wrote: Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could join the two, then do the select or use a subquery (I'm on UV 10.0.2 - not sure if sub querys work) Just curious how this could be done with UV Native, and without involving a program. I first tried SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID - NSELECT PEOPLE But that returned 0 - since the NSELECT only works off the current select list, not the whole file Yes, I could
Re: [U2] Selection Suggestions for using UV instead of SQL
I use it for EQUATE names to remind me that I can't change it when I'm waist deep in a program. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wjhonson Sent: Thursday, October 18, 2012 9:40 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL What is the purpose of using a $ in one of your variable names? -Original Message- From: Bill Haskett wphask...@advantos.net To: U2 Users List u2-users@listserver.u2ug.org Sent: Thu, Oct 18, 2012 9:24 am Subject: Re: [U2] Selection Suggestions for using UV instead of SQL George: I wrote a TREE.CHECK program that does the following: 2 Dev (0)- TREE.CHECK MASTER -IINDEX_2 Index Check: MASTER, INDEX_1 ?Key... Indexed Value. ?350 1432 1434 G ST CONDO ASSOC ?50 ABBOTT WOOD TOWNHOMES ASSOC ?70 ASPEN HEIGHT CONDO OWNERS ASSC ?80 AUTUMN RIDGE HOMEOWNERS' ASSOC ?90 BONNIE CUSACK ESTATES ASSOC ?110 BOSTON SQUARE CONDOMINIUMS ?115 BRIARCLIFF TOWNHOMES ASSOC ?130 BROOKSHIRE CONDOMINIUM ASSOC ?120 BROOKVIEW CONDOMINIUM ASSOC ?135 CAMPBELL CREEK DUPLEX OWNERS ?140 CAPE HATTERAS AT COMMODORE PRK ?170 CEDARCREST CONDO ASSOC ?175 CHANDELLE ACRES P/O ASSOC ?215 CHUGACH MEADOWS II CONDO ASSOC ?160 CHUGACH MEADOWS TOWNHOME ASSOC ?150 CHUGIAK HANGARS ASSOCIATION ?185 CLEAR CREEK CONDOMINIUM ASSOC ?190 CONCORD HILL HOMEOWNER ASSOC ?180 CREEKSIDE TWNHOMES CONDO ASSOC ?200 CREEKVIEW CONDO ASSOC ??? [N]ext Pg, [P]rev Pg, [F]irst Pg, [L]ast Pg, [A]ccept, [S]top ? I wonder if this would help? Bill By the way, I did post the LSELECT code to pickwiki, under UniQuerySource. - Original Message - *From:* ggal...@wyanokegroup.com *To:* U2 Users List u2-users@listserver.u2ug.org *Date:* 10/18/2012 6:15 AM *Subject:* Re: [U2] Selection Suggestions for using UV instead of SQL I'll have to play with this later - thanks for the info... George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Brian Leach Sent: Thursday, October 18, 2012 6:50 AM To: 'U2 Users List' Subject: Re: [U2] Selection Suggestions for using UV instead of SQL Since the index files are actually type 25 files, you can always (though of course I would NEVER recommend this ... ) create a file pointer direct to the INDEX.nnn and read the entries as records ... saves grabbing a select list .. Brian -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: 18 October 2012 09:53 To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL On 18/10/12 09:29, Martin Braid wrote: I think you mean SELECTINDEX index.name[,key.val] FROM file.var [TO list.num] Martin Followed by a READLIST ... Quite possibly - I can't remember. One *important* point to note - if you are planning to call this from an i-descriptor, as I did, don't forget you MUST save that to a non-default list, or any attempt to LIST the idescriptor will go horribly wrong... Cheers, Wol -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: 18 October 2012 08:55 To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL On 17/10/12 16:40, George Gallen wrote: How would I get the contents of the Index? George Don't have the UV BASIC manual in front of me, but it's something like READINDEX variable FROM filedescriptor, indexname[, indexvalue] If you only pass filedescriptor and indexname, it will return a list of all the index values. If you also pass indexvalue, it will return a list of all keys for that value. I'll have a look later and see if I can find that routine - it's probably on my system somewhere, but my archives aren't that organised... Cheers, Wol -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: Wednesday, October 17, 2012 11:38 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL On 17/10/12 14:59, George Gallen wrote: Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could
Re: [U2] Selection Suggestions for using UV instead of SQL
Sort of a carry-over from VB when a string was given a variable name with a '$' in it, usually at the end, or the beginning. Habits die hard. :-) Bill - Original Message - *From:* wjhon...@aol.com *To:* u2-users@listserver.u2ug.org *Date:* 10/18/2012 9:39 AM *Subject:* Re: [U2] Selection Suggestions for using UV instead of SQL What is the purpose of using a $ in one of your variable names? -Original Message- From: Bill Haskett wphask...@advantos.net To: U2 Users List u2-users@listserver.u2ug.org Sent: Thu, Oct 18, 2012 9:24 am Subject: Re: [U2] Selection Suggestions for using UV instead of SQL George: I wrote a TREE.CHECK program that does the following: 2 Dev (0)- TREE.CHECK MASTER -IINDEX_2 Index Check: MASTER, INDEX_1 ?Key... Indexed Value. ?350 1432 1434 G ST CONDO ASSOC ?50 ABBOTT WOOD TOWNHOMES ASSOC ?70 ASPEN HEIGHT CONDO OWNERS ASSC ?80 AUTUMN RIDGE HOMEOWNERS' ASSOC ?90 BONNIE CUSACK ESTATES ASSOC ?110 BOSTON SQUARE CONDOMINIUMS ?115 BRIARCLIFF TOWNHOMES ASSOC ?130 BROOKSHIRE CONDOMINIUM ASSOC ?120 BROOKVIEW CONDOMINIUM ASSOC ?135 CAMPBELL CREEK DUPLEX OWNERS ?140 CAPE HATTERAS AT COMMODORE PRK ?170 CEDARCREST CONDO ASSOC ?175 CHANDELLE ACRES P/O ASSOC ?215 CHUGACH MEADOWS II CONDO ASSOC ?160 CHUGACH MEADOWS TOWNHOME ASSOC ?150 CHUGIAK HANGARS ASSOCIATION ?185 CLEAR CREEK CONDOMINIUM ASSOC ?190 CONCORD HILL HOMEOWNER ASSOC ?180 CREEKSIDE TWNHOMES CONDO ASSOC ?200 CREEKVIEW CONDO ASSOC ??? [N]ext Pg, [P]rev Pg, [F]irst Pg, [L]ast Pg, [A]ccept, [S]top ? I wonder if this would help? Bill By the way, I did post the LSELECT code to pickwiki, under UniQuerySource. - Original Message - *From:* ggal...@wyanokegroup.com *To:* U2 Users List u2-users@listserver.u2ug.org *Date:* 10/18/2012 6:15 AM *Subject:* Re: [U2] Selection Suggestions for using UV instead of SQL I'll have to play with this later - thanks for the info... George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Brian Leach Sent: Thursday, October 18, 2012 6:50 AM To: 'U2 Users List' Subject: Re: [U2] Selection Suggestions for using UV instead of SQL Since the index files are actually type 25 files, you can always (though of course I would NEVER recommend this ... ) create a file pointer direct to the INDEX.nnn and read the entries as records ... saves grabbing a select list .. Brian -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: 18 October 2012 09:53 To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL On 18/10/12 09:29, Martin Braid wrote: I think you mean SELECTINDEX index.name[,key.val] FROM file.var [TO list.num] Martin Followed by a READLIST ... Quite possibly - I can't remember. One *important* point to note - if you are planning to call this from an i-descriptor, as I did, don't forget you MUST save that to a non-default list, or any attempt to LIST the idescriptor will go horribly wrong... Cheers, Wol -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: 18 October 2012 08:55 To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL On 17/10/12 16:40, George Gallen wrote: How would I get the contents of the Index? George Don't have the UV BASIC manual in front of me, but it's something like READINDEX variable FROM filedescriptor, indexname[, indexvalue] If you only pass filedescriptor and indexname, it will return a list of all the index values. If you also pass indexvalue, it will return a list of all keys for that value. I'll have a look later and see if I can find that routine - it's probably on my system somewhere, but my archives aren't that organised... Cheers, Wol -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: Wednesday, October 17, 2012 11:38 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL On 17/10/12 14:59, George Gallen wrote: Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could join the two, then do
Re: [U2] Selection Suggestions for using UV instead of SQL
In my first programming class in high school (on apple IIe's) we liked to use G$ (with the $ pronounced as string) Colin -Original Message- From: Bill Haskett Sent: October 18, 2012 12:24 PM To: U2 Users List Subject: Re: [U2] Selection Suggestions for using UV instead of SQL Sort of a carry-over from VB when a string was given a variable name with a '$' in it, usually at the end, or the beginning. Habits die hard. :-) Bill ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Selection Suggestions for using UV instead of SQL
Speaking of the Apple II AppleWorks was an excellent example of the days when changing the length of a field required the entire database to be picked up and layed back down again. And if you didn't have enough room to do that, you had to write it all to 35 floppy disks and then reload them. -Original Message- From: Colin Alfke alfke...@hotmail.com To: 'U2 Users List' u2-users@listserver.u2ug.org Sent: Thu, Oct 18, 2012 11:53 am Subject: Re: [U2] Selection Suggestions for using UV instead of SQL In my first programming class in high school (on apple IIe's) we liked to use G$ (with the $ pronounced as string) Colin -Original Message- From: Bill Haskett Sent: October 18, 2012 12:24 PM To: U2 Users List Subject: Re: [U2] Selection Suggestions for using UV instead of SQL Sort of a carry-over from VB when a string was given a variable name with a '$' in it, usually at the end, or the beginning. Habits die hard. :-) Bill ___ 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] Selection Suggestions for using UV instead of SQL
And we wonder where GIRLS came from??!? George Gallen Senior Programmer/Analyst Accounting/Data Division ggal...@wyanokegroup.com ph:856.848.9005 Ext 220 The Wyanoke Group http://www.wyanokegroup.com From: u2-users-boun...@listserver.u2ug.org [u2-users-boun...@listserver.u2ug.org] On Behalf Of Colin Alfke [alfke...@hotmail.com] Sent: Thursday, October 18, 2012 2:53 PM To: 'U2 Users List' Subject: Re: [U2] Selection Suggestions for using UV instead of SQL In my first programming class in high school (on apple IIe's) we liked to use G$ (with the $ pronounced as string) Colin -Original Message- From: Bill Haskett Sent: October 18, 2012 12:24 PM To: U2 Users List Subject: Re: [U2] Selection Suggestions for using UV instead of SQL Sort of a carry-over from VB when a string was given a variable name with a '$' in it, usually at the end, or the beginning. Habits die hard. :-) Bill ___ 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
[U2] Selection Suggestions for using UV instead of SQL
Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could join the two, then do the select or use a subquery (I'm on UV 10.0.2 - not sure if sub querys work) Just curious how this could be done with UV Native, and without involving a program. I first tried SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID - NSELECT PEOPLE But that returned 0 - since the NSELECT only works off the current select list, not the whole file Yes, I could create a temporary file, then select off that Yes, I could read each people record, then loop through registration to see if it doesn't exist. Both of the above require programming I guess I could create a subroutine that does the looping through registration, and put that into an I descriptor - looking for another option. George ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Selection Suggestions for using UV instead of SQL
Do it in more than one command? SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID NSELECT PEOPLE Or am I missing something? John -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Wednesday, October 17, 2012 9:59 AM To: U2 Users Subject: [U2] Selection Suggestions for using UV instead of SQL Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could join the two, then do the select or use a subquery (I'm on UV 10.0.2 - not sure if sub querys work) Just curious how this could be done with UV Native, and without involving a program. I first tried SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID - NSELECT PEOPLE But that returned 0 - since the NSELECT only works off the current select list, not the whole file Yes, I could create a temporary file, then select off that Yes, I could read each people record, then loop through registration to see if it doesn't exist. Both of the above require programming I guess I could create a subroutine that does the looping through registration, and put that into an I descriptor - looking for another option. George ___ 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] Selection Suggestions for using UV instead of SQL
That was my first try, but the NSELECT only checks PEOPLE for those that are in the list returned by the first That are not in PEOPLE, it doesn't check for those in PEOPLE that are not in the list returned. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: Wednesday, October 17, 2012 10:05 AM To: 'U2 Users List' Subject: Re: [U2] Selection Suggestions for using UV instead of SQL Do it in more than one command? SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID NSELECT PEOPLE Or am I missing something? John -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Wednesday, October 17, 2012 9:59 AM To: U2 Users Subject: [U2] Selection Suggestions for using UV instead of SQL Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could join the two, then do the select or use a subquery (I'm on UV 10.0.2 - not sure if sub querys work) Just curious how this could be done with UV Native, and without involving a program. I first tried SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID - NSELECT PEOPLE But that returned 0 - since the NSELECT only works off the current select list, not the whole file Yes, I could create a temporary file, then select off that Yes, I could read each people record, then loop through registration to see if it doesn't exist. Both of the above require programming I guess I could create a subroutine that does the looping through registration, and put that into an I descriptor - looking for another option. George ___ 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 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Selection Suggestions for using UV instead of SQL
I'm OK with using SQL - just wondering how you could use UV native. select distinct PEOPLE.PID from PEOPLE inner join REGISTRATION on (PEOPLE.PID = REGISTRATION.PID) where EID '1'; I will admit, UV could use a join clause. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Wednesday, October 17, 2012 10:20 AM To: U2 Users List Subject: Re: [U2] Selection Suggestions for using UV instead of SQL That was my first try, but the NSELECT only checks PEOPLE for those that are in the list returned by the first That are not in PEOPLE, it doesn't check for those in PEOPLE that are not in the list returned. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: Wednesday, October 17, 2012 10:05 AM To: 'U2 Users List' Subject: Re: [U2] Selection Suggestions for using UV instead of SQL Do it in more than one command? SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID NSELECT PEOPLE Or am I missing something? John -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Wednesday, October 17, 2012 9:59 AM To: U2 Users Subject: [U2] Selection Suggestions for using UV instead of SQL Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could join the two, then do the select or use a subquery (I'm on UV 10.0.2 - not sure if sub querys work) Just curious how this could be done with UV Native, and without involving a program. I first tried SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID - NSELECT PEOPLE But that returned 0 - since the NSELECT only works off the current select list, not the whole file Yes, I could create a temporary file, then select off that Yes, I could read each people record, then loop through registration to see if it doesn't exist. Both of the above require programming I guess I could create a subroutine that does the looping through registration, and put that into an I descriptor - looking for another option. George ___ 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 ___ 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] Selection Suggestions for using UV instead of SQL
This gets a little trickier, but how about: 1) SELECT PEOPLE TO 1. 2) SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID. 3) SELECT PEOPLE TO 2. 4) MERGE.LIST 1 DIFF 2 TO 3. 5) SAVE.LIST XXX FROM 3. This does assume that steps 1 and 2 actually return at least 1 key. MERGE.LIST is an amazingly powerful command. It only works with the native UniData (UniVerse?) syntax as do the specific list #s, but it is easy enough to use the lower case verbs to force the individual commands to run in native mode. John -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Wednesday, October 17, 2012 10:20 AM To: U2 Users List Subject: Re: [U2] Selection Suggestions for using UV instead of SQL That was my first try, but the NSELECT only checks PEOPLE for those that are in the list returned by the first That are not in PEOPLE, it doesn't check for those in PEOPLE that are not in the list returned. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: Wednesday, October 17, 2012 10:05 AM To: 'U2 Users List' Subject: Re: [U2] Selection Suggestions for using UV instead of SQL Do it in more than one command? SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID NSELECT PEOPLE Or am I missing something? John -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Wednesday, October 17, 2012 9:59 AM To: U2 Users Subject: [U2] Selection Suggestions for using UV instead of SQL Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could join the two, then do the select or use a subquery (I'm on UV 10.0.2 - not sure if sub querys work) Just curious how this could be done with UV Native, and without involving a program. I first tried SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID - NSELECT PEOPLE But that returned 0 - since the NSELECT only works off the current select list, not the whole file Yes, I could create a temporary file, then select off that Yes, I could read each people record, then loop through registration to see if it doesn't exist. Both of the above require programming I guess I could create a subroutine that does the looping through registration, and put that into an I descriptor - looking for another option. George ___ 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 ___ 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] Selection Suggestions for using UV instead of SQL
Yea, MERGE.LIST does work, but sometimes I have issues with it using it in an EXECUTE from BASIC. George Actually, you could leave off the TO 3, and the FROM 3 and it would still work. The only disadvantage, is MERGE.LIST ALWAYS does a distinct, and you can't use it to merge on More than one field, whereas in a JOIN you can. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: Wednesday, October 17, 2012 10:29 AM To: 'U2 Users List' Subject: Re: [U2] Selection Suggestions for using UV instead of SQL This gets a little trickier, but how about: 1) SELECT PEOPLE TO 1. 2) SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID. 3) SELECT PEOPLE TO 2. 4) MERGE.LIST 1 DIFF 2 TO 3. 5) SAVE.LIST XXX FROM 3. This does assume that steps 1 and 2 actually return at least 1 key. MERGE.LIST is an amazingly powerful command. It only works with the native UniData (UniVerse?) syntax as do the specific list #s, but it is easy enough to use the lower case verbs to force the individual commands to run in native mode. John -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Wednesday, October 17, 2012 10:20 AM To: U2 Users List Subject: Re: [U2] Selection Suggestions for using UV instead of SQL That was my first try, but the NSELECT only checks PEOPLE for those that are in the list returned by the first That are not in PEOPLE, it doesn't check for those in PEOPLE that are not in the list returned. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: Wednesday, October 17, 2012 10:05 AM To: 'U2 Users List' Subject: Re: [U2] Selection Suggestions for using UV instead of SQL Do it in more than one command? SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID NSELECT PEOPLE Or am I missing something? John -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Wednesday, October 17, 2012 9:59 AM To: U2 Users Subject: [U2] Selection Suggestions for using UV instead of SQL Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could join the two, then do the select or use a subquery (I'm on UV 10.0.2 - not sure if sub querys work) Just curious how this could be done with UV Native, and without involving a program. I first tried SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID - NSELECT PEOPLE But that returned 0 - since the NSELECT only works off the current select list, not the whole file Yes, I could create a temporary file, then select off that Yes, I could read each people record, then loop through registration to see if it doesn't exist. Both of the above require programming I guess I could create a subroutine that does the looping through registration, and put that into an I descriptor - looking for another option. George ___ 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 ___ 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 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Selection Suggestions for using UV instead of SQL
On 17/10/12 14:59, George Gallen wrote: Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could join the two, then do the select or use a subquery (I'm on UV 10.0.2 - not sure if sub querys work) Just curious how this could be done with UV Native, and without involving a program. I first tried SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID - NSELECT PEOPLE But that returned 0 - since the NSELECT only works off the current select list, not the whole file Yes, I could create a temporary file, then select off that Yes, I could read each people record, then loop through registration to see if it doesn't exist. Both of the above require programming I guess I could create a subroutine that does the looping through registration, and put that into an I descriptor - looking for another option. Hmmm... What I guess I'd do - create an index on file2 based on PID. Create an idescriptor on file1 that gets the contents of that index. I had a routine somewhere that got fed an index value and file, and returned the contents of the index. It was quite useful, iirc. Manipulate it from there. Probably selecting where there is no value matching ...+1+... Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Selection Suggestions for using UV instead of SQL
How would I get the contents of the Index? George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: Wednesday, October 17, 2012 11:38 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Selection Suggestions for using UV instead of SQL On 17/10/12 14:59, George Gallen wrote: Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could join the two, then do the select or use a subquery (I'm on UV 10.0.2 - not sure if sub querys work) Just curious how this could be done with UV Native, and without involving a program. I first tried SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID - NSELECT PEOPLE But that returned 0 - since the NSELECT only works off the current select list, not the whole file Yes, I could create a temporary file, then select off that Yes, I could read each people record, then loop through registration to see if it doesn't exist. Both of the above require programming I guess I could create a subroutine that does the looping through registration, and put that into an I descriptor - looking for another option. Hmmm... What I guess I'd do - create an index on file2 based on PID. Create an idescriptor on file1 that gets the contents of that index. I had a routine somewhere that got fed an index value and file, and returned the contents of the index. It was quite useful, iirc. Manipulate it from there. Probably selecting where there is no value matching ...+1+... Cheers, Wol ___ 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] Selection Suggestions for using UV instead of SQL
Meant to have left outer join, not inner join. BUT.I hit a snag. If someone has entries in REGISTRATION with EID = '1' and an EID = '2' - they still get selected, since the it passes The '1' test, but the outer join also pulls in the EID = '2'. So I'm going to have To do a loop through, or maybe the index method may work as well. Damn -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Wednesday, October 17, 2012 10:28 AM To: U2 Users List Subject: Re: [U2] Selection Suggestions for using UV instead of SQL I'm OK with using SQL - just wondering how you could use UV native. select distinct PEOPLE.PID from PEOPLE inner join REGISTRATION on (PEOPLE.PID = REGISTRATION.PID) where EID '1'; I will admit, UV could use a join clause. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Wednesday, October 17, 2012 10:20 AM To: U2 Users List Subject: Re: [U2] Selection Suggestions for using UV instead of SQL That was my first try, but the NSELECT only checks PEOPLE for those that are in the list returned by the first That are not in PEOPLE, it doesn't check for those in PEOPLE that are not in the list returned. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: Wednesday, October 17, 2012 10:05 AM To: 'U2 Users List' Subject: Re: [U2] Selection Suggestions for using UV instead of SQL Do it in more than one command? SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID NSELECT PEOPLE Or am I missing something? John -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Wednesday, October 17, 2012 9:59 AM To: U2 Users Subject: [U2] Selection Suggestions for using UV instead of SQL Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could join the two, then do the select or use a subquery (I'm on UV 10.0.2 - not sure if sub querys work) Just curious how this could be done with UV Native, and without involving a program. I first tried SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID - NSELECT PEOPLE But that returned 0 - since the NSELECT only works off the current select list, not the whole file Yes, I could create a temporary file, then select off that Yes, I could read each people record, then loop through registration to see if it doesn't exist. Both of the above require programming I guess I could create a subroutine that does the looping through registration, and put that into an I descriptor - looking for another option. George ___ 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 ___ 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 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Selection Suggestions for using UV instead of SQL
It looks like you have hit the point where code is needed because the SELECT just cannot handle all the conditions cleanly. My rule of thumb is: if I am going to need ANY code, do the whole thing in a program. Experience has taught me that at some point, someone will want to tweak things, and if it is already in a program, I will not migrating my Paragraphs (or whatever) into a program and them abandoning the paragraph. This may mean that I have REALLY simple program initially, but all too often, they grow over time. It also limits the number of places you need to look for that piece of code that is doing something. Just my 4 cents (2 cents w/ inflation) John -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Wednesday, October 17, 2012 12:02 PM To: U2 Users List Subject: Re: [U2] Selection Suggestions for using UV instead of SQL Meant to have left outer join, not inner join. BUT.I hit a snag. If someone has entries in REGISTRATION with EID = '1' and an EID = '2' - they still get selected, since the it passes The '1' test, but the outer join also pulls in the EID = '2'. So I'm going to have To do a loop through, or maybe the index method may work as well. Damn -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Wednesday, October 17, 2012 10:28 AM To: U2 Users List Subject: Re: [U2] Selection Suggestions for using UV instead of SQL I'm OK with using SQL - just wondering how you could use UV native. select distinct PEOPLE.PID from PEOPLE inner join REGISTRATION on (PEOPLE.PID = REGISTRATION.PID) where EID '1'; I will admit, UV could use a join clause. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Wednesday, October 17, 2012 10:20 AM To: U2 Users List Subject: Re: [U2] Selection Suggestions for using UV instead of SQL That was my first try, but the NSELECT only checks PEOPLE for those that are in the list returned by the first That are not in PEOPLE, it doesn't check for those in PEOPLE that are not in the list returned. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: Wednesday, October 17, 2012 10:05 AM To: 'U2 Users List' Subject: Re: [U2] Selection Suggestions for using UV instead of SQL Do it in more than one command? SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID NSELECT PEOPLE Or am I missing something? John -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Wednesday, October 17, 2012 9:59 AM To: U2 Users Subject: [U2] Selection Suggestions for using UV instead of SQL Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could join the two, then do the select or use a subquery (I'm on UV 10.0.2 - not sure if sub querys work) Just curious how this could be done with UV Native, and without involving a program. I first tried SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID - NSELECT PEOPLE But that returned 0 - since the NSELECT only works off the current select list, not the whole file Yes, I could create a temporary file, then select off that Yes, I could read each people record, then loop through registration to see if it doesn't exist. Both of the above require programming I guess I could create a subroutine that does the looping through registration, and put that into an I descriptor - looking for another option. George ___ 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 ___ 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 ___ 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] Selection Suggestions for using UV instead of SQL
George:
How about something like:
3 Dev (0)- LSELECT ?
This will create a list of {FileName} keys for records whose
field definition value appears in a defined list.
Syntax:
LSELECT File_Name WITH Dict_Name IN LIST1 TO LIST2 [VERBOSE| -V]
Example: SELECT CUSTOMERS SAMPLE 500
SAVE.LIST MY.LIST
LSELECT SALES.ORDERS WITH CUST.NBR IN MY.LIST TO MY.LIST.2
Notes:
If a destination list is not supplied then keys will be left as the active
select list. If a source list is not supplied then the current active
select
list will be used. Lists may be specified as select buffers (0-8) or as
named
lists from SAVEDLISTS.
An example of this is as follows:
3 Dev (0)- SELECT MASTER 260605
2 records selected to list 0.
3 Dev (0)- SAVE-LIST MY.LIST
2 key(s) saved to 1 record(s).
3 Dev (0)- LSELECT ARTMASTER WITH CLIENTNO IN MY.LIST VERBOSE
(Parsed Command)
LSELECT ARTMASTER WITH CLIENTNO IN MY.LIST TO 0
(Selected List)
List Source MY.LIST found 2 items.
(Create Temp File)
LSELECT0031 file created with MOD = 5
(Loaded List to Temp File) - 2 items.
977 key(s) saved to 1 record(s).
(Selected Result List)
UDT.COMMAND = select ARTMASTER WITH EVAL
TRANS('LSELECT0031',CLIENTNO,'@ID','X') GT TO 0
UDT.RESPONSE = ?977 records selected to list 0.??
Destination 0
Named List 0
(Delete Temp File)
LSELECT0031 file deleted...
3 Dev (0)
This now has a list of (/*ARTMASTER*/) records that contain a client# in
the original select list, MY.LIST.
HTH,
Bill
Untitled Page
- Original Message -
*From:* ggal...@wyanokegroup.com
*To:* U2 Users List u2-users@listserver.u2ug.org
*Date:* 10/17/2012 7:32 AM
*Subject:* Re: [U2] Selection Suggestions for using UV instead of SQL
Yea, MERGE.LIST does work, but sometimes I have issues with it using it in an
EXECUTE from BASIC.
George
Actually, you could leave off the TO 3, and the FROM 3 and it would still work.
The only disadvantage, is MERGE.LIST ALWAYS does a distinct, and you can't use
it to merge on
More than one field, whereas in a JOIN you can.
George
-Original Message-
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R.
Sent: Wednesday, October 17, 2012 10:29 AM
To: 'U2 Users List'
Subject: Re: [U2] Selection Suggestions for using UV instead of SQL
This gets a little trickier, but how about:
1) SELECT PEOPLE TO 1.
2) SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID.
3) SELECT PEOPLE TO 2.
4) MERGE.LIST 1 DIFF 2 TO 3.
5) SAVE.LIST XXX FROM 3.
This does assume that steps 1 and 2 actually return at least 1 key.
MERGE.LIST is an amazingly powerful command. It only works with the native
UniData (UniVerse?) syntax as do the specific list #s, but it is easy enough to
use the lower case verbs to force the individual commands to run in native mode.
John
-Original Message-
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen
Sent: Wednesday, October 17, 2012 10:20 AM
To: U2 Users List
Subject: Re: [U2] Selection Suggestions for using UV instead of SQL
That was my first try, but the NSELECT only checks PEOPLE for those that are in
the list returned by the first That are not in PEOPLE, it doesn't check for
those in PEOPLE that are not in the list returned.
George
-Original Message-
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R.
Sent: Wednesday, October 17, 2012 10:05 AM
To: 'U2 Users List'
Subject: Re: [U2] Selection Suggestions for using UV instead of SQL
Do it in more than one command?
SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID NSELECT PEOPLE
Or am I missing something?
John
-Original Message-
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen
Sent: Wednesday, October 17, 2012 9:59 AM
To: U2 Users
Subject: [U2] Selection Suggestions for using UV instead of SQL
Ok.
I have file1 - people that has @ID of PID I have file2 - registration that
has @ID of PID+EID+RID (EID = event id, RID = registration id)
I want to select people who are not in the registration file with an EID of 1.
With an SQL statement - I could join the two, then do the select or use a
subquery (I'm on UV 10.0.2 - not sure if sub querys work) Just curious how this
could be done with UV Native, and without involving a program.
I first tried SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID - NSELECT
PEOPLE
But that returned 0 - since the NSELECT only works off the current select list,
not the whole file
Yes, I could create a temporary file, then select off that Yes, I could read
each people record, then loop through registration to see if it doesn't exist.
Both of the above require programming
I guess I could create a subroutine that does the looping through registration
Re: [U2] Selection Suggestions for using UV instead of SQL
Looks like either UV doesn't have LSELECT, or UV 10.0.2 does not.
George
-Original Message-
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Bill Haskett
Sent: Wednesday, October 17, 2012 1:16 PM
To: U2 Users List
Subject: Re: [U2] Selection Suggestions for using UV instead of SQL
George:
How about something like:
3 Dev (0)- LSELECT ?
This will create a list of {FileName} keys for records whose
field definition value appears in a defined list.
Syntax:
LSELECT File_Name WITH Dict_Name IN LIST1 TO LIST2 [VERBOSE| -V]
Example: SELECT CUSTOMERS SAMPLE 500
SAVE.LIST MY.LIST
LSELECT SALES.ORDERS WITH CUST.NBR IN MY.LIST TO MY.LIST.2
Notes:
If a destination list is not supplied then keys will be left as the active
select list. If a source list is not supplied then the current active
select
list will be used. Lists may be specified as select buffers (0-8) or as
named
lists from SAVEDLISTS.
An example of this is as follows:
3 Dev (0)- SELECT MASTER 260605
2 records selected to list 0.
3 Dev (0)- SAVE-LIST MY.LIST
2 key(s) saved to 1 record(s).
3 Dev (0)- LSELECT ARTMASTER WITH CLIENTNO IN MY.LIST VERBOSE
(Parsed Command)
LSELECT ARTMASTER WITH CLIENTNO IN MY.LIST TO 0
(Selected List)
List Source MY.LIST found 2 items.
(Create Temp File)
LSELECT0031 file created with MOD = 5
(Loaded List to Temp File) - 2 items.
977 key(s) saved to 1 record(s).
(Selected Result List)
UDT.COMMAND = select ARTMASTER WITH EVAL
TRANS('LSELECT0031',CLIENTNO,'@ID','X') GT TO 0
UDT.RESPONSE = ?977 records selected to list 0.??
Destination 0
Named List 0
(Delete Temp File)
LSELECT0031 file deleted...
3 Dev (0)
This now has a list of (/*ARTMASTER*/) records that contain a client# in
the original select list, MY.LIST.
HTH,
Bill
Untitled Page
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Selection Suggestions for using UV instead of SQL
This is actually some code I put together from a Rock tech note. It's
come in handy at various times. :-)
I'd be happy to share the code with you; I use UD but I suspect it
wouldn't be too hard to get it running on UV.
Bill
Untitled Page
- Original Message -
*From:* ggal...@wyanokegroup.com
*To:* U2 Users List u2-users@listserver.u2ug.org
*Date:* 10/17/2012 10:20 AM
*Subject:* Re: [U2] Selection Suggestions for using UV instead of SQL
Looks like either UV doesn't have LSELECT, or UV 10.0.2 does not.
George
-Original Message-
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Bill Haskett
Sent: Wednesday, October 17, 2012 1:16 PM
To: U2 Users List
Subject: Re: [U2] Selection Suggestions for using UV instead of SQL
George:
How about something like:
3 Dev (0)- LSELECT ?
This will create a list of {FileName} keys for records whose
field definition value appears in a defined list.
Syntax:
LSELECT File_Name WITH Dict_Name IN LIST1 TO LIST2 [VERBOSE| -V]
Example: SELECT CUSTOMERS SAMPLE 500
SAVE.LIST MY.LIST
LSELECT SALES.ORDERS WITH CUST.NBR IN MY.LIST TO MY.LIST.2
Notes:
If a destination list is not supplied then keys will be left as the active
select list. If a source list is not supplied then the current active
select
list will be used. Lists may be specified as select buffers (0-8) or as
named
lists from SAVEDLISTS.
An example of this is as follows:
3 Dev (0)- SELECT MASTER 260605
2 records selected to list 0.
3 Dev (0)- SAVE-LIST MY.LIST
2 key(s) saved to 1 record(s).
3 Dev (0)- LSELECT ARTMASTER WITH CLIENTNO IN MY.LIST VERBOSE
(Parsed Command)
LSELECT ARTMASTER WITH CLIENTNO IN MY.LIST TO 0
(Selected List)
List Source MY.LIST found 2 items.
(Create Temp File)
LSELECT0031 file created with MOD = 5
(Loaded List to Temp File) - 2 items.
977 key(s) saved to 1 record(s).
(Selected Result List)
UDT.COMMAND = select ARTMASTER WITH EVAL
TRANS('LSELECT0031',CLIENTNO,'@ID','X') GT TO 0
UDT.RESPONSE = ?977 records selected to list 0.??
Destination 0
Named List 0
(Delete Temp File)
LSELECT0031 file deleted...
3 Dev (0)
This now has a list of (/*ARTMASTER*/) records that contain a client# in
the original select list, MY.LIST.
HTH,
Bill
Untitled Page
___
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] Selection Suggestions for using UV instead of SQL
Why not post it to the PickWiki
That way we can all use it.
-Original Message-
From: Bill Haskett wphask...@advantos.net
To: U2 Users List u2-users@listserver.u2ug.org
Sent: Wed, Oct 17, 2012 10:35 am
Subject: Re: [U2] Selection Suggestions for using UV instead of SQL
This is actually some code I put together from a Rock tech note. It's
come in handy at various times. :-)
I'd be happy to share the code with you; I use UD but I suspect it
wouldn't be too hard to get it running on UV.
Bill
Untitled Page
- Original Message -
*From:* ggal...@wyanokegroup.com
*To:* U2 Users List u2-users@listserver.u2ug.org
*Date:* 10/17/2012 10:20 AM
*Subject:* Re: [U2] Selection Suggestions for using UV instead of SQL
Looks like either UV doesn't have LSELECT, or UV 10.0.2 does not.
George
-Original Message-
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org]
On Behalf Of Bill Haskett
Sent: Wednesday, October 17, 2012 1:16 PM
To: U2 Users List
Subject: Re: [U2] Selection Suggestions for using UV instead of SQL
George:
How about something like:
3 Dev (0)- LSELECT ?
This will create a list of {FileName} keys for records whose
field definition value appears in a defined list.
Syntax:
LSELECT File_Name WITH Dict_Name IN LIST1 TO LIST2 [VERBOSE| -V]
Example: SELECT CUSTOMERS SAMPLE 500
SAVE.LIST MY.LIST
LSELECT SALES.ORDERS WITH CUST.NBR IN MY.LIST TO MY.LIST.2
Notes:
If a destination list is not supplied then keys will be left as the active
select list. If a source list is not supplied then the current active
select
list will be used. Lists may be specified as select buffers (0-8) or as
named
lists from SAVEDLISTS.
An example of this is as follows:
3 Dev (0)- SELECT MASTER 260605
2 records selected to list 0.
3 Dev (0)- SAVE-LIST MY.LIST
2 key(s) saved to 1 record(s).
3 Dev (0)- LSELECT ARTMASTER WITH CLIENTNO IN MY.LIST VERBOSE
(Parsed Command)
LSELECT ARTMASTER WITH CLIENTNO IN MY.LIST TO 0
(Selected List)
List Source MY.LIST found 2 items.
(Create Temp File)
LSELECT0031 file created with MOD = 5
(Loaded List to Temp File) - 2 items.
977 key(s) saved to 1 record(s).
(Selected Result List)
UDT.COMMAND = select ARTMASTER WITH EVAL
TRANS('LSELECT0031',CLIENTNO,'@ID','X') GT TO 0
UDT.RESPONSE = ?977 records selected to list 0.??
Destination 0
Named List 0
(Delete Temp File)
LSELECT0031 file deleted...
3 Dev (0)
This now has a list of (/*ARTMASTER*/) records that contain a client# in
the original select list, MY.LIST.
HTH,
Bill
Untitled Page
___
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
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Selection Suggestions for using UV instead of SQL
select registration with EID = '1' saving unique PID to 1 select people to 2 merge.list 2 diff 1 Larry Hiscock Western Computer Services -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Wednesday, October 17, 2012 6:59 AM To: U2 Users Subject: [U2] Selection Suggestions for using UV instead of SQL Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could join the two, then do the select or use a subquery (I'm on UV 10.0.2 - not sure if sub querys work) Just curious how this could be done with UV Native, and without involving a program. I first tried SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID - NSELECT PEOPLE But that returned 0 - since the NSELECT only works off the current select list, not the whole file Yes, I could create a temporary file, then select off that Yes, I could read each people record, then loop through registration to see if it doesn't exist. Both of the above require programming I guess I could create a subroutine that does the looping through registration, and put that into an I descriptor - looking for another option. George ___ 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] Selection Suggestions for using UV instead of SQL
And to do this from UniBasic: CMD = CMD-1 = PA CMD-1 = SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID TO 1 CMD-1 = IF @SYSTEM.RETURN.CODE 1 THEN GO THE.END CMD-1 = SELECT PEOPLE TO 2 CMD-1 = IF @SYSTEM.RETURN.CODE 1 THEN GO THE.END CMD-1 = MERGE.LIST 2 DIFF 1 CMD-1 = THE.END: EXECUTE CMD David A. Green (480) 813-1725 DAG Consulting -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Larry Hiscock Sent: Wednesday, October 17, 2012 10:54 AM To: 'U2 Users List' Subject: Re: [U2] Selection Suggestions for using UV instead of SQL select registration with EID = '1' saving unique PID to 1 select people to 2 merge.list 2 diff 1 Larry Hiscock Western Computer Services -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Wednesday, October 17, 2012 6:59 AM To: U2 Users Subject: [U2] Selection Suggestions for using UV instead of SQL Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could join the two, then do the select or use a subquery (I'm on UV 10.0.2 - not sure if sub querys work) Just curious how this could be done with UV Native, and without involving a program. I first tried SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID - NSELECT PEOPLE But that returned 0 - since the NSELECT only works off the current select list, not the whole file Yes, I could create a temporary file, then select off that Yes, I could read each people record, then loop through registration to see if it doesn't exist. Both of the above require programming I guess I could create a subroutine that does the looping through registration, and put that into an I descriptor - looking for another option. George ___ 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 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Selection Suggestions for using UV instead of SQL
Hmm it never occurred to me that you can BASIC EXECUTE a paragraph which doesn't actually exist in the VOC but is just created on-the-fly like this. I wonder if I should try this on Universe 11. -Original Message- From: David A. Green dgr...@dagconsulting.com To: 'U2 Users List' u2-users@listserver.u2ug.org Sent: Wed, Oct 17, 2012 11:45 am Subject: Re: [U2] Selection Suggestions for using UV instead of SQL And to do this from UniBasic: CMD = CMD-1 = PA CMD-1 = SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID TO 1 CMD-1 = IF @SYSTEM.RETURN.CODE 1 THEN GO THE.END CMD-1 = SELECT PEOPLE TO 2 CMD-1 = IF @SYSTEM.RETURN.CODE 1 THEN GO THE.END CMD-1 = MERGE.LIST 2 DIFF 1 CMD-1 = THE.END: EXECUTE CMD David A. Green (480) 813-1725 DAG Consulting -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Larry Hiscock Sent: Wednesday, October 17, 2012 10:54 AM To: 'U2 Users List' Subject: Re: [U2] Selection Suggestions for using UV instead of SQL select registration with EID = '1' saving unique PID to 1 select people to 2 merge.list 2 diff 1 Larry Hiscock Western Computer Services -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Wednesday, October 17, 2012 6:59 AM To: U2 Users Subject: [U2] Selection Suggestions for using UV instead of SQL Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could join the two, then do the select or use a subquery (I'm on UV 10.0.2 - not sure if sub querys work) Just curious how this could be done with UV Native, and without involving a program. I first tried SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID - NSELECT PEOPLE But that returned 0 - since the NSELECT only works off the current select list, not the whole file Yes, I could create a temporary file, then select off that Yes, I could read each people record, then loop through registration to see if it doesn't exist. Both of the above require programming I guess I could create a subroutine that does the looping through registration, and put that into an I descriptor - looking for another option. George ___ 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 ___ 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] Selection Suggestions for using UV instead of SQL
That's a neat trick, I always assumed that you would have to write the paragraph out to the VOC and then execute it. I like this! Thanks. -Dianne On 10/17/2012 2:45 PM, David A. Green wrote: And to do this from UniBasic: CMD = CMD-1 = PA CMD-1 = SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID TO 1 CMD-1 = IF @SYSTEM.RETURN.CODE 1 THEN GO THE.END CMD-1 = SELECT PEOPLE TO 2 CMD-1 = IF @SYSTEM.RETURN.CODE 1 THEN GO THE.END CMD-1 = MERGE.LIST 2 DIFF 1 CMD-1 = THE.END: EXECUTE CMD David A. Green (480) 813-1725 DAG Consulting -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Larry Hiscock Sent: Wednesday, October 17, 2012 10:54 AM To: 'U2 Users List' Subject: Re: [U2] Selection Suggestions for using UV instead of SQL select registration with EID = '1' saving unique PID to 1 select people to 2 merge.list 2 diff 1 Larry Hiscock Western Computer Services -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Wednesday, October 17, 2012 6:59 AM To: U2 Users Subject: [U2] Selection Suggestions for using UV instead of SQL Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could join the two, then do the select or use a subquery (I'm on UV 10.0.2 - not sure if sub querys work) Just curious how this could be done with UV Native, and without involving a program. I first tried SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID - NSELECT PEOPLE But that returned 0 - since the NSELECT only works off the current select list, not the whole file Yes, I could create a temporary file, then select off that Yes, I could read each people record, then loop through registration to see if it doesn't exist. Both of the above require programming I guess I could create a subroutine that does the looping through registration, and put that into an I descriptor - looking for another option. George ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Selection Suggestions for using UV instead of SQL
I like it! Once improvement: if the 2nd select fails, you should clear select list #1. Something like this: CMD = CMD-1 = PA CMD-1 = SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID TO 1 CMD-1 = IF @SYSTEM.RETURN.CODE 1 THEN GO THE.END CMD-1 = SELECT PEOPLE TO 2 CMD-1 = IF @SYSTEM.RETURN.CODE 1 THEN GO JUMP1 CMD-1 = MERGE.LIST 2 DIFF 1 CMD-1 = GO THE.END CMD-1 = JUMP1: CMD-1 = CLEARSELECT 1 CMD-1 = THE.END: EXECUTE CMD Even so, very cool! Thanks for sharing. JRI -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Dianne Ackerman Sent: Wednesday, October 17, 2012 3:02 PM To: U2 Users List Subject: Re: [U2] Selection Suggestions for using UV instead of SQL That's a neat trick, I always assumed that you would have to write the paragraph out to the VOC and then execute it. I like this! Thanks. -Dianne On 10/17/2012 2:45 PM, David A. Green wrote: And to do this from UniBasic: CMD = CMD-1 = PA CMD-1 = SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID TO 1 CMD-1 = IF @SYSTEM.RETURN.CODE 1 THEN GO THE.END CMD-1 = SELECT PEOPLE TO 2 CMD-1 = IF @SYSTEM.RETURN.CODE 1 THEN GO THE.END CMD-1 = MERGE.LIST 2 DIFF 1 CMD-1 = THE.END: EXECUTE CMD David A. Green (480) 813-1725 DAG Consulting -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Larry Hiscock Sent: Wednesday, October 17, 2012 10:54 AM To: 'U2 Users List' Subject: Re: [U2] Selection Suggestions for using UV instead of SQL select registration with EID = '1' saving unique PID to 1 select people to 2 merge.list 2 diff 1 Larry Hiscock Western Computer Services -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Wednesday, October 17, 2012 6:59 AM To: U2 Users Subject: [U2] Selection Suggestions for using UV instead of SQL Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could join the two, then do the select or use a subquery (I'm on UV 10.0.2 - not sure if sub querys work) Just curious how this could be done with UV Native, and without involving a program. I first tried SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID - NSELECT PEOPLE But that returned 0 - since the NSELECT only works off the current select list, not the whole file Yes, I could create a temporary file, then select off that Yes, I could read each people record, then loop through registration to see if it doesn't exist. Both of the above require programming I guess I could create a subroutine that does the looping through registration, and put that into an I descriptor - looking for another option. George ___ 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] Selection Suggestions for using UV instead of SQL
You don't need the PA in field one when you execute the stack. You can create a multi-field record and execute it and UniVerse treats it as a paragraph. Tom RATEX Business Solutions -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Dianne Ackerman Sent: Wednesday, October 17, 2012 3:02 PM To: U2 Users List Subject: Re: [U2] Selection Suggestions for using UV instead of SQL That's a neat trick, I always assumed that you would have to write the paragraph out to the VOC and then execute it. I like this! Thanks. -Dianne On 10/17/2012 2:45 PM, David A. Green wrote: And to do this from UniBasic: CMD = CMD-1 = PA CMD-1 = SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID TO 1 CMD-1 = IF @SYSTEM.RETURN.CODE 1 THEN GO THE.END CMD-1 = SELECT PEOPLE TO 2 CMD-1 = IF @SYSTEM.RETURN.CODE 1 THEN GO THE.END CMD-1 = MERGE.LIST 2 DIFF 1 CMD-1 = THE.END: EXECUTE CMD David A. Green (480) 813-1725 DAG Consulting -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Larry Hiscock Sent: Wednesday, October 17, 2012 10:54 AM To: 'U2 Users List' Subject: Re: [U2] Selection Suggestions for using UV instead of SQL select registration with EID = '1' saving unique PID to 1 select people to 2 merge.list 2 diff 1 Larry Hiscock Western Computer Services -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Wednesday, October 17, 2012 6:59 AM To: U2 Users Subject: [U2] Selection Suggestions for using UV instead of SQL Ok. I have file1 - people that has @ID of PID I have file2 - registration that has @ID of PID+EID+RID (EID = event id, RID = registration id) I want to select people who are not in the registration file with an EID of 1. With an SQL statement - I could join the two, then do the select or use a subquery (I'm on UV 10.0.2 - not sure if sub querys work) Just curious how this could be done with UV Native, and without involving a program. I first tried SELECT REGISTRATION WITH EID = '1' SAVING UNIQUE PID - NSELECT PEOPLE But that returned 0 - since the NSELECT only works off the current select list, not the whole file Yes, I could create a temporary file, then select off that Yes, I could read each people record, then loop through registration to see if it doesn't exist. Both of the above require programming I guess I could create a subroutine that does the looping through registration, and put that into an I descriptor - looking for another option. George ___ 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
