date:20130827

Re: [U2] Select from one file with save-list from another file

2013-08-27 Thread Charles Stevenson


I'm with Ian.
Maybe Ian and I don't understand the question.   The other answers seem 
so complicated.


Brandon, Replace Ian's example "Y.PACKET.ID" with whatever is thereal 
name in DICT X.INFO  pointing to  what you describa ase Y.INFO's key 
exists as a "foreign key" somewhere in X.INFO.


Give Ian's code a try and see if it gives you what you want.
You don't even need to do the SAVE.LIST & GET.LIST.  That's just so you 
can have the list for later use or examination.


Chuck


On 8/28/2013 1:32 AM, McGowan, Ian wrote:

At least on Unidata you can put the foreign key in the select statement, and 
that's the id that will be used:

#SELECT X WITH STATUS = "ccc" Y.PACKET.ID
#SAVE.LIST L1
#GET.LIST L1
#LIST Y

If you put two atb names in the select, they are interleaved in the resulting 
saved list, which can be a quick way to export some data...

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Hilk, Brandon
Sent: Tuesday, August 27, 2013 1:24 PM
To: u2-users@listserver.u2ug.org
Subject: [U2] Select from one file with save-list from another file

Hello!

  


UniVerse 10.1, HP/UX, Pick.

  


Is there a way I can build a save-list from a selection from one file X.INFO (where the 
record ID is an 8 digit number) and use that list to select from file Y.INFO (where that 
8 digit number is not the record ID but does exist as a "foreign key" elsewhere 
in the file). In SQL this would be considered a join and would look something like this:

  


select *

from X.INFO, Y.INFO

where X.INFO.packet.id=Y.INFO.packet.id

and X.INFO.status='ccc';

  


I can run this in TCL and have it return the results I want but can't build a 
save-list from it because I don't know the UniVerse/SQL syntax to do so.

  


So to summarize, is there a way to use a save-list built from X.INFO to make 
another save-list comprised of the record ID's from Y.INFO?

  


Thanks for any advice you can give.

  


___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users

Re: [U2] universe 11.1 triggers via indexing and @IDX.IOTYPE sample

2013-08-27 Thread Charles Stevenson


