[U2] Left Outer Join Question

2014-03-03 Thread randyleesmith
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

2014-03-03 Thread Will Johnson

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

2014-03-03 Thread McGowan, Ian
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

2014-03-03 Thread Dave Davis
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

2014-03-03 Thread George Gallen
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

2014-03-03 Thread Bill Haskett

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

2014-03-03 Thread randyleesmith
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

2014-03-03 Thread McGowan, Ian
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

2014-03-03 Thread randyleesmith
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

2014-03-03 Thread Brian Leach
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

2014-03-03 Thread jeffrey Butera

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

2014-03-03 Thread Jeff Schasny

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

2014-03-03 Thread jeffrey Butera

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

2014-03-03 Thread Anthonys Lists

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

2014-03-03 Thread Rutherford, Marc
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

2014-03-03 Thread Daniel McGrath
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

2014-03-03 Thread Bernard Lubin
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

2014-03-03 Thread jeffrey Butera

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

2014-03-03 Thread Rotman, David
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

2014-03-03 Thread McGowan, Ian
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

2014-03-03 Thread Wjhonson

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

2014-03-03 Thread Bill Haskett

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

2014-03-03 Thread randyleesmith
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

2014-03-03 Thread Baakkonen, Rodney A (Rod) 46K
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

2014-03-03 Thread Bill Haskett
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

2014-03-03 Thread David A. Green
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

2014-03-03 Thread Bernard Lubin
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

2014-03-03 Thread randyleesmith
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

2014-03-03 Thread larryh
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

2014-03-03 Thread Gregor Scott
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

2014-03-03 Thread McGowan, Ian
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

2014-03-03 Thread Mark Eastwood
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