[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

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

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

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

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

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

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:

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

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

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:

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

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

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

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

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

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

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:

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

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

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

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

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,

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:

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]

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.

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

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

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

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

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

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: (

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]