Thanks, Troy.
I was going to write that after I got to 11.1.
(The upgrade was scheduled for Q3, but I hear rumours it got bumped 
because salesmen just sold something we can't do - yet.  It pays the bills.)


Troy, I want to borrow your code,  but I want to include proper 
attribution in the program header.


Chuck

On 8/27/2013 7:02 PM, Buss, Troy (Contractor, Logitek Systems) wrote:

Regarding the recent discussion of using indexes as triggers, I use the 
following to update timestamps of record changes to a master item stats file 
that we use for controlling updates to a business warehouse (BW).   Without 
modifying hundreds of legacy programs to support proper timestamps in the 
primary records, I added the following indexed Idescriptors to files I need to 
track.   We are on universe 11.1.1 and this has been working well for over a 
year.

DICT AR-INVOICE INDEX..MODIFIEDZ
0001 I
0002 SUBR("ZIDESC.ITEMSTATS.UPDATE", "AR-INVOICE!":F0,"")
0004 \
0005 1L
0006 S
0049 used to create item created, modified, delete in zitemstats file - create 
index with this and no.nulls keyword
EOI 216 bytes

In this example, we have a AR-INVOICE header record and a related ARLI line item file.   
For our purposes to keep in sync with the BW, we want to timestamp the corresponding 
header record whenever a line item is updated so the ARLI dict item also updates 
AR-INVOICE header ZITEMSTATS with an "I" indirect update flag.

DICT ARLI INDEX..MODIFIEDZ
0001 I
0002 SUBR("ZIDESC.ITEMSTATS.UPDATE", "ARLI!":F0,""):SUBR("ZIDESC.ITEMSTATS.UPDATE", 
"AR-INVOICE!":F1,"I")
0004 \
0005 1L
0006 S
0049 used to create item created, modified, delete in zitemstats file - create 
index with this and no.nulls keyword
EOI 264 bytes

Alternate Key Index Summary for file ARLI
File... ARLI
Indices 3 (0 A-type, 0 C-type, 0 D-type, 3 I-type, 0 SQL, 0 S-type)
Index Updates.. Enabled, No updates pending

Index name  Type  BuildNulls  In DICT  S/M  Just Unique Field num/I-type
INDEX..MODIFIEDZ INot Reqd  No Yes  SL NSUBR("ZIDESC.ITE
 MSTATS.UPDATE",
 
"ARLI!":F0,""):S
 
UBR("ZIDESC.ITEM
 STATS.UPDATE", 
"
 
AR-INVOICE!":F1,
 "I")


Subroutine ZIDESC.ITEMSTATS.UPDATE:

0001 SUB (RETURN.VALUE, ZITEMSTATS.ID, OPTIONS)
0002 * updates zitemstats with timestamps; create, modified, deleted
0003 *
0004 * Revision history:
0005 *
0006 * tdb.1190 2012-04-22_1602 new
0007 *
0008 *
0009 * input:  ZITEMSTATS.ID item id to use for ZITEMSTATS file
0010 * OPTIONS
0011 *I  update attb 5 with "I" for indirect 
reference
0012 *
0013 * output: RETURN.VALUE  NULL
0014 *
0015 * common vars: /ZITEMSTATS/
0016 *
0017 * called by:  INDEX..MODIFIEDZ dictionary items
0018 * calls:
0019 *
0020 * notes:
0021 *
0022 *
0023 $OPTIONS TIME.MILLISECOND
0024 *
0025 * @idx.iotype = 0 - Not being used from an index;
0026 * 1 - INSERT (new) record;
0027 * 2 - DELETE record;
0028 * 3 - Derive old index value;
0029 * 4 - Derive new index value;
0030 *
0031 COMMON /ZITEMSTATS/ F.ZITEMSTATS, ZITEMSTATS.FLAG.OPEN, ZITEMSTATS.WHO
0032 *
0033 EQU NUL TO ""
0034 *
0035 RETURN.VALUE = NUL
0036 *
0037 * the following handles initial call and logto commands
0038 *
0039 IF NOT(ZITEMSTATS.FLAG.OPEN) OR (@WHO # ZITEMSTATS.WHO) THEN
0040   OPEN "ZITEMSTATS" TO F.ZITEMSTATS ELSE
0041 RETURN ;*  to caller.
0042   END
0043 *
0044   ZITEMSTATS.FLAG.OPEN = 1
0045   ZITEMSTATS.WHO   = @WHO
0046 END
0047 *
0048 LOG.TYPE = NUL
0049 *
0050 BEGIN CASE
0051   CASE @IDX.IOTYPE = 0
0052 *
0053   CASE @IDX.IOTYPE = 1
0054 LOG.TYPE = "C" ;* new - created - easier to see than 'N' new
0055 *
0056   CASE @IDX.IOTYPE = 2
0057 LOG.TYPE = "D" ;* delete
0058 *
0059   CASE @IDX.IOTYPE = 3
0060 *
0061   CASE @IDX.IOTYPE = 4
0062 LOG.TYPE = "M" ;* modified
0063 *
0064 END CASE
0065 *
0066 IF LOG.TYPE # NUL THEN
0067 *
0068   DATE.NOW = DATE()
0069   TIME.NOW = TIME()
0070 *
0071 * handle instance of midnight between date/time functions
0072 *
0073   IF DATE.NOW # DATE() AND TIME() > TIME.NOW THEN DATE.NOW = DATE()
0074 *
0075   TIMESTAMP = DATE.NOW * 86400 + TIME.NOW
0076 *
0077   READU ZITEMSTATS$ FROM F.ZITEMSTATS, ZITEMSTATS.ID ELSE ZITEMSTATS$ = NUL
0078 *
0079   INDIRECT.FLAG = COUNT(OPTIONS, "I")
0080 *
0081   IF INDIRECT.FLAG THEN
0082 ZITEMSTATS$<1> = "M" ;* modified
0083   END ELSE
0084 ZITEMSTATS$<1> = LOG.TYPE
0085   END
0086 *
0087 * created
0088 *
0089   IF INDIRECT.FLAG ELSE
0090 IF LOG.TYPE = "C" THEN
0091   ZITEMSTATS$<2> = TIMESTAMP
0092 END
0093   END
0094 *
0095 * modified
0096 *
0097   ZI

Re: [U2] Select from one file with save-list from another file

2013-08-27 Thread Allen Elwood (TW)



i like temp files properly sized, temp dicts, one simple statement 
invoking RSELECT, no possibility of syntax goofs


simple

easy

fun

On 8/27/2013 5:37 PM, Wjhonson wrote:

Are you implying that it somehow reads the permissions of the dictionary before 
allowing the use of EVAL?


  

  

  


-Original Message-
From: Wols Lists 
To: u2-users 
Sent: Tue, Aug 27, 2013 5:30 pm
Subject: Re: [U2] Select from one file with save-list from another file


On 28/08/13 01:20, Wjhonson wrote:

Yes I think you could use an On The Fly statement like the EVAL to do the

trans if you can't actually mod the dictionary
OOPS!!!

I know this is UD, but in UV you couldn't do "on the fly" like EVAL if
you couldn't mod the dictionary.

Whether that was a security feature and thus is still true, or it just
made temporary changes to the dictionary and may have changed, I don't know.

But if UD is the same, if you can't mod the dictionary you can't do an EVAL.

Cheers,
Wol



___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users

Re: [U2] Select from one file with save-list from another file

2013-08-27 Thread Richard Lewis

Yes, because (at least on UV) it actually writes a temporary record to the
dictionary to perform the EVAL expression.

On Tue, Aug 27, 2013 at 6:37 PM, Wjhonson  wrote:

> Are you implying that it somehow reads the permissions of the dictionary
> before allowing the use of EVAL?
>
>
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users

Re: [U2] Select from one file with save-list from another file

2013-08-27 Thread Wjhonson

Are you implying that it somehow reads the permissions of the dictionary before 
allowing the use of EVAL?


 

 

 

-Original Message-
From: Wols Lists 
To: u2-users 
Sent: Tue, Aug 27, 2013 5:30 pm
Subject: Re: [U2] Select from one file with save-list from another file


On 28/08/13 01:20, Wjhonson wrote:
> Yes I think you could use an On The Fly statement like the EVAL to do the 
trans if you can't actually mod the dictionary
> 
OOPS!!!

I know this is UD, but in UV you couldn't do "on the fly" like EVAL if
you couldn't mod the dictionary.

Whether that was a security feature and thus is still true, or it just
made temporary changes to the dictionary and may have changed, I don't know.

But if UD is the same, if you can't mod the dictionary you can't do an EVAL.

Cheers,
Wol

-- 
> 
> -Original Message-
> From: Allen Elwood (TW) 
> To: U2 Users List 
> Sent: Tue, Aug 27, 2013 5:13 pm
> Subject: Re: [U2] Select from one file with save-list from another file
> 
> 
> 
> forgot to mention, david green knows a way of doing this with some fancy 
> statements, i'm sure he'll chime in when he sees the thread, so don't 
> give up hope yet brandon!
> 
> On 8/27/2013 5:06 PM, Allen Elwood (TW) wrote:
>> that's basically what my program does on the fly, see fourth gosub
>>
>>   GOSUB GET.OPTIONS
>>   GOSUB DETERMINE.TEMP.FILESIZE
>>   GOSUB LOAD.TEMP.FILE
>>   GOSUB CREATE.TEMP.DICT
>>   GOSUB BUILD.LIST2
>>   GOSUB FINISH.UP
>>
>> On 8/27/2013 4:03 PM, Wjhonson wrote:
>>> It seems to me that the easiest way to create a "list of orders where 
>>> the customers state is Arizona"
>>> is to first create a translation FROM the Order file INTO the 
>>> Customer file which will return the customers state
>>> and then just select on that new dictionary entry
>>>
>>>
>>>
>>>
>>>
>>> -Original Message-
>>> From: Rick Nuckolls 
>>> To: 'U2 Users List' 
>>> Sent: Tue, Aug 27, 2013 4:01 pm
>>> Subject: Re: [U2] Select from one file with save-list from another file
>>>
>>>
>>> Your response supposes that the ORDER.ID IS is in the CUSTOMERS 
>>> file/table,
>>> which is not the criteria of the original problem.
>>>
>>> The problem presented is a bit difficult to work around in RETRIEVE, 
>>> and even
>>> harder if you are restricted to not programming, as Brandon evidently 
>>> is.
>>>
>>> We wrote a utility that will take a preselected list and create a new 
>>> list based
>>> on the values of an alternate key, which would functions as
>>>
>>> SELECT CUSTOMERS WITH STATE = 'AZ'
>>>
>>> SELECTINDEX ORDERS CUSTOMER.ID ## use the CUSTOMER.ID index of 
>>> ORDERS to
>>> find those orders related to customers within the list; return a list 
>>> of ORDERS
>>> ids.
>>>
>>> Alternately, in Brandon's example, it might work to create a 
>>> translate field
>>> from the Y.INFO file to the Y.INFO status field, but that would 
>>> probably involve
>>> a less efficient overall selection, and would not be practical if the 
>>> data
>>> common to the two tables (packet.id), is the primary key to neither, 
>>> in which
>>> case:
>>>
>>> SELECT X.INFO WITH status = 'ccc' SAVING [UNIQUE] packet.id
>>>
>>> SELECTINDEX Y.INFO packet.id
>>>
>>> Sorry that I am unable to send out the code for SELECTINDEX, but, a 
>>> simple
>>> version is straightforward to program using the BASIC "SELECTINDEX" 
>>> function.
>>>
>>> -Rick
>>>
>>> -Original Message-
>>> From: u2-users-boun...@listserver.u2ug.org 
>>> [mailto:u2-users-boun...@listserver.u2ug.org]
>>> On Behalf Of Jo Lester
>>> Sent: Tuesday, August 27, 2013 2:54 PM
>>> To: u2-users@listserver.u2ug.org
>>> Subject: Re: [U2] Select from one file with save-list from another file
>>>
>>> SELECT CUSTOMERS WITH STATE = 'AZ' SAVING ORDER.ID
>>>
>>> LIST ORDERS
>>>
 From: r...@lynden.com
 To: u2-users@listserver.u2ug.org
 Date: Tue, 27 Aug 2013 14:42:34 -0700
 Subject: Re: [U2] Select from one file with save-list from another file

 I found an old example on line that might get you started

 SELECT TO SLIST 0 FROM CUSTOMERS WHERE STATE = 'AZ';
 SAVE.LIST CUSTOMER.LIST

 SELECT * FROM ORDERS WHERE CUSTOMER.ID IN (SELECT EVAL "  
 RECORD" FROM
 &SAVEDLISTS& 'CUSTOMER.LIST');

 The extra syntax that might help is

 SELECT TO SLIST  FROM ORDERS 

 If CUSTOMER.LIST is your X.INFO and ORDERS is your Y.INFO...

 Rick

 -Original Message-
 From: u2-users-boun...@listserver.u2ug.org 
 [mailto:u2-users-boun...@listserver.u2ug.org]
>>> On Behalf Of Hilk, Brandon
 Sent: Tuesday, August 27, 2013 1:24 PM
 To: u2-users@listserver.u2ug.org
 Subject: [U2] Select from one file with save-list from another file

 Hello!


 UniVerse 10.1, HP/UX, Pick.


 Is there a way I can build a save-list from a selection from one file
 X.INFO (where the record ID is an 8 digit number) and use that list to
 select from file Y.INFO (where that 8

Re: [U2] Select from one file with save-list from another file

2013-08-27 Thread Wols Lists

On 28/08/13 01:20, Wjhonson wrote:
> Yes I think you could use an On The Fly statement like the EVAL to do the 
> trans if you can't actually mod the dictionary
> 
OOPS!!!

I know this is UD, but in UV you couldn't do "on the fly" like EVAL if
you couldn't mod the dictionary.

Whether that was a security feature and thus is still true, or it just
made temporary changes to the dictionary and may have changed, I don't know.

But if UD is the same, if you can't mod the dictionary you can't do an EVAL.

Cheers,
Wol

-- 
> 
> -Original Message-
> From: Allen Elwood (TW) 
> To: U2 Users List 
> Sent: Tue, Aug 27, 2013 5:13 pm
> Subject: Re: [U2] Select from one file with save-list from another file
> 
> 
> 
> forgot to mention, david green knows a way of doing this with some fancy 
> statements, i'm sure he'll chime in when he sees the thread, so don't 
> give up hope yet brandon!
> 
> On 8/27/2013 5:06 PM, Allen Elwood (TW) wrote:
>> that's basically what my program does on the fly, see fourth gosub
>>
>>   GOSUB GET.OPTIONS
>>   GOSUB DETERMINE.TEMP.FILESIZE
>>   GOSUB LOAD.TEMP.FILE
>>   GOSUB CREATE.TEMP.DICT
>>   GOSUB BUILD.LIST2
>>   GOSUB FINISH.UP
>>
>> On 8/27/2013 4:03 PM, Wjhonson wrote:
>>> It seems to me that the easiest way to create a "list of orders where 
>>> the customers state is Arizona"
>>> is to first create a translation FROM the Order file INTO the 
>>> Customer file which will return the customers state
>>> and then just select on that new dictionary entry
>>>
>>>
>>>
>>>
>>>
>>> -Original Message-
>>> From: Rick Nuckolls 
>>> To: 'U2 Users List' 
>>> Sent: Tue, Aug 27, 2013 4:01 pm
>>> Subject: Re: [U2] Select from one file with save-list from another file
>>>
>>>
>>> Your response supposes that the ORDER.ID IS is in the CUSTOMERS 
>>> file/table,
>>> which is not the criteria of the original problem.
>>>
>>> The problem presented is a bit difficult to work around in RETRIEVE, 
>>> and even
>>> harder if you are restricted to not programming, as Brandon evidently 
>>> is.
>>>
>>> We wrote a utility that will take a preselected list and create a new 
>>> list based
>>> on the values of an alternate key, which would functions as
>>>
>>> SELECT CUSTOMERS WITH STATE = 'AZ'
>>>
>>> SELECTINDEX ORDERS CUSTOMER.ID ## use the CUSTOMER.ID index of 
>>> ORDERS to
>>> find those orders related to customers within the list; return a list 
>>> of ORDERS
>>> ids.
>>>
>>> Alternately, in Brandon's example, it might work to create a 
>>> translate field
>>> from the Y.INFO file to the Y.INFO status field, but that would 
>>> probably involve
>>> a less efficient overall selection, and would not be practical if the 
>>> data
>>> common to the two tables (packet.id), is the primary key to neither, 
>>> in which
>>> case:
>>>
>>> SELECT X.INFO WITH status = 'ccc' SAVING [UNIQUE] packet.id
>>>
>>> SELECTINDEX Y.INFO packet.id
>>>
>>> Sorry that I am unable to send out the code for SELECTINDEX, but, a 
>>> simple
>>> version is straightforward to program using the BASIC "SELECTINDEX" 
>>> function.
>>>
>>> -Rick
>>>
>>> -Original Message-
>>> From: u2-users-boun...@listserver.u2ug.org 
>>> [mailto:u2-users-boun...@listserver.u2ug.org]
>>> On Behalf Of Jo Lester
>>> Sent: Tuesday, August 27, 2013 2:54 PM
>>> To: u2-users@listserver.u2ug.org
>>> Subject: Re: [U2] Select from one file with save-list from another file
>>>
>>> SELECT CUSTOMERS WITH STATE = 'AZ' SAVING ORDER.ID
>>>
>>> LIST ORDERS
>>>
 From: r...@lynden.com
 To: u2-users@listserver.u2ug.org
 Date: Tue, 27 Aug 2013 14:42:34 -0700
 Subject: Re: [U2] Select from one file with save-list from another file

 I found an old example on line that might get you started

 SELECT TO SLIST 0 FROM CUSTOMERS WHERE STATE = 'AZ';
 SAVE.LIST CUSTOMER.LIST

 SELECT * FROM ORDERS WHERE CUSTOMER.ID IN (SELECT EVAL "  
 RECORD" FROM
 &SAVEDLISTS& 'CUSTOMER.LIST');

 The extra syntax that might help is

 SELECT TO SLIST  FROM ORDERS 

 If CUSTOMER.LIST is your X.INFO and ORDERS is your Y.INFO...

 Rick

 -Original Message-
 From: u2-users-boun...@listserver.u2ug.org 
 [mailto:u2-users-boun...@listserver.u2ug.org]
>>> On Behalf Of Hilk, Brandon
 Sent: Tuesday, August 27, 2013 1:24 PM
 To: u2-users@listserver.u2ug.org
 Subject: [U2] Select from one file with save-list from another file

 Hello!


 UniVerse 10.1, HP/UX, Pick.


 Is there a way I can build a save-list from a selection from one file
 X.INFO (where the record ID is an 8 digit number) and use that list to
 select from file Y.INFO (where that 8 digit number is not the record ID
 but does exist as a "foreign key" elsewhere in the file). In SQL this
 would be considered a join and would look something like this:


 select *

 from X.INFO, Y.INFO

 where X.INFO.packet.id=Y.INFO.packet.id

Re: [U2] Select from one file with save-list from another file

2013-08-27 Thread Allen Elwood (TW)



forgot to mention, david green knows a way of doing this with some fancy 
statements, i'm sure he'll chime in when he sees the thread, so don't 
give up hope yet brandon!


On 8/27/2013 5:06 PM, Allen Elwood (TW) wrote:

that's basically what my program does on the fly, see fourth gosub

  GOSUB GET.OPTIONS
  GOSUB DETERMINE.TEMP.FILESIZE
  GOSUB LOAD.TEMP.FILE
  GOSUB CREATE.TEMP.DICT
  GOSUB BUILD.LIST2
  GOSUB FINISH.UP

On 8/27/2013 4:03 PM, Wjhonson wrote:
It seems to me that the easiest way to create a "list of orders where 
the customers state is Arizona"
is to first create a translation FROM the Order file INTO the 
Customer file which will return the customers state

and then just select on that new dictionary entry





-Original Message-
From: Rick Nuckolls 
To: 'U2 Users List' 
Sent: Tue, Aug 27, 2013 4:01 pm
Subject: Re: [U2] Select from one file with save-list from another file


Your response supposes that the ORDER.ID IS is in the CUSTOMERS 
file/table,

which is not the criteria of the original problem.

The problem presented is a bit difficult to work around in RETRIEVE, 
and even
harder if you are restricted to not programming, as Brandon evidently 
is.


We wrote a utility that will take a preselected list and create a new 
list based

on the values of an alternate key, which would functions as

SELECT CUSTOMERS WITH STATE = 'AZ'

SELECTINDEX ORDERS CUSTOMER.ID ## use the CUSTOMER.ID index of 
ORDERS to
find those orders related to customers within the list; return a list 
of ORDERS

ids.

Alternately, in Brandon's example, it might work to create a 
translate field
from the Y.INFO file to the Y.INFO status field, but that would 
probably involve
a less efficient overall selection, and would not be practical if the 
data
common to the two tables (packet.id), is the primary key to neither, 
in which

case:

SELECT X.INFO WITH status = 'ccc' SAVING [UNIQUE] packet.id

SELECTINDEX Y.INFO packet.id

Sorry that I am unable to send out the code for SELECTINDEX, but, a 
simple
version is straightforward to program using the BASIC "SELECTINDEX" 
function.


-Rick

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org]

On Behalf Of Jo Lester
Sent: Tuesday, August 27, 2013 2:54 PM
To: u2-users@listserver.u2ug.org
Subject: Re: [U2] Select from one file with save-list from another file

SELECT CUSTOMERS WITH STATE = 'AZ' SAVING ORDER.ID

LIST ORDERS


From: r...@lynden.com
To: u2-users@listserver.u2ug.org
Date: Tue, 27 Aug 2013 14:42:34 -0700
Subject: Re: [U2] Select from one file with save-list from another file

I found an old example on line that might get you started

SELECT TO SLIST 0 FROM CUSTOMERS WHERE STATE = 'AZ';
SAVE.LIST CUSTOMER.LIST

SELECT * FROM ORDERS WHERE CUSTOMER.ID IN (SELECT EVAL "  
RECORD" FROM

&SAVEDLISTS& 'CUSTOMER.LIST');

The extra syntax that might help is

SELECT TO SLIST  FROM ORDERS 

If CUSTOMER.LIST is your X.INFO and ORDERS is your Y.INFO...

Rick

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org]

On Behalf Of Hilk, Brandon

Sent: Tuesday, August 27, 2013 1:24 PM
To: u2-users@listserver.u2ug.org
Subject: [U2] Select from one file with save-list from another file

Hello!


UniVerse 10.1, HP/UX, Pick.


Is there a way I can build a save-list from a selection from one file
X.INFO (where the record ID is an 8 digit number) and use that list to
select from file Y.INFO (where that 8 digit number is not the record ID
but does exist as a "foreign key" elsewhere in the file). In SQL this
would be considered a join and would look something like this:


select *

from X.INFO, Y.INFO

where X.INFO.packet.id=Y.INFO.packet.id

and X.INFO.status='ccc';


I can run this in TCL and have it return the results I want but can't
build a save-list from it because I don't know the UniVerse/SQL syntax
to do so.


So to summarize, is there a way to use a save-list built from X.INFO to
make another save-list comprised of the record ID's from Y.INFO?


Thanks for any advice you can give.


___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users


___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users

  ___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users



___
U2-Users mailing list
U2-Users@listserver.u2ug.org
ht

Re: [U2] Select from one file with save-list from another file

2013-08-27 Thread Wjhonson

Yes I think you could use an On The Fly statement like the EVAL to do the trans 
if you can't actually mod the dictionary

 

 

 

-Original Message-
From: Allen Elwood (TW) 
To: U2 Users List 
Sent: Tue, Aug 27, 2013 5:13 pm
Subject: Re: [U2] Select from one file with save-list from another file



forgot to mention, david green knows a way of doing this with some fancy 
statements, i'm sure he'll chime in when he sees the thread, so don't 
give up hope yet brandon!

On 8/27/2013 5:06 PM, Allen Elwood (TW) wrote:
> that's basically what my program does on the fly, see fourth gosub
>
>   GOSUB GET.OPTIONS
>   GOSUB DETERMINE.TEMP.FILESIZE
>   GOSUB LOAD.TEMP.FILE
>   GOSUB CREATE.TEMP.DICT
>   GOSUB BUILD.LIST2
>   GOSUB FINISH.UP
>
> On 8/27/2013 4:03 PM, Wjhonson wrote:
>> It seems to me that the easiest way to create a "list of orders where 
>> the customers state is Arizona"
>> is to first create a translation FROM the Order file INTO the 
>> Customer file which will return the customers state
>> and then just select on that new dictionary entry
>>
>>
>>
>>
>>
>> -Original Message-
>> From: Rick Nuckolls 
>> To: 'U2 Users List' 
>> Sent: Tue, Aug 27, 2013 4:01 pm
>> Subject: Re: [U2] Select from one file with save-list from another file
>>
>>
>> Your response supposes that the ORDER.ID IS is in the CUSTOMERS 
>> file/table,
>> which is not the criteria of the original problem.
>>
>> The problem presented is a bit difficult to work around in RETRIEVE, 
>> and even
>> harder if you are restricted to not programming, as Brandon evidently 
>> is.
>>
>> We wrote a utility that will take a preselected list and create a new 
>> list based
>> on the values of an alternate key, which would functions as
>>
>> SELECT CUSTOMERS WITH STATE = 'AZ'
>>
>> SELECTINDEX ORDERS CUSTOMER.ID ## use the CUSTOMER.ID index of 
>> ORDERS to
>> find those orders related to customers within the list; return a list 
>> of ORDERS
>> ids.
>>
>> Alternately, in Brandon's example, it might work to create a 
>> translate field
>> from the Y.INFO file to the Y.INFO status field, but that would 
>> probably involve
>> a less efficient overall selection, and would not be practical if the 
>> data
>> common to the two tables (packet.id), is the primary key to neither, 
>> in which
>> case:
>>
>> SELECT X.INFO WITH status = 'ccc' SAVING [UNIQUE] packet.id
>>
>> SELECTINDEX Y.INFO packet.id
>>
>> Sorry that I am unable to send out the code for SELECTINDEX, but, a 
>> simple
>> version is straightforward to program using the BASIC "SELECTINDEX" 
>> function.
>>
>> -Rick
>>
>> -Original Message-
>> From: u2-users-boun...@listserver.u2ug.org 
>> [mailto:u2-users-boun...@listserver.u2ug.org]
>> On Behalf Of Jo Lester
>> Sent: Tuesday, August 27, 2013 2:54 PM
>> To: u2-users@listserver.u2ug.org
>> Subject: Re: [U2] Select from one file with save-list from another file
>>
>> SELECT CUSTOMERS WITH STATE = 'AZ' SAVING ORDER.ID
>>
>> LIST ORDERS
>>
>>> From: r...@lynden.com
>>> To: u2-users@listserver.u2ug.org
>>> Date: Tue, 27 Aug 2013 14:42:34 -0700
>>> Subject: Re: [U2] Select from one file with save-list from another file
>>>
>>> I found an old example on line that might get you started
>>>
>>> SELECT TO SLIST 0 FROM CUSTOMERS WHERE STATE = 'AZ';
>>> SAVE.LIST CUSTOMER.LIST
>>>
>>> SELECT * FROM ORDERS WHERE CUSTOMER.ID IN (SELECT EVAL "  
>>> RECORD" FROM
>>> &SAVEDLISTS& 'CUSTOMER.LIST');
>>>
>>> The extra syntax that might help is
>>>
>>> SELECT TO SLIST  FROM ORDERS 
>>>
>>> If CUSTOMER.LIST is your X.INFO and ORDERS is your Y.INFO...
>>>
>>> Rick
>>>
>>> -Original Message-
>>> From: u2-users-boun...@listserver.u2ug.org 
>>> [mailto:u2-users-boun...@listserver.u2ug.org]
>> On Behalf Of Hilk, Brandon
>>> Sent: Tuesday, August 27, 2013 1:24 PM
>>> To: u2-users@listserver.u2ug.org
>>> Subject: [U2] Select from one file with save-list from another file
>>>
>>> Hello!
>>>
>>>
>>> UniVerse 10.1, HP/UX, Pick.
>>>
>>>
>>> Is there a way I can build a save-list from a selection from one file
>>> X.INFO (where the record ID is an 8 digit number) and use that list to
>>> select from file Y.INFO (where that 8 digit number is not the record ID
>>> but does exist as a "foreign key" elsewhere in the file). In SQL this
>>> would be considered a join and would look something like this:
>>>
>>>
>>> select *
>>>
>>> from X.INFO, Y.INFO
>>>
>>> where X.INFO.packet.id=Y.INFO.packet.id
>>>
>>> and X.INFO.status='ccc';
>>>
>>>
>>> I can run this in TCL and have it return the results I want but can't
>>> build a save-list from it because I don't know the UniVerse/SQL syntax
>>> to do so.
>>>
>>>
>>> So to summarize, is there a way to use a save-list built from X.INFO to
>>> make another save-list comprised of the record ID's from Y.INFO?
>>>
>>>
>>> Thanks for any advice you can give.
>>>
>>>
>>> ___
>>> U2-Users mailing list
>>> U2-Users@listserver.u2ug.org
>>> http://listserver.u2

Re: [U2] Select from one file with save-list from another file

2013-08-27 Thread Allen Elwood (TW)


that's basically what my program does on the fly, see fourth gosub

  GOSUB GET.OPTIONS
  GOSUB DETERMINE.TEMP.FILESIZE
  GOSUB LOAD.TEMP.FILE
  GOSUB CREATE.TEMP.DICT
  GOSUB BUILD.LIST2
  GOSUB FINISH.UP

On 8/27/2013 4:03 PM, Wjhonson wrote:

It seems to me that the easiest way to create a "list of orders where the customers 
state is Arizona"
is to first create a translation FROM the Order file INTO the Customer file 
which will return the customers state
and then just select on that new dictionary entry


  

  

  


-Original Message-
From: Rick Nuckolls 
To: 'U2 Users List' 
Sent: Tue, Aug 27, 2013 4:01 pm
Subject: Re: [U2] Select from one file with save-list from another file


Your response supposes that the ORDER.ID IS is in the CUSTOMERS file/table,
which is not the criteria of the original problem.

The problem presented is a bit difficult to work around in RETRIEVE, and even
harder if you are restricted to not programming, as Brandon evidently is.

We wrote a utility that will take a preselected list and create a new list based
on the values of an alternate key, which would functions as

SELECT CUSTOMERS WITH STATE = 'AZ'

SELECTINDEX ORDERS CUSTOMER.ID ## use the CUSTOMER.ID index of ORDERS to
find those orders related to customers within the list; return a list of ORDERS
ids.

Alternately, in Brandon's example, it might work to create a translate field
from the Y.INFO file to the Y.INFO status field, but that would probably involve
a less efficient overall selection, and would not be practical if the data
common to the two tables (packet.id), is the primary key to neither, in which
case:

SELECT X.INFO WITH status = 'ccc' SAVING [UNIQUE] packet.id

SELECTINDEX Y.INFO packet.id

Sorry that I am unable to send out the code for SELECTINDEX, but, a simple
version is straightforward to program using the BASIC "SELECTINDEX" function.

-Rick

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org]
On Behalf Of Jo Lester
Sent: Tuesday, August 27, 2013 2:54 PM
To: u2-users@listserver.u2ug.org
Subject: Re: [U2] Select from one file with save-list from another file

