[U2] Left Outer Join Question
I have 2 files. File 2 is a black list of records (keys) I want to exclude in and active select list of File 1. I've been looking into this for a little while and am not sure what I'm doing wrong. Docs are not very helpful with any examples on the how to do with this. I know of ways to deal with this in code, but was hoping for an SQL way to deal with it. - I used to be an Optimist until I knocked over the glass. -- View this message in context: http://u2-universe-unidata.1073795.n5.nabble.com/Left-Outer-Join-Question-tp42740.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Left Outer Join Question
How about a U2 way to deal with it? -Original Message- From: randyleesmith [via U2 (UniVerse UniData)] ml-node+s1073795n42740...@n5.nabble.com To: Will Johnson wjhon...@aol.com Sent: Mon, Mar 3, 2014 9:43 am Subject: Left Outer Join Question I have 2 files. File 2 is a black list of records (keys) I want to exclude in and active select list of File 1. I've been looking into this for a little while and am not sure what I'm doing wrong. Docs are not very helpful with any examples on the how to do with this. I know of ways to deal with this in code, but was hoping for an SQL way to deal with it. I used to be an Optimist until I knocked over the glass. If you reply to this email, your message will be added to the discussion below: http://u2-universe-unidata.1073795.n5.nabble.com/Left-Outer-Join-Question-tp42740.html To start a new topic under U2 - Users, email ml-node+s1073795n3...@n5.nabble.com To unsubscribe from U2 (UniVerse UniData), click here. NAML -- View this message in context: http://u2-universe-unidata.1073795.n5.nabble.com/Left-Outer-Join-Question-tp42740p42741.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Left Outer Join Question
The usual pick way to do this is to create an attribute: AE DICT FILE1 BLACKLIST 001: I 002: TRANS(FILE2, @ID, 0, 0) ; or really old-school OCONV(@ID,TFILE2;X;0;0) 003: 004: 005: 20L 006: S :GET.LIST L1 1000 Items Selected :SELECT FILE1 WITH BLACKLIST = 600 Items Selected -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of randyleesmith Sent: Monday, March 03, 2014 9:44 AM To: u2-users@listserver.u2ug.org Subject: [U2] Left Outer Join Question I have 2 files. File 2 is a black list of records (keys) I want to exclude in and active select list of File 1. I've been looking into this for a little while and am not sure what I'm doing wrong. Docs are not very helpful with any examples on the how to do with this. I know of ways to deal with this in code, but was hoping for an SQL way to deal with it. - I used to be an Optimist until I knocked over the glass. -- View this message in context: http://u2-universe-unidata.1073795.n5.nabble.com/Left-Outer-Join-Question-tp42740.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users - IMPORTANT NOTICE: This message is intended only for the addressee and may contain confidential, privileged information. If you are not the intended recipient, you may not use, copy or disclose any information contained in the message. If you have received this message in error, please notify the sender by reply e-mail and delete the message. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Left Outer Join Question
This isn't SQL, but uniquery has NSELECT that does this. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of randyleesmith Sent: Monday, March 03, 2014 12:44 PM To: u2-users@listserver.u2ug.org Subject: [U2] Left Outer Join Question I have 2 files. File 2 is a black list of records (keys) I want to exclude in and active select list of File 1. I've been looking into this for a little while and am not sure what I'm doing wrong. Docs are not very helpful with any examples on the how to do with this. I know of ways to deal with this in code, but was hoping for an SQL way to deal with it. - I used to be an Optimist until I knocked over the glass. -- View this message in context: http://u2-universe-unidata.1073795.n5.nabble.com/Left-Outer-Join-Question-tp42740.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users Dave Davis Team Lead, Research Development P: 614-875-4910 x108 F: 614-875-4088 E: dda...@harriscomputer.com [http://www.harriscomputer.com/images/signatures/HarrisSchools.jpg] [http://www.harriscomputer.com/images/signatures/DivisionofHarris.gif]http://www.harriscomputer.com/ 6110 Enterprise Parkway Grove City, OH 43123 www.harris-schoolsolutions.comhttp://www.harris-schoolsolutions.com This message is intended exclusively for the individual or entity to which it is addressed. This communication may contain information that is proprietary, privileged or confidential or otherwise legally exempt from disclosure. If you are not the named addressee, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this message in error, please notify the sender immediately by e-mail and delete all copies of the message. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Left Outer Join Question
In order to simulate select lists in SQL , I create a temporary table, and write the keys I'm Looking for as col1 INSERT into temporarytable col1 (SELECT key from file1 where .) ** My equvilent to SaveList Now assuming file2 has similar keys your working with as in file1 Now you SELECT file2 where and key NOT IN (SELECT col1 FROM temporarytable) ** My equivalent to ReadList and Select (or NSELECT in this case) George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Dave Davis Sent: Monday, March 03, 2014 1:00 PM To: U2 Users List Subject: Re: [U2] Left Outer Join Question This isn't SQL, but uniquery has NSELECT that does this. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of randyleesmith Sent: Monday, March 03, 2014 12:44 PM To: u2-users@listserver.u2ug.org Subject: [U2] Left Outer Join Question I have 2 files. File 2 is a black list of records (keys) I want to exclude in and active select list of File 1. I've been looking into this for a little while and am not sure what I'm doing wrong. Docs are not very helpful with any examples on the how to do with this. I know of ways to deal with this in code, but was hoping for an SQL way to deal with it. - I used to be an Optimist until I knocked over the glass. -- View this message in context: http://u2-universe-unidata.1073795.n5.nabble.com/Left-Outer-Join-Question-tp42740.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users Dave Davis Team Lead, Research Development P: 614-875-4910 x108 F: 614-875-4088 E: dda...@harriscomputer.com [http://www.harriscomputer.com/images/signatures/HarrisSchools.jpg] [http://www.harriscomputer.com/images/signatures/DivisionofHarris.gif]http://www.harriscomputer.com/ 6110 Enterprise Parkway Grove City, OH 43123 www.harris-schoolsolutions.comhttp://www.harris-schoolsolutions.com This message is intended exclusively for the individual or entity to which it is addressed. This communication may contain information that is proprietary, privileged or confidential or otherwise legally exempt from disclosure. If you are not the named addressee, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this message in error, please notify the sender immediately by e-mail and delete all copies of the message. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Left Outer Join Question
Then, there's always: :SELECT FILE1 xxx items selected. NSELECT FILE 2 yyy items selected. You can also do the same as noted below with an EVAL command. e.g. :select FILE1 WITH EVAL TRANS('FILE2', @ID, 0, 0) = xxx items selected. Bill - Original Message - *From:* james.mcgo...@bankofthewest.com *To:* U2 Users List u2-users@listserver.u2ug.org *Date:* 3/3/2014 9:56 AM *Subject:* Re: [U2] Left Outer Join Question The usual pick way to do this is to create an attribute: AE DICT FILE1 BLACKLIST 001: I 002: TRANS(FILE2, @ID, 0, 0) ; or really old-school OCONV(@ID,TFILE2;X;0;0) 003: 004: 005: 20L 006: S :GET.LIST L1 1000 Items Selected :SELECT FILE1 WITH BLACKLIST = 600 Items Selected -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of randyleesmith Sent: Monday, March 03, 2014 9:44 AM To: u2-users@listserver.u2ug.org Subject: [U2] Left Outer Join Question I have 2 files. File 2 is a black list of records (keys) I want to exclude in and active select list of File 1. I've been looking into this for a little while and am not sure what I'm doing wrong. Docs are not very helpful with any examples on the how to do with this. I know of ways to deal with this in code, but was hoping for an SQL way to deal with it. - I used to be an Optimist until I knocked over the glass. -- View this message in context: http://u2-universe-unidata.1073795.n5.nabble.com/Left-Outer-Join-Question-tp42740.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users - IMPORTANT NOTICE: This message is intended only for the addressee and may contain confidential, privileged information. If you are not the intended recipient, you may not use, copy or disclose any information contained in the message. If you have received this message in error, please notify the sender by reply e-mail and delete the message. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Left Outer Join Question
Thanks for all the options. Been going over the Universe SQL Reference Doc's but they didn't give me enough detail. I think, a long time ago I have used NSELECT before. - I used to be an Optimist until I knocked over the glass. -- View this message in context: http://u2-universe-unidata.1073795.n5.nabble.com/Left-Outer-Join-Question-tp42740p42746.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Left Outer Join Question
I love learning little tidbits like this, that's awesome! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Bill Haskett Sent: Monday, March 03, 2014 10:39 AM To: U2 Users List Subject: Re: [U2] Left Outer Join Question Then, there's always: :SELECT FILE1 xxx items selected. NSELECT FILE 2 yyy items selected. You can also do the same as noted below with an EVAL command. e.g. :select FILE1 WITH EVAL TRANS('FILE2', @ID, 0, 0) = xxx items selected. Bill - Original Message - *From:* james.mcgo...@bankofthewest.com *To:* U2 Users List u2-users@listserver.u2ug.org *Date:* 3/3/2014 9:56 AM *Subject:* Re: [U2] Left Outer Join Question The usual pick way to do this is to create an attribute: AE DICT FILE1 BLACKLIST 001: I 002: TRANS(FILE2, @ID, 0, 0) ; or really old-school OCONV(@ID,TFILE2;X;0;0) 003: 004: 005: 20L 006: S :GET.LIST L1 1000 Items Selected :SELECT FILE1 WITH BLACKLIST = 600 Items Selected -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of randyleesmith Sent: Monday, March 03, 2014 9:44 AM To: u2-users@listserver.u2ug.org Subject: [U2] Left Outer Join Question I have 2 files. File 2 is a black list of records (keys) I want to exclude in and active select list of File 1. I've been looking into this for a little while and am not sure what I'm doing wrong. Docs are not very helpful with any examples on the how to do with this. I know of ways to deal with this in code, but was hoping for an SQL way to deal with it. - I used to be an Optimist until I knocked over the glass. -- View this message in context: http://u2-universe-unidata.1073795.n5.nabble.com/Left-Outer-Join-Quest ion-tp42740.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users - IMPORTANT NOTICE: This message is intended only for the addressee and may contain confidential, privileged information. If you are not the intended recipient, you may not use, copy or disclose any information contained in the message. If you have received this message in error, please notify the sender by reply e-mail and delete the message. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Left Outer Join Question
I've found that you can keep on going too as long as the keys are the same. :SELECT FILE1 xxx items selected. NSELECT FILE 2 yyy items selected. NSELECT FILE 3 zzz items selected. Turns out I may need to do it this way. Cool. - I used to be an Optimist until I knocked over the glass. -- View this message in context: http://u2-universe-unidata.1073795.n5.nabble.com/Left-Outer-Join-Question-tp42740p42748.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
Jeff Try mvQuery, that should not have any problems with those volumes. Brian PS Of course for actual reports rather than CSVs, there's mvPDF.. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of jeffrey Butera Sent: 28 February 2014 14:22 To: U2 Users List Subject: [U2] Reporting Tools I'm looking for feedback from those in any industry about your report needs with U2 and what tools you use to solve them. In short, Entrinsik has serious inroads in the academic community and Informer is a reasonably good and inexpensive tool for most reports. However, we are running into issues with some users like to write large reports. Large, by out standards, are reports with 200+ columns in the output. Are others writing reports with this many output fields? If so, are you ever running up against limits in the sentence size (U_SENTLEN)? Our limit is about 9700 chars and we have some reports hitting this limit. I'm having a hard time explaining to some VP types that the database can't handle this size report... Any feedback is welcome. -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ 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] Reporting Tools
On 3/3/14, 2:58 PM, Brian Leach wrote: Jeff Try mvQuery, that should not have any problems with those volumes. Hi Brian We've isolated the problem to Unidata itself, not the reporting tool. In short, when we do a LIST with about 150 fields, it throws: too many items in LIST As soon as we erase a field (any field), the LIST statement runs properly. Unfortunately, I cannot locate any parameter that might control this. I thought we were hitting U_SENTLEN - but we're nowhere near that value. -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
Jeff, What I think many of us are suggesting is essentially if it hurts when you do that, don't do that i.e. if the query language won't accomplish what you want to do, use something else. jeffrey Butera wrote: On 3/3/14, 2:58 PM, Brian Leach wrote: Jeff Try mvQuery, that should not have any problems with those volumes. Hi Brian We've isolated the problem to Unidata itself, not the reporting tool. In short, when we do a LIST with about 150 fields, it throws: too many items in LIST As soon as we erase a field (any field), the LIST statement runs properly. Unfortunately, I cannot locate any parameter that might control this. I thought we were hitting U_SENTLEN - but we're nowhere near that value. -- Jeff Schasny - Denver, Co, USA jschasny at gmail dot com ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
On 3/3/14, 3:21 PM, Jeff Schasny wrote: Jeff, What I think many of us are suggesting is essentially if it hurts when you do that, don't do that i.e. if the query language won't accomplish what you want to do, use something else. Jeff Believe me - I hear you (and others). But my administration isn't listening because they all think this is a reasonable request (excel can handle 200+ columns). So if I can't make this work in Unidata I'll have to move to MSSQL which I really, really, really don't want to do. At this point I'm just trying to understand the limit on the number of fields in a LIST statement. I know in my case that it's not a sentence length issue as I might've thought earlier so I'd like to know what is causing this (with the understanding that I very well may not be able to solve this). Jeff jeffrey Butera wrote: On 3/3/14, 2:58 PM, Brian Leach wrote: Jeff Try mvQuery, that should not have any problems with those volumes. Hi Brian We've isolated the problem to Unidata itself, not the reporting tool. In short, when we do a LIST with about 150 fields, it throws: too many items in LIST As soon as we erase a field (any field), the LIST statement runs properly. Unfortunately, I cannot locate any parameter that might control this. I thought we were hitting U_SENTLEN - but we're nowhere near that value. -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Left Outer Join Question
On 03/03/2014 19:06, randyleesmith wrote: I've found that you can keep on going too as long as the keys are the same. :SELECT FILE1 xxx items selected. NSELECT FILE 2 yyy items selected. NSELECT FILE 3 zzz items selected. Turns out I may need to do it this way. Cool. u2 neither knows nor cares how you get a select list, so each subsequent NSELECT doesn't know that the current list is the result of an NSELECT rather than a SELECT. The other way to do it is SELECT FILE1 TO 1 SELECT FILE2 TO 2 SELECT FILE3 TO 3 and then use commands that combine select lists. I can't remember what they are because I've never used them. Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
Jeff, I would second Rodney Baakkonen's suggestion of writing out your delimited data to a Unix directory and ftp the blob to wherever the user would like to find it. This vastly expands the possible size limitations you would next hit at the Unix level. Of course if the existing reports consists of a lot of complex dictionaries then you would have a programming task you may not want. Endless alternatives spring to mind for that. Is performance an issue? Marc Rutherford Principal Programmer/Analyst Advanced Bionics LLC 661 362-1754 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of jeffrey Butera Sent: Monday, March 03, 2014 12:34 PM To: U2 Users List; jscha...@gmail.com Subject: Re: [U2] Reporting Tools On 3/3/14, 3:21 PM, Jeff Schasny wrote: Jeff, What I think many of us are suggesting is essentially if it hurts when you do that, don't do that i.e. if the query language won't accomplish what you want to do, use something else. Jeff Believe me - I hear you (and others). But my administration isn't listening because they all think this is a reasonable request (excel can handle 200+ columns). So if I can't make this work in Unidata I'll have to move to MSSQL which I really, really, really don't want to do. At this point I'm just trying to understand the limit on the number of fields in a LIST statement. I know in my case that it's not a sentence length issue as I might've thought earlier so I'd like to know what is causing this (with the understanding that I very well may not be able to solve this). Jeff jeffrey Butera wrote: On 3/3/14, 2:58 PM, Brian Leach wrote: Jeff Try mvQuery, that should not have any problems with those volumes. Hi Brian We've isolated the problem to Unidata itself, not the reporting tool. In short, when we do a LIST with about 150 fields, it throws: too many items in LIST As soon as we erase a field (any field), the LIST statement runs properly. Unfortunately, I cannot locate any parameter that might control this. I thought we were hitting U_SENTLEN - but we're nowhere near that value. -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ 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] Reporting Tools
Hi Jeff, In case you missed it before: Does your query use the LIMIT keyword? This is an internally hardcoded limit that was increased, IIRC, in 7.3.6 to 250. On further checking, here is the issue, along with fix version. I suggest you update to the latest UDT version. UDT-4284 - Request for UniQuery hard limit of 150 attributes (in LIST or REFORMAT commands) to be increased A change was made to increase this to 250 attributes in 7.3.5. The actual limit has now been added to the report from the LIMIT verb so you can see limitation as U_ATTRLIST As a note, limitations on number of columns isn't just an MV issue. MS SQL will prevent you from even creating tables with large amounts of columns, whereas MV just limits the active querying of so many at a time, so before deciding to move because of something like that, you should really look into how the issue is explained to management. http://stackoverflow.com/questions/14070932/work-around-sql-server-maximum-columns-limit-1024-and-8kb-record-size Regards, Dan -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of jeffrey Butera Sent: Monday, March 03, 2014 1:34 PM To: U2 Users List; jscha...@gmail.com Subject: Re: [U2] Reporting Tools On 3/3/14, 3:21 PM, Jeff Schasny wrote: Jeff, What I think many of us are suggesting is essentially if it hurts when you do that, don't do that i.e. if the query language won't accomplish what you want to do, use something else. Jeff Believe me - I hear you (and others). But my administration isn't listening because they all think this is a reasonable request (excel can handle 200+ columns). So if I can't make this work in Unidata I'll have to move to MSSQL which I really, really, really don't want to do. At this point I'm just trying to understand the limit on the number of fields in a LIST statement. I know in my case that it's not a sentence length issue as I might've thought earlier so I'd like to know what is causing this (with the understanding that I very well may not be able to solve this). Jeff jeffrey Butera wrote: On 3/3/14, 2:58 PM, Brian Leach wrote: Jeff Try mvQuery, that should not have any problems with those volumes. Hi Brian We've isolated the problem to Unidata itself, not the reporting tool. In short, when we do a LIST with about 150 fields, it throws: too many items in LIST As soon as we erase a field (any field), the LIST statement runs properly. Unfortunately, I cannot locate any parameter that might control this. I thought we were hitting U_SENTLEN - but we're nowhere near that value. -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ 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] Left Outer Join Question
The Command that you are looking for is MERGE.LIST EG SELECT FILE1 TO 1 SELECT FILE2 TO 2 MERGE.LIST 1 INTERSECT 2 TO 3 Rgds Bernard Lubin Bernard Lubin System Group Developer 549 Blackburn Rd Mount Waverley VIC 3149 Australia P: +61 3 9535 F: +61 3 9535 2274 E: bernard.lu...@pentanasolutions.com W: pentanasolutions.com Pentana Solutions accepts no liability for the content of this email, or for the consequences of any actions taken on the basis of the information provided, unless that information is subsequently confirmed in writing. If you are not the intended recipient, you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited. Bernard Lubin System Group Developer 549 Blackburn Rd Mount Waverley VIC 3149 Australia P: +61 3 9535 F: +61 3 9535 2274 E: bernard.lu...@pentanasolutions.com W: pentanasolutions.com Pentana Solutions accepts no liability for the content of this email, or for the consequences of any actions taken on the basis of the information provided, unless that information is subsequently confirmed in writing. If you are not the intended recipient, you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Anthonys Lists Sent: Tuesday, 4 March 2014 7:57 AM To: U2 Users List Subject: Re: [U2] Left Outer Join Question On 03/03/2014 19:06, randyleesmith wrote: I've found that you can keep on going too as long as the keys are the same. :SELECT FILE1 xxx items selected. NSELECT FILE 2 yyy items selected. NSELECT FILE 3 zzz items selected. Turns out I may need to do it this way. Cool. u2 neither knows nor cares how you get a select list, so each subsequent NSELECT doesn't know that the current list is the result of an NSELECT rather than a SELECT. The other way to do it is SELECT FILE1 TO 1 SELECT FILE2 TO 2 SELECT FILE3 TO 3 and then use commands that combine select lists. I can't remember what they are because I've never used them. Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- Message protected by DealerGuard: e-mail anti-virus, anti-spam and content filtering. http://www.pentanasolutions.com Click here to report this message as spam: https://login.mailguard.com.au/report/1J6xBjB1pg/5xrs5L4i5SL8ElB4DyYMKj/1.507 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
On 3/3/14, 4:01 PM, Daniel McGrath wrote: Hi Jeff, In case you missed it before: Does your query use the LIMIT keyword? This is an internally hardcoded limit that was increased, IIRC, in 7.3.6 to 250. On further checking, here is the issue, along with fix version. I suggest you update to the latest UDT version. UDT-4284 - Request for UniQuery hard limit of 150 attributes (in LIST or REFORMAT commands) to be increased A change was made to increase this to 250 attributes in 7.3.5. The actual limit has now been added to the report from the LIMIT verb so you can see limitation as U_ATTRLIST Thanks Dan - this helps immensely. -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
As a work around, would this work: * produce file1 with record key and then 100+ data fields * produce file2 with remaining data fields * execute Linux command: paste file1 file2 myrealdata.csv David Rotman, Ph.D. Associate Vice President for Technology Chief Information Officer Cedarville University 251 N. Main Street Cedarville, OH 45314 rotm...@cedarville.edu voice 937-766-7905 fax 937-766-8819 On Mon, Mar 3, 2014 at 3:34 PM, jeffrey Butera jbut...@hampshire.eduwrote: On 3/3/14, 3:21 PM, Jeff Schasny wrote: Jeff, What I think many of us are suggesting is essentially if it hurts when you do that, don't do that i.e. if the query language won't accomplish what you want to do, use something else. Jeff Believe me - I hear you (and others). But my administration isn't listening because they all think this is a reasonable request (excel can handle 200+ columns). So if I can't make this work in Unidata I'll have to move to MSSQL which I really, really, really don't want to do. At this point I'm just trying to understand the limit on the number of fields in a LIST statement. I know in my case that it's not a sentence length issue as I might've thought earlier so I'd like to know what is causing this (with the understanding that I very well may not be able to solve this). Jeff jeffrey Butera wrote: On 3/3/14, 2:58 PM, Brian Leach wrote: Jeff Try mvQuery, that should not have any problems with those volumes. Hi Brian We've isolated the problem to Unidata itself, not the reporting tool. In short, when we do a LIST with about 150 fields, it throws: too many items in LIST As soon as we erase a field (any field), the LIST statement runs properly. Unfortunately, I cannot locate any parameter that might control this. I thought we were hitting U_SENTLEN - but we're nowhere near that value. -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ 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] Reporting Tools
If you're not already, it's a really good idea to consider creating something that will make producing excel files (the number one choice of accounting groups everywhere) from your reports easily. Many years ago we used to physically print a bunch of reports each night, but since creating a tool the users can easily use, almost everything is electronic. http://www.pickwiki.com/cgi-bin/wiki.pl?DumpRecall shows one way to do it, though it's pretty specific to our needs. It wouldn't be hard to use this as inspiration for something that works in your own environment. I'd forgotten about the 150 field limit in unidata, but this comment remembers: * We have a list of field names and a select, parse them the hard way * and write a tab-seperated file. This is slower but avoids limitations * on the number of columns (of unidata) and the perl module WriteExcel -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of jeffrey Butera Sent: Monday, March 03, 2014 1:05 PM To: U2 Users List; dmcgr...@rocketsoftware.com Subject: Re: [U2] Reporting Tools On 3/3/14, 4:01 PM, Daniel McGrath wrote: Hi Jeff, In case you missed it before: Does your query use the LIMIT keyword? This is an internally hardcoded limit that was increased, IIRC, in 7.3.6 to 250. On further checking, here is the issue, along with fix version. I suggest you update to the latest UDT version. UDT-4284 - Request for UniQuery hard limit of 150 attributes (in LIST or REFORMAT commands) to be increased A change was made to increase this to 250 attributes in 7.3.5. The actual limit has now been added to the report from the LIMIT verb so you can see limitation as U_ATTRLIST Thanks Dan - this helps immensely. -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users - IMPORTANT NOTICE: This message is intended only for the addressee and may contain confidential, privileged information. If you are not the intended recipient, you may not use, copy or disclose any information contained in the message. If you have received this message in error, please notify the sender by reply e-mail and delete the message. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
Ian you don't happen to have an example of what this code *does* do you? -Original Message- From: McGowan, Ian james.mcgo...@bankofthewest.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Mon, Mar 3, 2014 1:35 pm Subject: Re: [U2] Reporting Tools If you're not already, it's a really good idea to consider creating something that will make producing excel files (the number one choice of accounting groups everywhere) from your reports easily. Many years ago we used to physically print a bunch of reports each night, but since creating a tool the users can easily use, almost everything is electronic. http://www.pickwiki.com/cgi-bin/wiki.pl?DumpRecall shows one way to do it, though it's pretty specific to our needs. It wouldn't be hard to use this as inspiration for something that works in your own environment. I'd forgotten about the 150 field limit in unidata, but this comment remembers: * We have a list of field names and a select, parse them the hard way * and write a tab-seperated file. This is slower but avoids limitations * on the number of columns (of unidata) and the perl module WriteExcel -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of jeffrey Butera Sent: Monday, March 03, 2014 1:05 PM To: U2 Users List; dmcgr...@rocketsoftware.com Subject: Re: [U2] Reporting Tools On 3/3/14, 4:01 PM, Daniel McGrath wrote: Hi Jeff, In case you missed it before: Does your query use the LIMIT keyword? This is an internally hardcoded limit that was increased, IIRC, in 7.3.6 to 250. On further checking, here is the issue, along with fix version. I suggest you update to the latest UDT version. UDT-4284 - Request for UniQuery hard limit of 150 attributes (in LIST or REFORMAT commands) to be increased A change was made to increase this to 250 attributes in 7.3.5. The actual limit has now been added to the report from the LIMIT verb so you can see limitation as U_ATTRLIST Thanks Dan - this helps immensely. -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users - IMPORTANT NOTICE: This message is intended only for the addressee and may contain confidential, privileged information. If you are not the intended recipient, you may not use, copy or disclose any information contained in the message. If you have received this message in error, please notify the sender by reply e-mail and delete the message. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
Jeff: I've written a little BASIC utility that takes a file, selects what I want, then outputs via the ITYPE function as a CSV file to the spooler. I wonder if that gets around the 150 field limit, since it's in BASIC? I know UniData is weird regarding this kind of thing. For instance, they also have something like a 256 item limit in a query statement... :SELECT MYFILE WITH CUST# = 123...256257 ...will abort. This too is hardwired. However, a neat little utility provided by U2 called LSELECT selects items whose field definition value is in a list, so if you're on the web you'd first allow the user to select stuff, put it into a list, then use LSELECT to select the items from the file...kind of like: :SELECT CUSTOMERS WITH LAST_ACTIVE 01/01/13 500 records selected to list 0. SAVE.LIST MY.LIST Overwriting existing saved list. 500 key(s) saved to 1 record(s). LSELECT SALESORDERS WITH CUSTNO IN MY.LIST VERBOSE (Parsed Command) LSELECT SALESORDERS WITH CUSTNO IN MY.LIST TO 0 (Selected List) List Source MY.LIST found 500 items. (Create Temp File) LSELECT0031 file created with MOD = 3 (Loaded List to Temp File) - 500 items. 1436 key(s) saved to 1 record(s). (Selected Result List) UDT.COMMAND = select SALESORDERS WITH EVAL TRANS('LSELECT0031',CUSTNO,'@ID','X') GT TO 0 UDT.RESPONSE = ■1436 records selected to list 0.■■ Destination 0 Named List 0 (Delete Temp File) LSELECT0031 file deleted... Makes one wonder...h. Bill Untitled Page - Original Message - *From:* rotm...@cedarville.edu *To:* U2 Users List u2-users@listserver.u2ug.org *Date:* 3/3/2014 12:57 PM *Subject:* Re: [U2] Reporting Tools As a work around, would this work: * produce file1 with record key and then 100+ data fields * produce file2 with remaining data fields * execute Linux command: paste file1 file2 myrealdata.csv David Rotman, Ph.D. Associate Vice President for Technology Chief Information Officer Cedarville University 251 N. Main Street Cedarville, OH 45314 rotm...@cedarville.edu voice 937-766-7905 fax 937-766-8819 On Mon, Mar 3, 2014 at 3:34 PM, jeffrey Butera jbut...@hampshire.eduwrote: On 3/3/14, 3:21 PM, Jeff Schasny wrote: Jeff, What I think many of us are suggesting is essentially if it hurts when you do that, don't do that i.e. if the query language won't accomplish what you want to do, use something else. Jeff Believe me - I hear you (and others). But my administration isn't listening because they all think this is a reasonable request (excel can handle 200+ columns). So if I can't make this work in Unidata I'll have to move to MSSQL which I really, really, really don't want to do. At this point I'm just trying to understand the limit on the number of fields in a LIST statement. I know in my case that it's not a sentence length issue as I might've thought earlier so I'd like to know what is causing this (with the understanding that I very well may not be able to solve this). Jeff jeffrey Butera wrote: On 3/3/14, 2:58 PM, Brian Leach wrote: Jeff Try mvQuery, that should not have any problems with those volumes. Hi Brian We've isolated the problem to Unidata itself, not the reporting tool. In short, when we do a LIST with about 150 fields, it throws: too many items in LIST As soon as we erase a field (any field), the LIST statement runs properly. Unfortunately, I cannot locate any parameter that might control this. I thought we were hitting U_SENTLEN - but we're nowhere near that value. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Left Outer Join Question
I must be doing something wrong. When I try this I get; SELECT FILE1 TO 1 SELECT FILE2 TO 2 MERGE.LIST 1 INTERSECT 2 TO 3 SELECT list 1 is not active. - I used to be an Optimist until I knocked over the glass. -- View this message in context: http://u2-universe-unidata.1073795.n5.nabble.com/Left-Outer-Join-Question-tp42740p42762.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Left Outer Join Question
What ECl type are you using. If you are using 'P' instead of 'U', use the lowercase verb and it should work select FILE1 TO 1 select FILE2 TO 2 MERGE.LIST 1 INTERSECT 2 TO 3 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of randyleesmith Sent: Monday, March 03, 2014 4:37 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Left Outer Join Question I must be doing something wrong. When I try this I get; SELECT FILE1 TO 1 SELECT FILE2 TO 2 MERGE.LIST 1 INTERSECT 2 TO 3 SELECT list 1 is not active. - I used to be an Optimist until I knocked over the glass. -- View this message in context: http://u2-universe-unidata.1073795.n5.nabble.com/Left-Outer-Join-Question-tp42740p42762.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- CONFIDENTIALITY NOTICE: If you have received this email in error, please immediately notify the sender by e-mail at the address shown. This email transmission may contain confidential information. This information is intended only for the use of the individual(s) or entity to whom it is intended even if addressed incorrectly. Please delete it from your files if you are not the intended recipient. Thank you for your compliance. Copyright (c) 2014 Cigna == ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Left Outer Join Question
If you on UniData and you're in PICK mode then you have to lower case this kind of thing, since such PRIME style stuff only works in the UniData parser mode. So, try this: 3 Dev (0)- SELECT ADDRBOOK WITH MAILLIST NE AND WITH MAILLIST_OWNERS = [BOD] 309 records selected to list 0. SAVE-LIST BILL1 Overwriting existing saved list. 309 key(s) saved to 1 record(s). SELECT ADDRBOOK WITH CUSTOMERS NE 114 records selected to list 0. SAVE-LIST BILL2 114 key(s) saved to 1 record(s). get.list BILL1 TO 1 309 records retrieved to list 1. get.list BILL2 TO 2 114 records retrieved to list 2. merge.list 1 UNION 2 TO 0 423 record(s) selected. 3 Dev (0) ...and here we are with 423 items selected as the active select list. HTH, Bill //- Original Message - *From:* randyleesm...@roadrunner.com *To:* u2-users@listserver.u2ug.org *Date:* 3/3/2014 2:36 PM *Subject:* Re: [U2] Left Outer Join Question I must be doing something wrong. When I try this I get; SELECT FILE1 TO 1 SELECT FILE2 TO 2 MERGE.LIST 1 INTERSECT 2 TO 3 SELECT list 1 is not active. - I used to be an Optimist until I knocked over the glass. -- View this message in context: http://u2-universe-unidata.1073795.n5.nabble.com/Left-Outer-Join-Question-tp42740p42762.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Left Outer Join Question
Remove the TO 3 on the MERGE command. David A. Green (480) 201-7953 DAG Consulting -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of randyleesmith Sent: Monday, March 03, 2014 3:37 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Left Outer Join Question I must be doing something wrong. When I try this I get; SELECT FILE1 TO 1 SELECT FILE2 TO 2 MERGE.LIST 1 INTERSECT 2 TO 3 SELECT list 1 is not active. - I used to be an Optimist until I knocked over the glass. -- View this message in context: http://u2-universe-unidata.1073795.n5.nabble.com/Left-Outer-Join-Question-tp 42740p42762.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Left Outer Join Question
Just retried it on a couple of servers, AIX and Linux running different version of UniVerse and am not getting your error. Are you running UniVerse or UniData. Not too sure what the syntax would be on UniData Rgds Bernard Lubin System Group Developer 549 Blackburn Rd Mount Waverley VIC 3149 Australia P: +61 3 9535 F: +61 3 9535 2274 E: bernard.lu...@pentanasolutions.com W: pentanasolutions.com Pentana Solutions accepts no liability for the content of this email, or for the consequences of any actions taken on the basis of the information provided, unless that information is subsequently confirmed in writing. If you are not the intended recipient, you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited. Bernard Lubin System Group Developer 549 Blackburn Rd Mount Waverley VIC 3149 Australia P: +61 3 9535 F: +61 3 9535 2274 E: bernard.lu...@pentanasolutions.com W: pentanasolutions.com Pentana Solutions accepts no liability for the content of this email, or for the consequences of any actions taken on the basis of the information provided, unless that information is subsequently confirmed in writing. If you are not the intended recipient, you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of randyleesmith Sent: Tuesday, 4 March 2014 9:37 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Left Outer Join Question I must be doing something wrong. When I try this I get; SELECT FILE1 TO 1 SELECT FILE2 TO 2 MERGE.LIST 1 INTERSECT 2 TO 3 SELECT list 1 is not active. - I used to be an Optimist until I knocked over the glass. -- View this message in context: http://u2-universe-unidata.1073795.n5.nabble.com/Left-Outer-Join-Question-tp42740p42762.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- Message protected by DealerGuard: e-mail anti-virus, anti-spam and content filtering. http://www.pentanasolutions.com Click here to report this message as spam: https://login.mailguard.com.au/report/1J6znFV8yk/enIHXI5PduuibDphvUC9e/4.239 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Left Outer Join Question
I'm on Universe for this. I will try your options next time I'm on the system. Thanks for all the help. - I used to be an Optimist until I knocked over the glass. -- View this message in context: http://u2-universe-unidata.1073795.n5.nabble.com/Left-Outer-Join-Question-tp42740p42767.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Left Outer Join Question
Works for me under UV 10.2.7 (PIOPEN flavor) on AIX: SELECT GCRS01007.BP TO 1 74 record(s) selected to SELECT list #1. SELECT LGH.BP TO 2 176 record(s) selected to SELECT list #2. MERGE.LIST 1 INTERSECT 2 TO 3 20 record(s) selected to SELECT list #3. Larry Hiscock Western Computer Services I'm on Universe for this. I will try your options next time I'm on the system. Thanks for all the help. - I used to be an Optimist until I knocked over the glass. -- View this message in context: http://u2-universe-unidata.1073795.n5.nabble.com/Left-Outer-Join-Question-tp42740p42767.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Left Outer Join Question
Just checking the obvious cause... Are there any items in FILE1 to be selected? Gregor Scott System Group Manager 549 Blackburn Rd Mount Waverley VIC 3149 Australia P: +61 3 9535 F: +61 3 9535 2274 E: gregor.sc...@pentanasolutions.com W: pentanasolutions.com Pentana Solutions accepts no liability for the content of this email, or for the consequences of any actions taken on the basis of the information provided, unless that information is subsequently confirmed in writing. If you are not the intended recipient, you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited. Gregor Scott System Group Manager 549 Blackburn Rd Mount Waverley VIC 3149 Australia P: +61 3 9535 F: +61 3 9535 2274 E: gregor.sc...@pentanasolutions.com W: pentanasolutions.com Pentana Solutions accepts no liability for the content of this email, or for the consequences of any actions taken on the basis of the information provided, unless that information is subsequently confirmed in writing. If you are not the intended recipient, you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of randyleesmith Sent: Tuesday, 4 March 2014 9:37 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Left Outer Join Question I must be doing something wrong. When I try this I get; SELECT FILE1 TO 1 SELECT FILE2 TO 2 MERGE.LIST 1 INTERSECT 2 TO 3 SELECT list 1 is not active. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
If I have a recall (our terminology for a canned query) like this: RECALL.SHELL Info-Trieve Shell03 Mar 2014 BOTW_DEV217:38:03 Command Name MARKETING.REP Modified: ( bytes) Compiled: ( bytes) by root SELECT PARAMETER BY MRKT.NAME WITH @ID = 10*]~ LIST PARAMETER UATB.MRKT.NAME UATB.MRKT.OFFICE ATB Edit Copy Mail New Run Search Width Quit I get an excel spreadsheet as an attachment like this, limited only by what excel can handle. I've used a variety of formats over the years, but have come back to doing tab-delimited, but naming the file *.xls - that seems to be the most likely to not get mangled in transit and still open in a variety of spreadsheet programs and versions. NAME OFFICE 3RD PARTY SERVICER 1 AB HUSSMAN PROGRAM 4 ACCURAY, INC. PROGRAM 6 -Original Message- From: u2-users-boun...@listserver.u2ug.orgmailto:u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wjhonson Sent: Monday, March 03, 2014 1:42 PM To: u2-users@listserver.u2ug.orgmailto:u2-users@listserver.u2ug.org Subject: Re: [U2] Reporting Tools Ian you don't happen to have an example of what this code *does* do you? IMPORTANT NOTICE: This message is intended only for the addressee and may contain confidential, privileged information. If you are not the intended recipient, you may not use, copy or disclose any information contained in the message. If you have received this message in error, please notify the sender by reply e-mail and delete the message. - IMPORTANT NOTICE: This message is intended only for the addressee and may contain confidential, privileged information. If you are not the intended recipient, you may not use, copy or disclose any information contained in the message. If you have received this message in error, please notify the sender by reply e-mail and delete the message. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Left Outer Join Question
Select * from FILE1 where id not in (select id from FILE2) ??? -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of randyleesmith Sent: Monday, March 03, 2014 10:44 AM To: u2-users@listserver.u2ug.org Subject: [U2] Left Outer Join Question I have 2 files. File 2 is a black list of records (keys) I want to exclude in and active select list of File 1. I've been looking into this for a little while and am not sure what I'm doing wrong. Docs are not very helpful with any examples on the how to do with this. I know of ways to deal with this in code, but was hoping for an SQL way to deal with it. - I used to be an Optimist until I knocked over the glass. -- View this message in context: http://u2-universe-unidata.1073795.n5.nabble.com/Left-Outer-Join-Question-tp42740.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ** This email and any files transmitted with it are confidential and may be subject to copyright. They are intended solely for the use of the individual or entity to which they are addressed. If you have received this message in error please notify AFS immediately by return email. Any views or opinions presented in this email are solely those of the author and do not necessarily represent those of AFS, except where an authorized sender specifically states them to be the views of AFS. It is your responsibility to verify this email and any attachments for the presence of viruses. AFS accepts no liability for any damage caused by any virus transmitted. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users