Re: [U2] Left Outer Join Question
I wrote a paragraph called ML to use MERGE.LIST on saved lists - much easier than assembling the lists manually. PA * ML type list1 list2 list3 * I2,TYPE IF TYPE = U THEN GO THERE IF TYPE = D THEN GO THERE IF TYPE = I THEN GO THERE DISPLAY DISPLAY Command Format is DISPLAY ML type list1 list2 list3 DISPLAY DISPLAY Where type is 'U'nion or 'D'ifference or 'I'ntersection DISPLAY DISPLAY and where list1 and list2 exist on SAVEDLISTS. DISPLAY The result will be stored as list3. DISPLAY GO FINISH * THERE: IF I3,LIST1 = '' THEN GO FINISH IF I4,LIST2 = '' THEN GO FINISH IF I5,LIST3 = '' THEN GO FINISH GET.LIST LIST1 TO 1 GET.LIST LIST2 TO 2 * IF TYPE = U THEN GO UNION IF TYPE = D THEN GO DIFFERENCE IF TYPE = I THEN GO INTERSECTION * UNION: MERGE.LIST 1 UNION 2 TO 3 SAVE.LIST LIST3 FROM 3 DISPLAY UNION GO FINISH * DIFFERENCE: MERGE.LIST 1 DIFFERENCE 2 TO 3 SAVE.LIST LIST3 FROM 3 DISPLAY DIFFERENCE GO FINISH * INTERSECTION: MERGE.LIST 1 INTERSECTION 2 TO 3 SAVE.LIST LIST3 FROM 3 DISPLAY INTERSECTION GO FINISH * FINISH: CLEARPROMPTS ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Left Outer Join Question
I tried the simplest way and still could not get it to work. SF-MORTSELECT OLDMAS TO 1 5247 record(s) selected to SELECT list #1. SF-MORTSELECT TRWMONTHEND TO 2 5229 record(s) selected to SELECT list #2. SF-MORTMERGE.LIST 1 INTERSECT 2 TO 3 SELECT list 1 is not active. SF-MORTMERGE.LIST 1 INTERSECT 2 SELECT list 1 is not active. I even tried the lowercase select and got the same result. Our version is 10.2.7 and we are running in PICK PICK.FORMAT. We are running a number of older applications and Proc's. So it may because of the Version I'm using. : 0001: X : 0002: 10.2.7 : 0003: PICK : 0004: PICK.FORMAT : 0005: 10.2.7 - 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-tp42740p42772.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
Just to be sure, What does your VOC for MERGE.LIST show? I'm on 10.0.2 of UV - but Information favor. ED VOC MERGE.LIST 4 lines long. : P 0001: V 0002: MERGE.LIST 0003: I 0004: GKSX George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of randyleesmith Sent: Tuesday, March 04, 2014 7:39 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Left Outer Join Question I tried the simplest way and still could not get it to work. SF-MORTSELECT OLDMAS TO 1 5247 record(s) selected to SELECT list #1. SF-MORTSELECT TRWMONTHEND TO 2 5229 record(s) selected to SELECT list #2. SF-MORTMERGE.LIST 1 INTERSECT 2 TO 3 SELECT list 1 is not active. SF-MORTMERGE.LIST 1 INTERSECT 2 SELECT list 1 is not active. I even tried the lowercase select and got the same result. Our version is 10.2.7 and we are running in PICK PICK.FORMAT. We are running a number of older applications and Proc's. So it may because of the Version I'm using. : 0001: X : 0002: 10.2.7 : 0003: PICK : 0004: PICK.FORMAT : 0005: 10.2.7 - 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-tp42740p42772.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
SELECT OLDMAS 5247 records selected NSELECT TRWMONTHEND -Original Message- From: randyleesmith [via U2 (UniVerse UniData)] ml-node+s1073795n42772...@n5.nabble.com To: Will Johnson wjhon...@aol.com Sent: Tue, Mar 4, 2014 4:39 am Subject: Re: Left Outer Join Question I tried the simplest way and still could not get it to work. SF-MORTSELECT OLDMAS TO 1 5247 record(s) selected to SELECT list #1. SF-MORTSELECT TRWMONTHEND TO 2 5229 record(s) selected to SELECT list #2. SF-MORTMERGE.LIST 1 INTERSECT 2 TO 3 SELECT list 1 is not active. SF-MORTMERGE.LIST 1 INTERSECT 2 SELECT list 1 is not active. I even tried the lowercase select and got the same result. Our version is 10.2.7 and we are running in PICK PICK.FORMAT. We are running a number of older applications and Proc's. So it may because of the Version I'm using. : 0001: X : 0002: 10.2.7 : 0003: PICK : 0004: PICK.FORMAT : 0005: 10.2.7 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-tp42740p42772.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-tp42740p42775.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
It looks like the same thing. 4 lines long. : 0001: V : 0002: MERGE.LIST : 0003: I : 0004: GKSX Bottom at line 4. - 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-tp42740p42776.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
You are definitely typing the lines from TCL level, and NOT from a program that's running EXECUTE/PERFORMS? Because if they are being run from a program, you have to use a multivalued command to EXECUTE, with all The lines in that command Example CMD= CMD-1=GET-LIST LIST1 TO 1 CMD-1=GET-LIST LIST2 TO 2 CMD-1=MERGE.LIST 1 INTERSECT 2 TO 3 EXECUTE CMD LOOP READNEXT ID FROM 3 ELSE EXIT REPEAT -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of randyleesmith Sent: Tuesday, March 04, 2014 1:34 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Left Outer Join Question It looks like the same thing. 4 lines long. : 0001: V : 0002: MERGE.LIST : 0003: I : 0004: GKSX Bottom at line 4. - 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-tp42740p42776.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
Using NSELECT works as long as I don't do a complicated statement with it. NSELECT OLDMAS WITH CO = SF AND WITH TRWSTATUS AND WITH ACTIONCODE NE 99 ... Unknown word WITH on command line. - 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-tp42740p42778.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
For all my testing I've been doing from TCL. Looks like I may need to do this in a PROC and not execute them in Unibasic code. Still this information maybe useful for the next time when I need to do it this way. - 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-tp42740p42779.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
No you can't do that. You do select this, select that, select this, nselect that. When you are all DONE, you can then do another SELECT on the result. Nselect cannot take options like that But you can do a Select AFTER you've done the Nselect -Original Message- From: randyleesmith [via U2 (UniVerse UniData)] ml-node+s1073795n4277...@n5.nabble.com To: Will Johnson wjhon...@aol.com Sent: Tue, Mar 4, 2014 10:43 am Subject: Re: Left Outer Join Question Using NSELECT works as long as I don't do a complicated statement with it. NSELECT OLDMAS WITH CO = SF AND WITH TRWSTATUS AND WITH ACTIONCODE NE 99 ... Unknown word WITH on command line. 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-tp42740p42778.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-tp42740p42780.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
If I do; SELECT X NSELECT Y Won't that take all my duplicate keys out from Y when I only want selected ones taken out from Y? - 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-tp42740p42784.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
Randy: NSELECT is a not select. It doesn't use, as far as I know, any filters with it. Use the filters with the SELECT then use the NSELECT (then use more SELECTs with filters if you want). Bill - Original Message - *From:* randyleesm...@roadrunner.com *To:* u2-users@listserver.u2ug.org *Date:* 3/4/2014 10:43 AM *Subject:* Re: [U2] Left Outer Join Question Using NSELECT works as long as I don't do a complicated statement with it. NSELECT OLDMAS WITH CO = SF AND WITH TRWSTATUS AND WITH ACTIONCODE NE 99 ... Unknown word WITH on command line. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Left Outer Join Question
Explain this concern better, I don't understand it. -Original Message- From: randyleesmith [via U2 (UniVerse UniData)] ml-node+s1073795n42784...@n5.nabble.com To: Will Johnson wjhon...@aol.com Sent: Tue, Mar 4, 2014 11:07 am Subject: Re: Left Outer Join Question If I do; SELECT X NSELECT Y Won't that take all my duplicate keys out from Y when I only want selected ones taken out from Y? 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-tp42740p42784.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-tp42740p42786.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
NO SELECT X NSELECT Y Will return all items in X that are NOT in Y Otherwise it will Exclude Y from X's List That is what your looking for? George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of randyleesmith Sent: Tuesday, March 04, 2014 2:07 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Left Outer Join Question If I do; SELECT X NSELECT Y Won't that take all my duplicate keys out from Y when I only want selected ones taken out from Y? - 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-tp42740p42784.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
NSELECT gets the items in the list, created by SELECT X, that do not exist in Y. Consequently, you can do things like: SELECT VENDORS WITH LAST USED 01/01/13 INVOICEKEY xxx records selected to list 0 NSELECT INVOICES ...where INVOICEKEY is the field that produces the record keys into the INVOICES file. Duplicate keys are a different story. If you want the list to be duplicate free then you need to use the SAVING UNIQUE (that's UniData but I think it's also UniVerse) keyword at the end of the SELECT statement. e.g. SELECT VENDORS WITH LAST USED 01/01/13 INVOICEKEY xxx records selected to list 0 SELECT INVOICES SAVING UNIQUE @ID HTH, Bill - Original Message - *From:* randyleesm...@roadrunner.com *To:* u2-users@listserver.u2ug.org *Date:* 3/4/2014 11:07 AM *Subject:* Re: [U2] Left Outer Join Question If I do; SELECT X NSELECT Y Won't that take all my duplicate keys out from Y when I only want selected ones taken out from Y? ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Left Outer Join Question
Also keep in mind that MERGE.LIST (if you can get it to work) will also Remove Duplicate Keys George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Bill Haskett Sent: Tuesday, March 04, 2014 2:22 PM To: U2 Users List Subject: Re: [U2] Left Outer Join Question NSELECT gets the items in the list, created by SELECT X, that do not exist in Y. Consequently, you can do things like: SELECT VENDORS WITH LAST USED 01/01/13 INVOICEKEY xxx records selected to list 0 NSELECT INVOICES ...where INVOICEKEY is the field that produces the record keys into the INVOICES file. Duplicate keys are a different story. If you want the list to be duplicate free then you need to use the SAVING UNIQUE (that's UniData but I think it's also UniVerse) keyword at the end of the SELECT statement. e.g. SELECT VENDORS WITH LAST USED 01/01/13 INVOICEKEY xxx records selected to list 0 SELECT INVOICES SAVING UNIQUE @ID HTH, Bill - Original Message - *From:* randyleesm...@roadrunner.com *To:* u2-users@listserver.u2ug.org *Date:* 3/4/2014 11:07 AM *Subject:* Re: [U2] Left Outer Join Question If I do; SELECT X NSELECT Y Won't that take all my duplicate keys out from Y when I only want selected ones taken out from Y? ___ 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
Ok, I see. If I just keep doing a Select I can keep trimming down the active select list as long as the keys are the same. (And I use the right logic. What I want to keep vs what I don't want.) Sorry, I got caught up in lists and the like. - 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-tp42740p42790.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
Which was what I was looking for. Thanks. - 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-tp42740p42791.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
Yes, that's what I meant. I was hoping to take out selected items in Y from X. I was trying to use it in another way to control it more. I realize now I can do that with the active select list using multiple selects as long as I use the right logic to filter out what I want vs. what I don't want. As it turns out I can use the SELECT X and NSELECT Y to get what I need by using the right combo of files. 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-tp42740p42792.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
Randy, It sounds like NSELECT is what you need. But MERGE.LIST is a handy thing to get working. I use it all the time, especially when researching bugs or bad data. What happens if you do other commands manipulating active select list 1? SELECT OLDMAS TO 1 SAVE.LIST X FROM 1 ? or LIST OLDMAS FROM 1 ? You say you're in Pick-flavor. Look for 1, 2, 3 in the VOC. There may be A-items defined there. They may conflict with parsing the command. 0 thru 11 actually. If there are, they should be moved to DICT.DICT which, unlike true Pick, is UV's master dictionary of dictionaries. While you're at it, for any and all of the words used in the command line compare your VOC to what is in NEWACC (the file they build VOCs for new accounts). Particularly TO FROM. I have seen some commands fail, but others work fine when those are not exactly as NEWACC says they should be. Chuck On 3/4/2014 3:39 PM, randyleesmith wrote: I tried the simplest way and still could not get it to work. SF-MORTSELECT OLDMAS TO 1 5247 record(s) selected to SELECT list #1. SF-MORTSELECT TRWMONTHEND TO 2 5229 record(s) selected to SELECT list #2. SF-MORTMERGE.LIST 1 INTERSECT 2 TO 3 SELECT list 1 is not active. SF-MORTMERGE.LIST 1 INTERSECT 2 SELECT list 1 is not active. I even tried the lowercase select and got the same result. Our version is 10.2.7 and we are running in PICK PICK.FORMAT. We are running a number of older applications and Proc's. So it may because of the Version I'm using. : 0001: X : 0002: 10.2.7 : 0003: PICK : 0004: PICK.FORMAT : 0005: 10.2.7 - 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-tp42740p42772.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
I Use MERGE.LIST a lot. Say for instance you want to know how many emails from an excel file are In a customer file. If you sort the excel file by email, then click on the excel column header, right click and copy Then go to UV, EDIT-LIST EM1, IENTER Then paste the clipboard and File that SELECT CUSTOMERFILE SAVING UNIQUE EMAIL SAVE-LIST EM2 GET-LIST EM1 TO 1 GET-LIST EM2 TO 2 MERGE.LIST 1 DIFF 2 SAVE-LIST EMAILS-NOT-IN-CUSTOMER-FILE GET-LIST EM1 TO 1 GET-LIST EM2 TO 2 MERGE.LIST 2 DIFF 1 SAVE-LIST EMAILS-NOT-IN-EXCEL-FILE And so on -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charles Stevenson Sent: Tuesday, March 04, 2014 4:24 PM To: U2 Users List Subject: Re: [U2] Left Outer Join Question Randy, It sounds like NSELECT is what you need. But MERGE.LIST is a handy thing to get working. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
[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] 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] 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] 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] 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