SELECT CUSTOMERS WITH STATE = 'AZ' SAVING ORDER.ID

LIST ORDERS


From: r...@lynden.com
To: u2-users@listserver.u2ug.org
Date: Tue, 27 Aug 2013 14:42:34 -0700
Subject: Re: [U2] Select from one file with save-list from another file

I found an old example on line that might get you started

SELECT TO SLIST 0 FROM CUSTOMERS WHERE STATE = 'AZ';
SAVE.LIST CUSTOMER.LIST

SELECT * FROM ORDERS WHERE CUSTOMER.ID IN (SELECT EVAL "  RECORD" FROM
&SAVEDLISTS& 'CUSTOMER.LIST');

The extra syntax that might help is

SELECT TO SLIST  FROM ORDERS 

If CUSTOMER.LIST is your X.INFO and ORDERS is your Y.INFO...

Rick

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org]

On Behalf Of Hilk, Brandon

Sent: Tuesday, August 27, 2013 1:24 PM
To: u2-users@listserver.u2ug.org
Subject: [U2] Select from one file with save-list from another file

Hello!

  


UniVerse 10.1, HP/UX, Pick.

  


Is there a way I can build a save-list from a selection from one file
X.INFO (where the record ID is an 8 digit number) and use that list to
select from file Y.INFO (where that 8 digit number is not the record ID
but does exist as a "foreign key" elsewhere in the file). In SQL this
would be considered a join and would look something like this:

  


select *

from X.INFO, Y.INFO

where X.INFO.packet.id=Y.INFO.packet.id

and X.INFO.status='ccc';

  


I can run this in TCL and have it return the results I want but can't
build a save-list from it because I don't know the UniVerse/SQL syntax
to do so.

  


So to summarize, is there a way to use a save-list built from X.INFO to
make another save-list comprised of the record ID's from Y.INFO?

  


Thanks for any advice you can give.

  


___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users


___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users

  
___

U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users



___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users

Re: [U2] Select from one file with save-list from another file

2013-08-27 Thread Wjhonson

It seems to me that the easiest way to create a "list of orders where the 
customers state is Arizona"
is to first create a translation FROM the Order file INTO the Customer file 
which will return the customers state
and then just select on that new dictionary entry


 

 

 

-Original Message-
From: Rick Nuckolls 
To: 'U2 Users List' 
Sent: Tue, Aug 27, 2013 4:01 pm
Subject: Re: [U2] Select from one file with save-list from another file


Your response supposes that the ORDER.ID IS is in the CUSTOMERS file/table, 
which is not the criteria of the original problem.

The problem presented is a bit difficult to work around in RETRIEVE, and even 
harder if you are restricted to not programming, as Brandon evidently is.

We wrote a utility that will take a preselected list and create a new list 
based 
on the values of an alternate key, which would functions as

SELECT CUSTOMERS WITH STATE = 'AZ'

SELECTINDEX ORDERS CUSTOMER.ID ## use the CUSTOMER.ID index of ORDERS to 
find those orders related to customers within the list; return a list of ORDERS 
ids.

Alternately, in Brandon's example, it might work to create a translate field 
from the Y.INFO file to the Y.INFO status field, but that would probably 
involve 
a less efficient overall selection, and would not be practical if the data 
common to the two tables (packet.id), is the primary key to neither, in which 
case:

SELECT X.INFO WITH status = 'ccc' SAVING [UNIQUE] packet.id

SELECTINDEX Y.INFO packet.id

Sorry that I am unable to send out the code for SELECTINDEX, but, a simple 
version is straightforward to program using the BASIC "SELECTINDEX" function.

-Rick

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org] 
On Behalf Of Jo Lester
Sent: Tuesday, August 27, 2013 2:54 PM
To: u2-users@listserver.u2ug.org
Subject: Re: [U2] Select from one file with save-list from another file

SELECT CUSTOMERS WITH STATE = 'AZ' SAVING ORDER.ID

LIST ORDERS

