How to get/query the list of files in a folder from Envision

Say you have a list of file in HOLD_SHARED_MYFOLDER directory in Colleague, and you want to read in the list of files in MYFOLDER and do something with them, maybe read or write or both.

What you need is read/write access to MYFOLDER. Your admin should know how to set it up, and a VOC entry should already be created for it. 

In Colleague Studio, use the following code to get back a list of files inside MYFOLDER directory:

X.STMT = 'SELECT HOLD_SHARED_MYFOLDER'
CALL S.EXECUTE(X.STMT)
XL.FILES.NAME = ''
CALL S.READLIST(XL.FILES.NAME,0,X.STATUS)
CONVERT @FM:@SM:@VM:@AM TO @VM:@VM:@VM:@VM IN XL.FILES.NAME

Now you will have all the files' names in MYFOLDER populated in XL.FILES.NAME. To open each file up, you iterate them with S.OPEN.SEQ subroutine

FOR X = 1 to DCOUNT(XL.FILES.NAME, @VM)
   * Open the file
   CALL S.OPEN.SEQ("HOLD_SHARED_MYFOLDER", XL.FILES.NAME<1,X>, X, "R", X.ERROR.MSG)
   * Read from the file
   CALL S.READ.SEQ(X.TEXT, X.EOF, X, X.ERROR, X.MSG)
   * Close the file
   CALL S.CLOSE.SEQ(X, X.ERROR, X.MSG)
NEXT X

Bug when running uniquery

The following query failed to run:

X.STMT = "SELECT X.TABLE WITH X.FIELD1 EQ 'someValue' SAVING X.FIELD2"

CALL S.EXECUTE(X.STMT)

CALL S.READLIST(A.OUT, '', A.OUT2)

When using the SAVING keyword, the query has to return some data for it to work. If there is no X.FIELD1 with value equals to "somevalue" in table X.TABLE, the query will fail and return random data. I think it's whatever is in the active list 0. In this case, A.OUT will have random data and A.OUT2 will be '1'. 

SQL transaction for safety

In SQL studio, whenever an update to a table is perform, always test it first using TRANSACTION command. This ensures the execution of the query in its entity, and we can also have a preview on how the data will look like after the query execution.

DECLARE @TransactionName varchar(20) = 'Transaction1';
BEGIN TRAN @TransactionName

delete from C70_XTABLE
where C70_XTABLE_ID like '%Colleague%'

select * from C70_XTABLE

ROLLBACK TRAN @TransactionName;

After executing the whole query block, the data will remain unchanged, and we will have a sneak peek of what the query will do.

* Edit: The whole block can be written as:

begin transaction
(sql queries here)
rollback transaction

Found this gem in Ellucian's documentation: 2909: Explanation of Rules and Connectives

Colleague
Mnemonic RLDE

The internal documentation on the RLDE Rule Definition screen offers these connectives:

1: WITH With
2: AND And
3: EVERY and every
4: OR Or
5: OREVERY Or every
6: ORWITH Or with
7: OWE Or with every
8: WE With every

Explanation of each connective:

1. WITH
WITH works as a parenthetical AND to start a new true/false condition.

2. AND
AND works with the previous line to determine true or false. The AND statement does *not* start a new parenthetical.

3. EVERY
The EVERY connector evaluates every value within a multi-valued field. Each value in the multi-valued field must equal the defined condition for a true result.

4. OR
OR works with the previous line to determine true or false. The OR connective does *not* start a new parenthetical.

5. OREVERY
Combines the OR and EVERY connectives. As such, it does not start a new parenthetical but works with the previous line to determine true or false. Each value in the multi-valued field must equal the defined condition for a true result.

6. ORWITH
ORWITH begins a new parenthetical to establish a true or false condition. This is in contrast to the WITH connective which is inclusive (AND) whereas ORWITH is exclusive (OR).

7. OWE (ORWITHEVERY)
OWE functions just like ORWITH except that it is used to evaluate multi-valued fields. OWE begins a new exclusive parenthetical and every value in the multi-valued field being evaluated must be equal to the defined condition for a true result.

8. WE (WITHEVERY)
WE combines the WITH and EVERY connectives. As such, it *does* start a new parenthetical. Each value in the multi-valued field being evaluated must be equal to the defined condition for a true result.


To properly build a rule with combinations of OR/AND, keep these facts in mind:

1. The syntax processor within UniData will process AND and OR in the order it finds them from left to right of your sentence. A and B or C or D and E won't necessarily produce the results you expect. For example, if your criteria are as follows:
WITH LAST.NAME EQ 'Smith'
AND STATE EQ 'Virginia'
OR STATE EQ 'Maryland'
OR STATE EQ 'Pennsylvania'
AND FIRST.NAME EQ 'William'

Your result set would consist of persons with last name Smith that live in Virginia and first name William, OR people who live in Maryland or Pennsylvania with first name of William.

2. In the table above, #1 WITH and #2 AND are NOT synonymous and cannot be used inter-changably. They are only inter-changable in a simplest case like A and B and C and D. Here WITH A AND B AND C AND D is the same as WITH A WITH B WITH C WITH D. The key point to remember is that WITH *does* start a new parenthetical while AND does *not* start a new parenthetical.

3. Putting 2 values on the right-hand side of an expression acts as an implied OR. If you want everyone in the states of New York and New Jersey, you can write it as STATE = 'NY','NJ'. This translates as 'with state equal New York OR New Jersey. Note that a comma must be placed between the right-hand side values, which is different than the normal query structure.

4. The best method to get the syntax accurate is to write what you want as a query sentence, being careful to use the word WITH as parentheses around groups. Then use the examples below to create the rule. The second column shows every possible variation of OR and AND with 4 variables. The third column repeats the 2nd, but only uses parentheses when they are necessary to keep the logic accurate.

1 (A and B) and (C and D) A and B and C and D
2 (A and B) and (C or D) A and B and (C or D)
3 (A and B) or (C and D) (A and B) or (C and D)
4 (A and B) or (C or D) (A and B) or C or D
5 (A or B) and (C and D) (A or B) and C and D
6 (A or B) or (C and D) A or B or (C and D)
7 (A or B) or (C or D) A or B or C or D

Here's how the rules should look for each case:
1 A and B and C and D
1st choice 2nd choice (synonymous)
WITH A WITH A
AND B WITH B
AND C WITH C
AND D WITH D

2 A and B and (C or D)
WITH A
AND B
WITH C
OR D

3 (A and B) or (C and D)
WITH A
AND B
ORWITH C
AND D

4 (A and B) or C or D
WITH A
AND B
OR C
OR D

5 (A or B) and C and D
WITH A
OR B
WITH C
AND D

6 A or B or (C and D)
WITH A
OR B
ORWITH C
AND D

7 A or B or C or D
1st choice If you want several values of one variable, use
WITH A WITH variable = 'A' , 'B' , 'C' , 'D'
OR B
OR C
OR D