> From: r...@lynden.com
> To: u2-users@listserver.u2ug.org
> Date: Tue, 27 Aug 2013 14:42:34 -0700
> Subject: Re: [U2] Select from one file with save-list from another file
> 
> I found an old example on line that might get you started
> 
> SELECT TO SLIST 0 FROM CUSTOMERS WHERE STATE = 'AZ';
> SAVE.LIST CUSTOMER.LIST
> 
> SELECT * FROM ORDERS WHERE CUSTOMER.ID IN (SELECT EVAL "  RECORD" FROM
> &SAVEDLISTS& 'CUSTOMER.LIST');
> 
> The extra syntax that might help is
> 
> SELECT TO SLIST  FROM ORDERS 
> 
> If CUSTOMER.LIST is your X.INFO and ORDERS is your Y.INFO...
> 
> Rick
> 
> -Original Message-
> From: u2-users-boun...@listserver.u2ug.org 
> [mailto:u2-users-boun...@listserver.u2ug.org] 
On Behalf Of Hilk, Brandon
> Sent: Tuesday, August 27, 2013 1:24 PM
> To: u2-users@listserver.u2ug.org
> Subject: [U2] Select from one file with save-list from another file
> 
> Hello! 
> 
>  
> 
> UniVerse 10.1, HP/UX, Pick.
> 
>  
> 
> Is there a way I can build a save-list from a selection from one file
> X.INFO (where the record ID is an 8 digit number) and use that list to
> select from file Y.INFO (where that 8 digit number is not the record ID
> but does exist as a "foreign key" elsewhere in the file). In SQL this
> would be considered a join and would look something like this:
> 
>  
> 
> select *
> 
> from X.INFO, Y.INFO
> 
> where X.INFO.packet.id=Y.INFO.packet.id
> 
> and X.INFO.status='ccc';
> 
>  
> 
> I can run this in TCL and have it return the results I want but can't
> build a save-list from it because I don't know the UniVerse/SQL syntax
> to do so.
> 
>  
> 
> So to summarize, is there a way to use a save-list built from X.INFO to
> make another save-list comprised of the record ID's from Y.INFO?
> 
>  
> 
> Thanks for any advice you can give.
> 
>  
> 
> ___
> U2-Users mailing list
> U2-Users@listserver.u2ug.org
> http://listserver.u2ug.org/mailman/listinfo/u2-users
> ___
> U2-Users mailing list
> U2-Users@listserver.u2ug.org
> http://listserver.u2ug.org/mailman/listinfo/u2-users
  
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users

 
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users

Re: [U2] Select from one file with save-list from another file

2013-08-27 Thread Rick Nuckolls

Your response supposes that the ORDER.ID IS is in the CUSTOMERS file/table, 
which is not the criteria of the original problem.

The problem presented is a bit difficult to work around in RETRIEVE, and even 
harder if you are restricted to not programming, as Brandon evidently is.

We wrote a utility that will take a preselected list and create a new list 
based on the values of an alternate key, which would functions as

SELECT CUSTOMERS WITH STATE = 'AZ'

SELECTINDEX ORDERS CUSTOMER.ID ## use the CUSTOMER.ID index of ORDERS to 
find those orders related to customers within the list; return a list of ORDERS 
ids.

Alternately, in Brandon's example, it might work to create a translate field 
from the Y.INFO file to the Y.INFO status field, but that would probably 
involve a less efficient overall selection, and would not be practical if the 
data common to the two tables (packet.id), is the primary key to neither, in 
which case:

SELECT X.INFO WITH status = 'ccc' SAVING [UNIQUE] packet.id

SELECTINDEX Y.INFO packet.id

Sorry that I am unable to send out the code for SELECTINDEX, but, a simple 
version is straightforward to program using the BASIC "SELECTINDEX" function.

-Rick

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jo Lester
Sent: Tuesday, August 27, 2013 2:54 PM
To: u2-users@listserver.u2ug.org
Subject: Re: [U2] Select from one file with save-list from another file

SELECT CUSTOMERS WITH STATE = 'AZ' SAVING ORDER.ID

LIST ORDERS

> From: r...@lynden.com
> To: u2-users@listserver.u2ug.org
> Date: Tue, 27 Aug 2013 14:42:34 -0700
> Subject: Re: [U2] Select from one file with save-list from another file
> 
> I found an old example on line that might get you started
> 
> SELECT TO SLIST 0 FROM CUSTOMERS WHERE STATE = 'AZ';
> SAVE.LIST CUSTOMER.LIST
> 
> SELECT * FROM ORDERS WHERE CUSTOMER.ID IN (SELECT EVAL "  RECORD" FROM
> &SAVEDLISTS& 'CUSTOMER.LIST');
> 
> The extra syntax that might help is
> 
> SELECT TO SLIST  FROM ORDERS 
> 
> If CUSTOMER.LIST is your X.INFO and ORDERS is your Y.INFO...
> 
> Rick
> 
> -Original Message-
> From: u2-users-boun...@listserver.u2ug.org 
> [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Hilk, Brandon
> Sent: Tuesday, August 27, 2013 1:24 PM
> To: u2-users@listserver.u2ug.org
> Subject: [U2] Select from one file with save-list from another file
> 
> Hello! 
> 
>  
> 
> UniVerse 10.1, HP/UX, Pick.
> 
>  
> 
> Is there a way I can build a save-list from a selection from one file
> X.INFO (where the record ID is an 8 digit number) and use that list to
> select from file Y.INFO (where that 8 digit number is not the record ID
> but does exist as a "foreign key" elsewhere in the file). In SQL this
> would be considered a join and would look something like this:
> 
>  
> 
> select *
> 
> from X.INFO, Y.INFO
> 
> where X.INFO.packet.id=Y.INFO.packet.id
> 
> and X.INFO.status='ccc';
> 
>  
> 
> I can run this in TCL and have it return the results I want but can't
> build a save-list from it because I don't know the UniVerse/SQL syntax
> to do so.
> 
>  
> 
> So to summarize, is there a way to use a save-list built from X.INFO to
> make another save-list comprised of the record ID's from Y.INFO?
> 
>  
> 
> Thanks for any advice you can give.
> 
>  
> 
> ___
> U2-Users mailing list
> U2-Users@listserver.u2ug.org
> http://listserver.u2ug.org/mailman/listinfo/u2-users
> ___
> U2-Users mailing list
> U2-Users@listserver.u2ug.org
> http://listserver.u2ug.org/mailman/listinfo/u2-users
  
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users

Re: [U2] Select from one file with save-list from another file

2013-08-27 Thread McGowan, Ian

At least on Unidata you can put the foreign key in the select statement, and 
that's the id that will be used:

#SELECT X WITH STATUS = "ccc" Y.PACKET.ID
#SAVE.LIST L1
#GET.LIST L1
#LIST Y

If you put two atb names in the select, they are interleaved in the resulting 
saved list, which can be a quick way to export some data...

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Hilk, Brandon
Sent: Tuesday, August 27, 2013 1:24 PM
To: u2-users@listserver.u2ug.org
Subject: [U2] Select from one file with save-list from another file

Hello! 

 

UniVerse 10.1, HP/UX, Pick.

 

Is there a way I can build a save-list from a selection from one file X.INFO 
(where the record ID is an 8 digit number) and use that list to select from 
file Y.INFO (where that 8 digit number is not the record ID but does exist as a 
"foreign key" elsewhere in the file). In SQL this would be considered a join 
and would look something like this:

 

select *

from X.INFO, Y.INFO

where X.INFO.packet.id=Y.INFO.packet.id

and X.INFO.status='ccc';

 

I can run this in TCL and have it return the results I want but can't build a 
save-list from it because I don't know the UniVerse/SQL syntax to do so.

 

So to summarize, is there a way to use a save-list built from X.INFO to make 
another save-list comprised of the record ID's from Y.INFO?

 

Thanks for any advice you can give.

 

___
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] Select from one file with save-list from another file

2013-08-27 Thread Jo Lester

SELECT CUSTOMERS WITH STATE = 'AZ' SAVING ORDER.ID

LIST ORDERS

> From: r...@lynden.com
> To: u2-users@listserver.u2ug.org
> Date: Tue, 27 Aug 2013 14:42:34 -0700
> Subject: Re: [U2] Select from one file with save-list from another file
> 
> I found an old example on line that might get you started
> 
> SELECT TO SLIST 0 FROM CUSTOMERS WHERE STATE = 'AZ';
> SAVE.LIST CUSTOMER.LIST
> 
> SELECT * FROM ORDERS WHERE CUSTOMER.ID IN (SELECT EVAL "  RECORD" FROM
> &SAVEDLISTS& 'CUSTOMER.LIST');
> 
> The extra syntax that might help is
> 
> SELECT TO SLIST  FROM ORDERS 
> 
> If CUSTOMER.LIST is your X.INFO and ORDERS is your Y.INFO...
> 
> Rick
> 
> -Original Message-
> From: u2-users-boun...@listserver.u2ug.org 
> [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Hilk, Brandon
> Sent: Tuesday, August 27, 2013 1:24 PM
> To: u2-users@listserver.u2ug.org
> Subject: [U2] Select from one file with save-list from another file
> 
> Hello! 
> 
>  
> 
> UniVerse 10.1, HP/UX, Pick.
> 
>  
> 
> Is there a way I can build a save-list from a selection from one file
> X.INFO (where the record ID is an 8 digit number) and use that list to
> select from file Y.INFO (where that 8 digit number is not the record ID
> but does exist as a "foreign key" elsewhere in the file). In SQL this
> would be considered a join and would look something like this:
> 
>  
> 
> select *
> 
> from X.INFO, Y.INFO
> 
> where X.INFO.packet.id=Y.INFO.packet.id
> 
> and X.INFO.status='ccc';
> 
>  
> 
> I can run this in TCL and have it return the results I want but can't
> build a save-list from it because I don't know the UniVerse/SQL syntax
> to do so.
> 
>  
> 
> So to summarize, is there a way to use a save-list built from X.INFO to
> make another save-list comprised of the record ID's from Y.INFO?
> 
>  
> 
> Thanks for any advice you can give.
> 
>  
> 
> ___
> 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] Select from one file with save-list from another file

2013-08-27 Thread Rick Nuckolls

I found an old example on line that might get you started

SELECT TO SLIST 0 FROM CUSTOMERS WHERE STATE = 'AZ';
SAVE.LIST CUSTOMER.LIST

SELECT * FROM ORDERS WHERE CUSTOMER.ID IN (SELECT EVAL "  RECORD" FROM
&SAVEDLISTS& 'CUSTOMER.LIST');

The extra syntax that might help is

SELECT TO SLIST  FROM ORDERS 

If CUSTOMER.LIST is your X.INFO and ORDERS is your Y.INFO...

Rick

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Hilk, Brandon
Sent: Tuesday, August 27, 2013 1:24 PM
To: u2-users@listserver.u2ug.org
Subject: [U2] Select from one file with save-list from another file

Hello! 

 

UniVerse 10.1, HP/UX, Pick.

 

Is there a way I can build a save-list from a selection from one file
X.INFO (where the record ID is an 8 digit number) and use that list to
select from file Y.INFO (where that 8 digit number is not the record ID
but does exist as a "foreign key" elsewhere in the file). In SQL this
would be considered a join and would look something like this:

 

select *

from X.INFO, Y.INFO

where X.INFO.packet.id=Y.INFO.packet.id

and X.INFO.status='ccc';

 

I can run this in TCL and have it return the results I want but can't
build a save-list from it because I don't know the UniVerse/SQL syntax
to do so.

 

So to summarize, is there a way to use a save-list built from X.INFO to
make another save-list comprised of the record ID's from Y.INFO?

 

Thanks for any advice you can give.

 

___
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] Select from one file with save-list from another file

2013-08-27 Thread Hilk, Brandon

Very nifty Allen, thank you! Unfortunately, I'm a mere user in my
organization and not a programmer, so I don't have UniVerse permissions
to compile programs or even run the editor.

*sigh* Alas.

I hope someone else is able to utilize your program.

-Original Message-
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Allen Elwood
(TW)
Sent: Tuesday, August 27, 2013 3:51 PM
To: U2 Users List
Subject: Re: [U2] Select from one file with save-list from another file

i made a relational selection program many many moons ago - i'm retired 
now - thanks to a crushed right hand i can only type with the left hand 
which goes numb quickly thanks to an elbow injury

but i keep the code around and lurk on the list just in case :-)

* RSELECT - Relational File Selection Utility
* Usage:
* RSELECT LIST1 FILENAME ATTRNBR LIST2
 Version="~Ver=~7.0.10~362202866~"
*#* COPY COPY.TOOLS.BP STANDARD.VARIABLES.1 (REPLACING PGM.NAME BY 
RSELECT, FN.NAME BY RSELECT, IO.OPEN.OPTS BY TERM.DATA:LOCK) ;*#* Copied

Source Follows (06-25-03)
   $INCLUDE STANDARD.COMMON.VARIABLES FROM COPY.TOOLS.BP
   $INCLUDE STANDARD.COMMON.APP.PROGRAMS FROM COPY.TOOLS.BP
   $INCLUDE STANDARD.VARIABLES.END FROM COPY.TOOLS.BP
   PGM.NAME='RSELECT'; FN.NAME ='RSELECT'
   CALL IO.OPEN('TERM.DATA:LOCK',PASSWORDS)
*#*
*===
=
   GOSUB GET.OPTIONS
   GOSUB DETERMINE.TEMP.FILESIZE
   GOSUB LOAD.TEMP.FILE
   GOSUB CREATE.TEMP.DICT
   GOSUB BUILD.LIST2
   GOSUB FINISH.UP
   STOP
*===
=
GET.OPTIONS:
   PRINT 'Enter END at any prompt to abort'
   PRINT
   PRINT 'Enter Source List Name :   ':;INPUT LIST1
   IF LIST1 = 'END' THEN STOP
   PRINT 'Enter the Selection Filename : ':;INPUT FILENAME
   IF FILENAME = 'END' THEN STOP
   PRINT 'Enter the Selection Attribute Number : ':;INPUT ATTRNBR
   IF ATTRNBR = 'END' THEN STOP
   PRINT 'Enter the Destination List Name :  ':;INPUT LIST2
   IF LIST2 = 'END' THEN STOP
   PRINT
   LOOP
 PRINT 'Continue? (Y/END)':;INPUT YORN
 IF YORN = 'END' THEN STOP
 IF YORN = 'Y' THEN EXIT
   REPEAT
   RETURN
*===
=
DETERMINE.TEMP.FILESIZE:
   MSG = 'GET.LIST ':LIST1
   PERFORM MSG CAPTURING JUNK
   SELECTION = @SYSTEM.RETURN.CODE
   TBYTES= 0
   LOOP WHILE READNEXT ID DO
 TBYTES+=LEN(ID)
   REPEAT
   TEMP.MOD  = INT(TBYTES/1024)
   IF TEMP.MOD < 1 THEN
 TEMP.MOD = 5
   END
   TEMP.NAME = 'RSELECT':PROCESS.ID
   MSG = 'CREATE.FILE ':TEMP.NAME:' ':TEMP.MOD
   PERFORM MSG CAPTURING JUNK
   OPEN '', TEMP.NAME TO TEMP.HANDLE ELSE PRINT 'Internal processing 
error';STOP
   OPEN 'DICT', TEMP.NAME TO TEMP.DICT ELSE PRINT 'Internal processing 
error';STOP
   RETURN
*===
=
LOAD.TEMP.FILE:
   MSG = 'GET.LIST ':LIST1
   PERFORM MSG CAPTURING JUNK
   NADA = ''
   LOOP WHILE READNEXT ID DO
 WRITEV NADA ON TEMP.HANDLE, ID, 0
   REPEAT
   RETURN
*===
=
CREATE.TEMP.DICT:
   DREC = ''
   DREC<1> = 'I'
   DREC<2> = 'TRANS("':TEMP.NAME:'",F':ATTRNBR:',"F0","X")'
   DREC<4> = 'Temporary Trans Dict'
   DREC<5> = '25L'
   WRITE DREC ON TEMP.DICT, "TRAN.DICT"
   DREC = ''
   DREC<1> = 'D'
   DREC<2> = ATTRNBR
   DREC<4> = 'Temprary Base Dict'
   DREC<5> = '25L'
   DID = "F":ATTRNBR
   WRITE DREC ON TEMP.DICT, DID
   RETURN
*===
=
BUILD.LIST2:
   PRINT 'Now creating list ':LIST2
   MSG = 'SELECT ':FILENAME:' WITH TRAN.DICT GT "" USING DICT ':
TEMP.NAME
   PERFORM MSG
   PERFORM 'SAVE.LIST ':LIST2
RETURN
*===
=
FINISH.UP:
   CLOSE TEMP.HANDLE
   CLOSE TEMP.DICT
   MSG = 'DELETE.FILE ':TEMP.NAME
   DATA 'Y'
   PERFORM MSG CAPTURING JUNK
   PRINT 'Program Completed'
   RETURN

On 8/27/2013 1:24 PM, Hilk, Brandon wrote:
> Hello!
>
>   
>
> UniVerse 10.1, HP/UX, Pick.
>
>   
>
> Is there a way I can build a save-list from a selection from one file
> X.INFO (where the record ID is an 8 digit number) and use that list to
> select from file Y.INFO (where that 8 digit number is not the record
ID
> but does exist as a "foreign key" elsewhere in the file). In SQL this
> would be considered a join and would look something like this:
>
>   
>
> select *
>
> from X.INFO, Y.INFO
>
> where X.INFO.packet.id=Y.INFO.packet.id
>
> and X.INFO.status='ccc';
>
>   
>
> I can run this in TCL and have it return the results I want but can't
> build a save-list from it because I don't know the UniVerse/SQL syntax
> to do so.
>
>   
>
> So to summarize, is there a way to use a save-list built from X.INFO
to
> make another save-list comprised of the record ID's from Y.INFO?
>
>   
>
> Thanks for any advice y

Re: [U2] Select from one file with save-list from another file

2013-08-27 Thread Allen Elwood (TW)



i made a relational selection program many many moons ago - i'm retired 
now - thanks to a crushed right hand i can only type with the left hand 
which goes numb quickly thanks to an elbow injury


but i keep the code around and lurk on the list just in case :-)

* RSELECT - Relational File Selection Utility
* Usage:
* RSELECT LIST1 FILENAME ATTRNBR LIST2
Version="~Ver=~7.0.10~362202866~"
*#* COPY COPY.TOOLS.BP STANDARD.VARIABLES.1 (REPLACING PGM.NAME BY 
RSELECT, FN.NAME BY RSELECT, IO.OPEN.OPTS BY TERM.DATA:LOCK) ;*#* Copied 
Source Follows (06-25-03)

  $INCLUDE STANDARD.COMMON.VARIABLES FROM COPY.TOOLS.BP
  $INCLUDE STANDARD.COMMON.APP.PROGRAMS FROM COPY.TOOLS.BP
  $INCLUDE STANDARD.VARIABLES.END FROM COPY.TOOLS.BP
  PGM.NAME='RSELECT'; FN.NAME ='RSELECT'
  CALL IO.OPEN('TERM.DATA:LOCK',PASSWORDS)
*#*
*
  GOSUB GET.OPTIONS
  GOSUB DETERMINE.TEMP.FILESIZE
  GOSUB LOAD.TEMP.FILE
  GOSUB CREATE.TEMP.DICT
  GOSUB BUILD.LIST2
  GOSUB FINISH.UP
  STOP
*
GET.OPTIONS:
  PRINT 'Enter END at any prompt to abort'
  PRINT
  PRINT 'Enter Source List Name :   ':;INPUT LIST1
  IF LIST1 = 'END' THEN STOP
  PRINT 'Enter the Selection Filename : ':;INPUT FILENAME
  IF FILENAME = 'END' THEN STOP
  PRINT 'Enter the Selection Attribute Number : ':;INPUT ATTRNBR
  IF ATTRNBR = 'END' THEN STOP
  PRINT 'Enter the Destination List Name :  ':;INPUT LIST2
  IF LIST2 = 'END' THEN STOP
  PRINT
  LOOP
PRINT 'Continue? (Y/END)':;INPUT YORN
IF YORN = 'END' THEN STOP
IF YORN = 'Y' THEN EXIT
  REPEAT
  RETURN
*
DETERMINE.TEMP.FILESIZE:
  MSG = 'GET.LIST ':LIST1
  PERFORM MSG CAPTURING JUNK
  SELECTION = @SYSTEM.RETURN.CODE
  TBYTES= 0
  LOOP WHILE READNEXT ID DO
TBYTES+=LEN(ID)
  REPEAT
  TEMP.MOD  = INT(TBYTES/1024)
  IF TEMP.MOD < 1 THEN
TEMP.MOD = 5
  END
  TEMP.NAME = 'RSELECT':PROCESS.ID
  MSG = 'CREATE.FILE ':TEMP.NAME:' ':TEMP.MOD
  PERFORM MSG CAPTURING JUNK
  OPEN '', TEMP.NAME TO TEMP.HANDLE ELSE PRINT 'Internal processing 
error';STOP
  OPEN 'DICT', TEMP.NAME TO TEMP.DICT ELSE PRINT 'Internal processing 
error';STOP

  RETURN
*
LOAD.TEMP.FILE:
  MSG = 'GET.LIST ':LIST1
  PERFORM MSG CAPTURING JUNK
  NADA = ''
  LOOP WHILE READNEXT ID DO
WRITEV NADA ON TEMP.HANDLE, ID, 0
  REPEAT
  RETURN
*
CREATE.TEMP.DICT:
  DREC = ''
  DREC<1> = 'I'
  DREC<2> = 'TRANS("':TEMP.NAME:'",F':ATTRNBR:',"F0","X")'
  DREC<4> = 'Temporary Trans Dict'
  DREC<5> = '25L'
  WRITE DREC ON TEMP.DICT, "TRAN.DICT"
  DREC = ''
  DREC<1> = 'D'
  DREC<2> = ATTRNBR
  DREC<4> = 'Temprary Base Dict'
  DREC<5> = '25L'
  DID = "F":ATTRNBR
  WRITE DREC ON TEMP.DICT, DID
  RETURN
*
BUILD.LIST2:
  PRINT 'Now creating list ':LIST2
  MSG = 'SELECT ':FILENAME:' WITH TRAN.DICT GT "" USING DICT ': TEMP.NAME
  PERFORM MSG
  PERFORM 'SAVE.LIST ':LIST2
RETURN
*
FINISH.UP:
  CLOSE TEMP.HANDLE
  CLOSE TEMP.DICT
  MSG = 'DELETE.FILE ':TEMP.NAME
  DATA 'Y'
  PERFORM MSG CAPTURING JUNK
  PRINT 'Program Completed'
  RETURN



On 8/27/2013 1:24 PM, Hilk, Brandon wrote:

Hello!

  


UniVerse 10.1, HP/UX, Pick.

  


Is there a way I can build a save-list from a selection from one file
X.INFO (where the record ID is an 8 digit number) and use that list to
select from file Y.INFO (where that 8 digit number is not the record ID
but does exist as a "foreign key" elsewhere in the file). In SQL this
would be considered a join and would look something like this:

  


select *

from X.INFO, Y.INFO

where X.INFO.packet.id=Y.INFO.packet.id

and X.INFO.status='ccc';

  


I can run this in TCL and have it return the results I want but can't
build a save-list from it because I don't know the UniVerse/SQL syntax
to do so.

  


So to summarize, is there a way to use a save-list built from X.INFO to
make another save-list comprised of the record ID's from Y.INFO?

  


Thanks for any advice you can give.

  


___
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] Select from one file with save-list from another file

2013-08-27 Thread Hilk, Brandon

Hello! 

 

UniVerse 10.1, HP/UX, Pick.

 

Is there a way I can build a save-list from a selection from one file
X.INFO (where the record ID is an 8 digit number) and use that list to
select from file Y.INFO (where that 8 digit number is not the record ID
but does exist as a "foreign key" elsewhere in the file). In SQL this
would be considered a join and would look something like this:

 

select *

from X.INFO, Y.INFO

where X.INFO.packet.id=Y.INFO.packet.id

and X.INFO.status='ccc';

 

I can run this in TCL and have it return the results I want but can't
build a save-list from it because I don't know the UniVerse/SQL syntax
to do so.

 

So to summarize, is there a way to use a save-list built from X.INFO to
make another save-list comprised of the record ID's from Y.INFO?

 

Thanks for any advice you can give.

 

___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users

Re: [U2] universe 11.1 triggers via indexing and @IDX.IOTYPE sample

2013-08-27 Thread Buss, Troy (Contractor, Logitek Systems)

Phil,  

Ahh, good catch, I believe you are correct.   Thankfully, this should be a very 
very rare occurrence.  Thanks!

Should be:
0073   IF DATE.NOW # DATE() AND TIME() < TIME.NOW THEN DATE.NOW = DATE()

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Phil Walker
Sent: Tuesday, August 27, 2013 11:51 AM
To: U2 Users List
Subject: Re: [U2] universe 11.1 triggers via indexing and @IDX.IOTYPE sample

Should line 73 of your program be < rather than > for the time as when the date 
has changed, the time will be less than the time of the date - 1 which you have 
just set.


0068   DATE.NOW = DATE()
0069   TIME.NOW = TIME()

0073   IF DATE.NOW # DATE() AND TIME() > TIME.NOW THEN DATE.NOW = DATE()

Example:

DATE.NOW = 16789
TIME.NOW = 83999

DATE() = 16790
TIME() = 1

Might be wrong

_
Scanned by IBM Email Security Management Services powered by MessageLabs. For 
more information please visit http://www.ers.ibm.com

This email is intended only for the use of the party to which it is addressed 
and may contain information that is privileged, confidential, or protected by 
law.  If you are not the intended recipient you are hereby notified that any 
dissemination, copying or distribution of the email or its contents is strictly 
prohibited.  If you have received this message in error, please notify us 
immediately, by replying to the message and deleting it from your computer.

WARNING: Internet communications are not assured to be secure or clear of 
inaccuracies as information could be intercepted, corrupted, lost, destroyed, 
arrive late or incomplete, or contain viruses.  Therefore, we do not accept 
responsibility for any errors or omissions that are present in this email, or 
any attachment, that have arisen as a result of e-mail transmission.
_
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users

Re: [U2] universe 11.1 triggers via indexing and @IDX.IOTYPE sample

2013-08-27 Thread Phil Walker

Should line 73 of your program be < rather than > for the time as when the date 
has changed, the time will be less than the time of the date - 1 which you have 
just set.


0068   DATE.NOW = DATE()
0069   TIME.NOW = TIME()

0073   IF DATE.NOW # DATE() AND TIME() > TIME.NOW THEN DATE.NOW = DATE()

Example:

DATE.NOW = 16789
TIME.NOW = 83999

DATE() = 16790
TIME() = 1

Might be wrong



-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Perry Taylor
Sent: Wednesday, 28 August 2013 4:26 a.m.
To: U2 Users List
Subject: Re: [U2] universe 11.1 triggers via indexing and @IDX.IOTYPE sample

That is awesome information Troy!  Thanks for that!

(Now if I could just get my production environment upgrade to 11.1x :D )

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Buss, Troy 
(Contractor, Logitek Systems)
Sent: Tuesday, August 27, 2013 10:03 AM
To: U2 Users List
Subject: [U2] universe 11.1 triggers via indexing and @IDX.IOTYPE sample

Regarding the recent discussion of using indexes as triggers, I use the 
following to update timestamps of record changes to a master item stats file 
that we use for controlling updates to a business warehouse (BW).   Without 
modifying hundreds of legacy programs to support proper timestamps in the 
primary records, I added the following indexed Idescriptors to files I need to 
track.   We are on universe 11.1.1 and this has been working well for over a 
year.

DICT AR-INVOICE INDEX..MODIFIEDZ
0001 I
0002 SUBR("ZIDESC.ITEMSTATS.UPDATE", "AR-INVOICE!":F0,"")
0004 \
0005 1L
0006 S
0049 used to create item created, modified, delete in zitemstats file - create 
index with this and no.nulls keyword EOI 216 bytes

In this example, we have a AR-INVOICE header record and a related ARLI line 
item file.   For our purposes to keep in sync with the BW, we want to timestamp 
the corresponding header record whenever a line item is updated so the ARLI 
dict item also updates AR-INVOICE header ZITEMSTATS with an "I" indirect update 
flag.

DICT ARLI INDEX..MODIFIEDZ
0001 I
0002 SUBR("ZIDESC.ITEMSTATS.UPDATE", 
"ARLI!":F0,""):SUBR("ZIDESC.ITEMSTATS.UPDATE", "AR-INVOICE!":F1,"I")
0004 \
0005 1L
0006 S
0049 used to create item created, modified, delete in zitemstats file - create 
index with this and no.nulls keyword EOI 264 bytes

Alternate Key Index Summary for file ARLI File... ARLI Indices 
3 (0 A-type, 0 C-type, 0 D-type, 3 I-type, 0 SQL, 0 S-type) Index Updates.. 
Enabled, No updates pending

Index name  Type  BuildNulls  In DICT  S/M  Just Unique Field num/I-type
INDEX..MODIFIEDZ INot Reqd  No Yes  SL NSUBR("ZIDESC.ITE
MSTATS.UPDATE",
"ARLI!":F0,""):S
UBR("ZIDESC.ITEM
STATS.UPDATE", "
AR-INVOICE!":F1,
"I")


Subroutine ZIDESC.ITEMSTATS.UPDATE:

0001 SUB (RETURN.VALUE, ZITEMSTATS.ID, OPTIONS)
0002 * updates zitemstats with timestamps; create, modified, deleted
0003 *
0004 * Revision history:
0005 *
0006 * tdb.1190 2012-04-22_1602 new
0007 *
0008 *
0009 * input:  ZITEMSTATS.ID item id to use for ZITEMSTATS file
0010 * OPTIONS
0011 *I  update attb 5 with "I" for indirect 
reference
0012 *
0013 * output: RETURN.VALUE  NULL
0014 *
0015 * common vars: /ZITEMSTATS/
0016 *
0017 * called by:  INDEX..MODIFIEDZ dictionary items
0018 * calls:
0019 *
0020 * notes:
0021 *
0022 *
0023 $OPTIONS TIME.MILLISECOND
0024 *
0025 * @idx.iotype = 0 - Not being used from an index;
0026 * 1 - INSERT (new) record;
0027 * 2 - DELETE record;
0028 * 3 - Derive old index value;
0029 * 4 - Derive new index value;
0030 *
0031 COMMON /ZITEMSTATS/ F.ZITEMSTATS, ZITEMSTATS.FLAG.OPEN, ZITEMSTATS.WHO
0032 *
0033 EQU NUL TO ""
0034 *
0035 RETURN.VALUE = NUL
0036 *
0037 * the following handles initial call and logto commands
0038 *
0039 IF NOT(ZITEMSTATS.FLAG.OPEN) OR (@WHO # ZITEMSTATS.WHO) THEN
0040   OPEN "ZITEMSTATS" TO F.ZITEMSTATS ELSE
0041 RETURN ;*  to caller.
0042   END
0043 *
0044   ZITEMSTATS.FLAG.OPEN = 1
0045   ZITEMSTATS.WHO   = @WHO
0046 END
0047 *
0048 LOG.TYPE = NUL
0049 *
0050 BEGIN CASE
0051   CASE @IDX.IOTYPE = 0
0052 *
0053   CASE @IDX.IOTYPE = 1
0054 LOG.TYPE = "C" ;* new - created - easier to see than 'N' new
0055 *
0056   CASE @IDX.IOTYPE = 2
0057 LOG.TYPE = "D" ;* delete
0058 *
0059   CASE @IDX.IOTYPE = 3
0060 *
0061   CASE @IDX.IOTYPE = 4
0062 LOG.TYPE = "M" ;* modified
0063 *
0064 END CASE
0065 *
0066 IF LOG.TYPE # NUL THEN
0067 *
0068   DATE.NOW

Re: [U2] UDT 7.1 Indexing question

2013-08-27 Thread Lunt, Bruce

I will tweak my select to look for just the 3 periods that I want and see if 
that helps.
Thanks for the help.

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wjhonson
Sent: Tuesday, August 27, 2013 11:04 AM
To: u2-users@listserver.u2ug.org
Subject: Re: [U2] UDT 7.1 Indexing question

So if each one has at most five multi-values, and you want the more recent 
three, you will be selecting at a minimum 60% of the file's entries.  For that 
purposes an index is the *wrong* structure to use :)

The most efficient search is strictly linear... group order.  So just a simple 
SSELECT... WHEN...

-Original Message-
From: Lunt, Bruce 
To: U2 Users List 
Sent: Tue, Aug 27, 2013 10:59 am
Subject: Re: [U2] UDT 7.1 Indexing question

Each record has 2-5 multi-values that are date periods. I am looking for the
most recent 3 periods. There are over a million records so the BY-EXP gives me 3
million values to process. I thought about writing a work file with the values
in a separate record but the size seems like it would take forever to do and
then I would still need to create the report from the work file's 3 million
records.

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org]
On Behalf Of Dave Davis
Sent: Tuesday, August 27, 2013 10:52 AM
To: U2 Users List
Subject: Re: [U2] UDT 7.1 Indexing question

It really depends on the percentage of records that contain the value you are
looking for - multivalued or not

I think if you include a criteria like:

WITH mv_indexed_field = "value"

it will be faster than without the index.

However, if most of the records in the file have that value it won't make much
difference.

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org]
On Behalf Of Lunt, Bruce
Sent: Tuesday, August 27, 2013 1:46 PM
To: U2 Users List
Subject: Re: [U2] UDT 7.1 Indexing question

That's what I was afraid of. I have a large file and I was looking for some way
to speed up a report that I am creating with Uniquery.

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org]
On Behalf Of Kevin King
Sent: Tuesday, August 27, 2013 10:43 AM
To: U2 Users List
Subject: Re: [U2] UDT 7.1 Indexing question

My guess is that it shouldn't matter.  Each multivalue in a list gets a separate
index entry.

On Tue, Aug 27, 2013 at 10:46 AM, Lunt, Bruce  wrote:

> Hi All,
>
> Will indexing a field that I retrieve with BY-EXP be faster than an
> un-indexed field?
>
> Thanks,
> Bruce
> ___
> 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

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]
6110 Enterprise Parkway
Grove City, OH
43123
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

___
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] UDT 7.1 Indexing question

2013-08-27 Thread Wjhonson

So if each one has at most five multi-values, and you want the more recent 
three, you will be selecting at a minimum 60% of the file's entries.  For that 
purposes an index is the *wrong* structure to use :)

The most efficient search is strictly linear... group order.  So just a simple 
SSELECT... WHEN...

-Original Message-
From: Lunt, Bruce 
To: U2 Users List 
Sent: Tue, Aug 27, 2013 10:59 am
Subject: Re: [U2] UDT 7.1 Indexing question

Each record has 2-5 multi-values that are date periods. I am looking for the 
most recent 3 periods. There are over a million records so the BY-EXP gives me 
3 
million values to process. I thought about writing a work file with the values 
in a separate record but the size seems like it would take forever to do and 
then I would still need to create the report from the work file's 3 million 
records.

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org] 
On Behalf Of Dave Davis
Sent: Tuesday, August 27, 2013 10:52 AM
To: U2 Users List
Subject: Re: [U2] UDT 7.1 Indexing question

It really depends on the percentage of records that contain the value you are 
looking for - multivalued or not

I think if you include a criteria like:

WITH mv_indexed_field = "value"

it will be faster than without the index.

However, if most of the records in the file have that value it won't make much 
difference.

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org] 
On Behalf Of Lunt, Bruce
Sent: Tuesday, August 27, 2013 1:46 PM
To: U2 Users List
Subject: Re: [U2] UDT 7.1 Indexing question

That's what I was afraid of. I have a large file and I was looking for some way 
to speed up a report that I am creating with Uniquery.

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org] 
On Behalf Of Kevin King
Sent: Tuesday, August 27, 2013 10:43 AM
To: U2 Users List
Subject: Re: [U2] UDT 7.1 Indexing question

My guess is that it shouldn't matter.  Each multivalue in a list gets a 
separate 
index entry.

On Tue, Aug 27, 2013 at 10:46 AM, Lunt, Bruce  wrote:

> Hi All,
>
> Will indexing a field that I retrieve with BY-EXP be faster than an
> un-indexed field?
>
> Thanks,
> Bruce
> ___
> 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

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]
6110 Enterprise Parkway
Grove City, OH
43123
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

___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users

Re: [U2] UDT 7.1 Indexing question

2013-08-27 Thread Kevin King

Dave's right, if you can look for a specific value (or values with
"x""y""z") you'll get significantly faster speed than with with BY.EXP.
 You can then use a LOCATE(..) in BASIC to figure out which rows are to be
included on the report.


On Tue, Aug 27, 2013 at 11:58 AM, Lunt, Bruce  wrote:

> Each record has 2-5 multi-values that are date periods. I am looking for
> the most recent 3 periods. There are over a million records so the BY-EXP
> gives me 3 million values to process. I thought about writing a work file
> with the values in a separate record but the size seems like it would take
> forever to do and then I would still need to create the report from the
> work file's 3 million records.
>
> -Original Message-
> From: u2-users-boun...@listserver.u2ug.org [mailto:
> u2-users-boun...@listserver.u2ug.org] On Behalf Of Dave Davis
> Sent: Tuesday, August 27, 2013 10:52 AM
> To: U2 Users List
> Subject: Re: [U2] UDT 7.1 Indexing question
>
> It really depends on the percentage of records that contain the value you
> are looking for - multivalued or not
>
> I think if you include a criteria like:
>
> WITH mv_indexed_field = "value"
>
> it will be faster than without the index.
>
> However, if most of the records in the file have that value it won't make
> much difference.
>
> -Original Message-
> From: u2-users-boun...@listserver.u2ug.org [mailto:
> u2-users-boun...@listserver.u2ug.org] On Behalf Of Lunt, Bruce
> Sent: Tuesday, August 27, 2013 1:46 PM
> To: U2 Users List
> Subject: Re: [U2] UDT 7.1 Indexing question
>
> That's what I was afraid of. I have a large file and I was looking for
> some way to speed up a report that I am creating with Uniquery.
>
> -Original Message-
> From: u2-users-boun...@listserver.u2ug.org [mailto:
> u2-users-boun...@listserver.u2ug.org] On Behalf Of Kevin King
> Sent: Tuesday, August 27, 2013 10:43 AM
> To: U2 Users List
> Subject: Re: [U2] UDT 7.1 Indexing question
>
> My guess is that it shouldn't matter.  Each multivalue in a list gets a
> separate index entry.
>
>
> On Tue, Aug 27, 2013 at 10:46 AM, Lunt, Bruce  wrote:
>
> > Hi All,
> >
> > Will indexing a field that I retrieve with BY-EXP be faster than an
> > un-indexed field?
> >
> > Thanks,
> > Bruce
> > ___
> > 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
> 
>
>
> 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.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
>
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users

Re: [U2] UDT 7.1 Indexing question

2013-08-27 Thread Lunt, Bruce

Each record has 2-5 multi-values that are date periods. I am looking for the 
most recent 3 periods. There are over a million records so the BY-EXP gives me 
3 million values to process. I thought about writing a work file with the 
values in a separate record but the size seems like it would take forever to do 
and then I would still need to create the report from the work file's 3 million 
records.

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Dave Davis
Sent: Tuesday, August 27, 2013 10:52 AM
To: U2 Users List
Subject: Re: [U2] UDT 7.1 Indexing question

It really depends on the percentage of records that contain the value you are 
looking for - multivalued or not

I think if you include a criteria like:

WITH mv_indexed_field = "value"

it will be faster than without the index.

However, if most of the records in the file have that value it won't make much 
difference.

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Lunt, Bruce
Sent: Tuesday, August 27, 2013 1:46 PM
To: U2 Users List
Subject: Re: [U2] UDT 7.1 Indexing question

That's what I was afraid of. I have a large file and I was looking for some way 
to speed up a report that I am creating with Uniquery.

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Kevin King
Sent: Tuesday, August 27, 2013 10:43 AM
To: U2 Users List
Subject: Re: [U2] UDT 7.1 Indexing question

My guess is that it shouldn't matter.  Each multivalue in a list gets a 
separate index entry.

On Tue, Aug 27, 2013 at 10:46 AM, Lunt, Bruce  wrote:

> Hi All,
>
> Will indexing a field that I retrieve with BY-EXP be faster than an
> un-indexed field?
>
> Thanks,
> Bruce
> ___
> 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

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]
6110 Enterprise Parkway
Grove City, OH
43123
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] UDT 7.1 Indexing question

2013-08-27 Thread Dave Davis

It really depends on the percentage of records that contain the value you are 
looking for - multivalued or not

I think if you include a criteria like:

WITH mv_indexed_field = "value"

it will be faster than without the index.

However, if most of the records in the file have that value it won't make much 
difference.

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Lunt, Bruce
Sent: Tuesday, August 27, 2013 1:46 PM
To: U2 Users List
Subject: Re: [U2] UDT 7.1 Indexing question

That's what I was afraid of. I have a large file and I was looking for some way 
to speed up a report that I am creating with Uniquery.

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Kevin King
Sent: Tuesday, August 27, 2013 10:43 AM
To: U2 Users List
Subject: Re: [U2] UDT 7.1 Indexing question

My guess is that it shouldn't matter.  Each multivalue in a list gets a 
separate index entry.

On Tue, Aug 27, 2013 at 10:46 AM, Lunt, Bruce  wrote:

> Hi All,
>
> Will indexing a field that I retrieve with BY-EXP be faster than an
> un-indexed field?
>
> Thanks,
> Bruce
> ___
> 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

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]
6110 Enterprise Parkway
Grove City, OH
43123
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] UDT 7.1 Indexing question

2013-08-27 Thread Lunt, Bruce

That's what I was afraid of. I have a large file and I was looking for some way 
to speed up a report that I am creating with Uniquery.

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Kevin King
Sent: Tuesday, August 27, 2013 10:43 AM
To: U2 Users List
Subject: Re: [U2] UDT 7.1 Indexing question

My guess is that it shouldn't matter.  Each multivalue in a list gets a
separate index entry.


On Tue, Aug 27, 2013 at 10:46 AM, Lunt, Bruce  wrote:

> Hi All,
>
> Will indexing a field that I retrieve with BY-EXP be faster than an
> un-indexed field?
>
> Thanks,
> Bruce
> ___
> 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] UDT 7.1 Indexing question

2013-08-27 Thread Kevin King

My guess is that it shouldn't matter.  Each multivalue in a list gets a
separate index entry.


On Tue, Aug 27, 2013 at 10:46 AM, Lunt, Bruce  wrote:

> Hi All,
>
> Will indexing a field that I retrieve with BY-EXP be faster than an
> un-indexed field?
>
> Thanks,
> Bruce
> ___
> 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] UDT 7.1 Indexing question

2013-08-27 Thread Lunt, Bruce

Hi All,

Will indexing a field that I retrieve with BY-EXP be faster than an un-indexed 
field?

Thanks,
Bruce
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users

Re: [U2] universe 11.1 triggers via indexing and @IDX.IOTYPE sample

2013-08-27 Thread Perry Taylor

That is awesome information Troy!  Thanks for that!

(Now if I could just get my production environment upgrade to 11.1x :D )

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Buss, Troy 
(Contractor, Logitek Systems)
Sent: Tuesday, August 27, 2013 10:03 AM
To: U2 Users List
Subject: [U2] universe 11.1 triggers via indexing and @IDX.IOTYPE sample

Regarding the recent discussion of using indexes as triggers, I use the 
following to update timestamps of record changes to a master item stats file 
that we use for controlling updates to a business warehouse (BW).   Without 
modifying hundreds of legacy programs to support proper timestamps in the 
primary records, I added the following indexed Idescriptors to files I need to 
track.   We are on universe 11.1.1 and this has been working well for over a 
year.

DICT AR-INVOICE INDEX..MODIFIEDZ
0001 I
0002 SUBR("ZIDESC.ITEMSTATS.UPDATE", "AR-INVOICE!":F0,"")
0004 \
0005 1L
0006 S
0049 used to create item created, modified, delete in zitemstats file - create 
index with this and no.nulls keyword
EOI 216 bytes

In this example, we have a AR-INVOICE header record and a related ARLI line 
item file.   For our purposes to keep in sync with the BW, we want to timestamp 
the corresponding header record whenever a line item is updated so the ARLI 
dict item also updates AR-INVOICE header ZITEMSTATS with an "I" indirect update 
flag.

DICT ARLI INDEX..MODIFIEDZ
0001 I
0002 SUBR("ZIDESC.ITEMSTATS.UPDATE", 
"ARLI!":F0,""):SUBR("ZIDESC.ITEMSTATS.UPDATE", "AR-INVOICE!":F1,"I")
0004 \
0005 1L
0006 S
0049 used to create item created, modified, delete in zitemstats file - create 
index with this and no.nulls keyword
EOI 264 bytes

Alternate Key Index Summary for file ARLI
File... ARLI
Indices 3 (0 A-type, 0 C-type, 0 D-type, 3 I-type, 0 SQL, 0 S-type)
Index Updates.. Enabled, No updates pending

Index name  Type  BuildNulls  In DICT  S/M  Just Unique Field num/I-type
INDEX..MODIFIEDZ INot Reqd  No Yes  SL NSUBR("ZIDESC.ITE
MSTATS.UPDATE",
"ARLI!":F0,""):S
UBR("ZIDESC.ITEM
STATS.UPDATE", "
AR-INVOICE!":F1,
"I")


Subroutine ZIDESC.ITEMSTATS.UPDATE:

0001 SUB (RETURN.VALUE, ZITEMSTATS.ID, OPTIONS)
0002 * updates zitemstats with timestamps; create, modified, deleted
0003 *
0004 * Revision history:
0005 *
0006 * tdb.1190 2012-04-22_1602 new
0007 *
0008 *
0009 * input:  ZITEMSTATS.ID item id to use for ZITEMSTATS file
0010 * OPTIONS
0011 *I  update attb 5 with "I" for indirect 
reference
0012 *
0013 * output: RETURN.VALUE  NULL
0014 *
0015 * common vars: /ZITEMSTATS/
0016 *
0017 * called by:  INDEX..MODIFIEDZ dictionary items
0018 * calls:
0019 *
0020 * notes:
0021 *
0022 *
0023 $OPTIONS TIME.MILLISECOND
0024 *
0025 * @idx.iotype = 0 - Not being used from an index;
0026 * 1 - INSERT (new) record;
0027 * 2 - DELETE record;
0028 * 3 - Derive old index value;
0029 * 4 - Derive new index value;
0030 *
0031 COMMON /ZITEMSTATS/ F.ZITEMSTATS, ZITEMSTATS.FLAG.OPEN, ZITEMSTATS.WHO
0032 *
0033 EQU NUL TO ""
0034 *
0035 RETURN.VALUE = NUL
0036 *
0037 * the following handles initial call and logto commands
0038 *
0039 IF NOT(ZITEMSTATS.FLAG.OPEN) OR (@WHO # ZITEMSTATS.WHO) THEN
0040   OPEN "ZITEMSTATS" TO F.ZITEMSTATS ELSE
0041 RETURN ;*  to caller.
0042   END
0043 *
0044   ZITEMSTATS.FLAG.OPEN = 1
0045   ZITEMSTATS.WHO   = @WHO
0046 END
0047 *
0048 LOG.TYPE = NUL
0049 *
0050 BEGIN CASE
0051   CASE @IDX.IOTYPE = 0
0052 *
0053   CASE @IDX.IOTYPE = 1
0054 LOG.TYPE = "C" ;* new - created - easier to see than 'N' new
0055 *
0056   CASE @IDX.IOTYPE = 2
0057 LOG.TYPE = "D" ;* delete
0058 *
0059   CASE @IDX.IOTYPE = 3
0060 *
0061   CASE @IDX.IOTYPE = 4
0062 LOG.TYPE = "M" ;* modified
0063 *
0064 END CASE
0065 *
0066 IF LOG.TYPE # NUL THEN
0067 *
0068   DATE.NOW = DATE()
0069   TIME.NOW = TIME()
0070 *
0071 * handle instance of midnight between date/time functions
0072 *
0073   IF DATE.NOW # DATE() AND TIME() > TIME.NOW THEN DATE.NOW = DATE()
0074 *
0075   TIMESTAMP = DATE.NOW * 86400 + TIME.NOW
0076 *
0077   READU ZITEMSTATS$ FROM F.ZITEMSTATS, ZITEMSTATS.ID ELSE ZITEMSTATS$ = NUL
0078 *
0079   INDIRECT.FLAG = COUNT(OPTIONS, "I")
0080 *
0081   IF INDIRECT.FLAG THEN
0082 ZITEMSTATS$<1> = "M" ;* modified
0083   END ELSE
0084 ZITEMSTATS$<1> = LOG.TYPE
0085   END
0086 *
0087 * created
0088 *
0089   IF INDIRECT.FLAG ELSE
0090 IF LOG.TYPE = "C" THEN
0091   ZITEMSTATS$<2> = TIMESTAMP
0092 END
0093   END
0094 *

[U2] universe 11.1 triggers via indexing and @IDX.IOTYPE sample

2013-08-27 Thread Buss, Troy (Contractor, Logitek Systems)

Regarding the recent discussion of using indexes as triggers, I use the 
following to update timestamps of record changes to a master item stats file 
that we use for controlling updates to a business warehouse (BW).   Without 
modifying hundreds of legacy programs to support proper timestamps in the 
primary records, I added the following indexed Idescriptors to files I need to 
track.   We are on universe 11.1.1 and this has been working well for over a 
year.

DICT AR-INVOICE INDEX..MODIFIEDZ
0001 I
0002 SUBR("ZIDESC.ITEMSTATS.UPDATE", "AR-INVOICE!":F0,"")
0004 \
0005 1L
0006 S
0049 used to create item created, modified, delete in zitemstats file - create 
index with this and no.nulls keyword
EOI 216 bytes

In this example, we have a AR-INVOICE header record and a related ARLI line 
item file.   For our purposes to keep in sync with the BW, we want to timestamp 
the corresponding header record whenever a line item is updated so the ARLI 
dict item also updates AR-INVOICE header ZITEMSTATS with an "I" indirect update 
flag.

DICT ARLI INDEX..MODIFIEDZ
0001 I
0002 SUBR("ZIDESC.ITEMSTATS.UPDATE", 
"ARLI!":F0,""):SUBR("ZIDESC.ITEMSTATS.UPDATE", "AR-INVOICE!":F1,"I")
0004 \
0005 1L
0006 S
0049 used to create item created, modified, delete in zitemstats file - create 
index with this and no.nulls keyword
EOI 264 bytes

Alternate Key Index Summary for file ARLI
File... ARLI
Indices 3 (0 A-type, 0 C-type, 0 D-type, 3 I-type, 0 SQL, 0 S-type)
Index Updates.. Enabled, No updates pending

Index name  Type  BuildNulls  In DICT  S/M  Just Unique Field num/I-type
INDEX..MODIFIEDZ INot Reqd  No Yes  SL NSUBR("ZIDESC.ITE
MSTATS.UPDATE",
"ARLI!":F0,""):S
UBR("ZIDESC.ITEM
STATS.UPDATE", "
AR-INVOICE!":F1,
"I")


Subroutine ZIDESC.ITEMSTATS.UPDATE:

0001 SUB (RETURN.VALUE, ZITEMSTATS.ID, OPTIONS)
0002 * updates zitemstats with timestamps; create, modified, deleted
0003 *
0004 * Revision history:
0005 *
0006 * tdb.1190 2012-04-22_1602 new
0007 *
0008 *
0009 * input:  ZITEMSTATS.ID item id to use for ZITEMSTATS file
0010 * OPTIONS
0011 *I  update attb 5 with "I" for indirect 
reference
0012 *
0013 * output: RETURN.VALUE  NULL
0014 *
0015 * common vars: /ZITEMSTATS/
0016 *
0017 * called by:  INDEX..MODIFIEDZ dictionary items
0018 * calls:
0019 *
0020 * notes:
0021 *
0022 *
0023 $OPTIONS TIME.MILLISECOND
0024 *
0025 * @idx.iotype = 0 - Not being used from an index;
0026 * 1 - INSERT (new) record;
0027 * 2 - DELETE record;
0028 * 3 - Derive old index value;
0029 * 4 - Derive new index value;
0030 *
0031 COMMON /ZITEMSTATS/ F.ZITEMSTATS, ZITEMSTATS.FLAG.OPEN, ZITEMSTATS.WHO
0032 *
0033 EQU NUL TO ""
0034 *
0035 RETURN.VALUE = NUL
0036 *
0037 * the following handles initial call and logto commands
0038 *
0039 IF NOT(ZITEMSTATS.FLAG.OPEN) OR (@WHO # ZITEMSTATS.WHO) THEN
0040   OPEN "ZITEMSTATS" TO F.ZITEMSTATS ELSE
0041 RETURN ;*  to caller.
0042   END
0043 *
0044   ZITEMSTATS.FLAG.OPEN = 1
0045   ZITEMSTATS.WHO   = @WHO
0046 END
0047 *
0048 LOG.TYPE = NUL
0049 *
0050 BEGIN CASE
0051   CASE @IDX.IOTYPE = 0
0052 *
0053   CASE @IDX.IOTYPE = 1
0054 LOG.TYPE = "C" ;* new - created - easier to see than 'N' new
0055 *
0056   CASE @IDX.IOTYPE = 2
0057 LOG.TYPE = "D" ;* delete
0058 *
0059   CASE @IDX.IOTYPE = 3
0060 *
0061   CASE @IDX.IOTYPE = 4
0062 LOG.TYPE = "M" ;* modified
0063 *
0064 END CASE
0065 *
0066 IF LOG.TYPE # NUL THEN
0067 *
0068   DATE.NOW = DATE()
0069   TIME.NOW = TIME()
0070 *
0071 * handle instance of midnight between date/time functions
0072 *
0073   IF DATE.NOW # DATE() AND TIME() > TIME.NOW THEN DATE.NOW = DATE()
0074 *
0075   TIMESTAMP = DATE.NOW * 86400 + TIME.NOW
0076 *
0077   READU ZITEMSTATS$ FROM F.ZITEMSTATS, ZITEMSTATS.ID ELSE ZITEMSTATS$ = NUL
0078 *
0079   INDIRECT.FLAG = COUNT(OPTIONS, "I")
0080 *
0081   IF INDIRECT.FLAG THEN
0082 ZITEMSTATS$<1> = "M" ;* modified
0083   END ELSE
0084 ZITEMSTATS$<1> = LOG.TYPE
0085   END
0086 *
0087 * created
0088 *
0089   IF INDIRECT.FLAG ELSE
0090 IF LOG.TYPE = "C" THEN
0091   ZITEMSTATS$<2> = TIMESTAMP
0092 END
0093   END
0094 *
0095 * modified
0096 *
0097   ZITEMSTATS$<3> = TIMESTAMP
0098 *
0099 * deleted
0100 *
0101   IF INDIRECT.FLAG ELSE
0102 IF LOG.TYPE = "D" THEN
0103   ZITEMSTATS$<4> = TIMESTAMP
0104 END ELSE
0105   ZITEMSTATS$<4> = NUL
0106 END
0107   END
0108 *
0109   IF INDIRECT.FLAG THEN
0110 ZITEMSTATS$<5> = "I" ;* indirect update flag
0111   END ELSE
0112 ZITEMSTATS$<5> = EREPLACE(ZITEMSTATS$<5>, "I", "") ;* rem

Re: [U2] [UV] Do you avoid TRIGGERS because of the difficulty using DEBUG or RAID with them? Was: Universe Triggers

2013-08-27 Thread Hona, David

Yes, Chuck a typo in the release number and much less capable than SQL style 
triggers...so a backwards step too. But far less overhead and drama to 
setup/maintain...



-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charles Stevenson
Sent: Wednesday, 7 August 2013 7:58 AM
To: U2 Users List
Subject: Re: [U2] [UV] Do you avoid TRIGGERS because of the difficulty using 
DEBUG or RAID with them? Was: Universe Triggers

@IDX.IOTYPE
Thanks to a fellow u2-list member who mailed me privately.
I think David Hona was maybe thinking of that,  but it's available at 11.1, not 
10.1.

Chuck

On 8/5/2013 6:23 PM, Perry Taylor wrote:
> Rocket added an @variable (don't recall the name of it) that tells which call 
> is being made.
>
> Perry
>
> -Original Message-
> From: u2-users-boun...@listserver.u2ug.org 
> [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charles 
> Stevenson
> Sent: Saturday, August 03, 2013 9:40 AM
> To: U2 Users List
> Subject: Re: [U2] [UV] Do you avoid TRIGGERS because of the difficulty 
> using DEBUG or RAID with them? Was: Universe Triggers
>
> David,
>
> I didn't understand your 1st clause, "Now that (from UV10.1) 
> Index-based triggers are officially supported,...".
>
> By "index-based triggers",  I assume you mean the trick of indexing an 
> I-descriptor that calls a subroutine that updates some other file, 
> which is generally not the sort of thing you expect such a subroutine to do.
>
> What is this "official support"?  Did I miss an announcement, a change 
> in the documentation, or a whitepaper?
>
> And  by "support"  -  just to get my hopes up beyond all reason - does 
> that mean they've introduced some mechanism (@variable?) to help 
> distinguish among calls of the subroutine for insert (where indexing 
> calls the subroutine once, to find the new value to index) delete 
> (where indexing calls the subroutine 1x, to find the value to delete), 
> and change (where indexing calls the subroutine 2x, once with the old 
> version of the record, once with the new, to see whether the indexed 
> value has changed and, if so,  what to delete, what to add.
> Distinguishing these has always been tricky for the general case.
>
> Hope springs eternal,
> Chuck
>
> On 8/1/2013 12:32 PM, Hona, David wrote:
>> Now that (from UV10.1) Index-based triggers are officially supported, can 
>> these replace your SQL-based triggers? These have less functionality and 
>> less overhead, but that's the price you have to pay
>>
>> Can't say I had a chance to try it for myself...yet...!
>>
>>
>>
>> -Original Message-
>> From: u2-users-boun...@listserver.u2ug.org 
>> [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charles 
>> Stevenson
>> Sent: Saturday, 27 July 2013 5:32 AM
>> To: U2 Users List
>> Subject: [U2] [UV] Do you avoid TRIGGERS because of the difficulty 
>> using DEBUG or RAID with them? Was: Universe Triggers
>>
>> How many people avoid using triggers BECAUSE of the virtual impossibility of 
>> using RAID with Triggers?
>>
>> On 7/26/2013 12:33 PM, Phil Walker wrote:
>>> I won't be holding my breath Charles ;-)
>>>
>>> -Original Message-
>>> From: u2-users-boun...@listserver.u2ug.org
>>> [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charles 
>>> Stevenson
>>> Sent: Friday, 26 July 2013 9:22 p.m.
>>> To: U2 Users List
>>> Subject: Re: [U2] Universe Triggers
>>>
>>> re. triggers & Raid,  I could not agree with Phil more.  Well said.
>>> Come on, Rocket!
>>>
>>> On 7/19/2013 1:32 AM, Phil Walker wrote:
 Ken,

 I am glad you raised the issue about debugging a program with a file which 
 has a trigger attached. I have been on to UV (Vmark/Ardent/IBM/Rocket for 
 ages about fixing this pushing for the ability to be able to step into the 
 trigger code, but at a VERY MINIMUM being able to debug the program and 
 perform the write on the file, and in effect step over the trigger 
 subroutine and carry on debugging. The issue is the trigger subroutine 
 cannot support input, so what UV have done is basically say you are using 
 the debugger so you are inputting debug commands so you will abort. They 
 need to turn this restriction off for debugging so that either of the 
 above two scenarios is supported.

 In a Microsoft world I can debug anything through the connected world of 
 web/databases etc..

 Have had no feedback from UV

 -Original Message-
 From: u2-users-boun...@listserver.u2ug.org
 [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Ken Ford
 Sent: Friday, 19 July 2013 9:48 a.m.
 To: u2-users@listserver.u2ug.org
 Subject: Re: [U2] Universe Triggers

 Dan,
 In addition to the other responses you have received, I suggest the 
 following:
 1. Have one master file trigger subroutine (globally catalogued) that 
 calls subr

Re: [U2] Select from one file with save-list from another file

Re: [U2] universe 11.1 triggers via indexing and @IDX.IOTYPE sample

Re: [U2] Select from one file with save-list from another file

Re: [U2] Select from one file with save-list from another file

Re: [U2] Select from one file with save-list from another file

Re: [U2] Select from one file with save-list from another file

Re: [U2] Select from one file with save-list from another file

Re: [U2] Select from one file with save-list from another file

Re: [U2] Select from one file with save-list from another file

Re: [U2] Select from one file with save-list from another file

Re: [U2] Select from one file with save-list from another file

Re: [U2] Select from one file with save-list from another file

Re: [U2] Select from one file with save-list from another file

Re: [U2] Select from one file with save-list from another file

Re: [U2] Select from one file with save-list from another file

Re: [U2] Select from one file with save-list from another file

[U2] Select from one file with save-list from another file

Re: [U2] universe 11.1 triggers via indexing and @IDX.IOTYPE sample

Re: [U2] universe 11.1 triggers via indexing and @IDX.IOTYPE sample

Re: [U2] UDT 7.1 Indexing question

Re: [U2] UDT 7.1 Indexing question

Re: [U2] UDT 7.1 Indexing question

Re: [U2] UDT 7.1 Indexing question

Re: [U2] UDT 7.1 Indexing question

Re: [U2] UDT 7.1 Indexing question

Re: [U2] UDT 7.1 Indexing question

[U2] UDT 7.1 Indexing question

Re: [U2] universe 11.1 triggers via indexing and @IDX.IOTYPE sample

[U2] universe 11.1 triggers via indexing and @IDX.IOTYPE sample

Re: [U2] [UV] Do you avoid TRIGGERS because of the difficulty using DEBUG or RAID with them? Was: Universe Triggers

30 matches

Site Navigation

Mail list logo

Footer information