[U2] I just discovered this

Gregor Scott
System Group Manager
I have been working with UV for a few years now but I occasionally uncover some functionality I never knew existed that is really cool and useful.

Such as the invisible @TMP table (I just crated this post about it: http://wp.me/p1692U-1o).
It is documented in the "BASIC SQL Client Interface Guide" and the "UCI Developers Guide" for those needing documentary evidence that it does exist.

The premise of it is quite simple:

1. Create a dynamic array of data to be reported on.
2. Assign the array to a numbered select list
3. Use the SQL SELECT syntax to populate the @TMP table with the select list contents and produce an appropriate report/recordset.

An example always helps to understand.

The Test program:  TEST.@TMP
------
CATEG = "ACBDAADBCB"
INFO = ''
FOR II = 1 TO 10
   ROW = II
   ROW<2> = "ROW ":II
   ROW<3> = TIME()-(II*(if mod(II,2) then -1 else 1))
   ROW<4> = CATEG[RIGHT(II,1)+1,1]
   INFO<II> = CONVERT(@AM,@TM,ROW)
NEXT II
SELECTN INFO TO 9
------
   Note that each row's data is separated by @TM instead of an @VM. This is important for the SQL interface.

Compile and run the above program.

Using the active select list...

Try this:
   SELECT F1 FMT "4R" AS "Row", F2 FMT "20L" AS "Descr", F3 FMT "10R" CONV "MTS" AS "Time", F4 FMT "3L" AS "Code" FROM @TMP SLIST 9;

 This produces a simple output of the contents of select list 9 in a nicely formatted result.

        Row. Descr............... Time...... Code

           1 ROW 1                  10:45:54 C
           2 ROW 2                  10:45:51 B
           3 ROW 3                  10:45:56 D
           4 ROW 4                  10:45:49 A
           5 ROW 5                  10:45:58 A
           6 ROW 6                  10:45:47 D
           7 ROW 7                  10:46:00 B
           8 ROW 8                  10:45:45 C
           9 ROW 9                  10:46:02 B
          10 ROW 10                 10:45:43 A

        10 records listed.

                >

For a more complex sort sequence, try this one:
  SELECT F1 FMT "4R" AS "Row", F4 FMT "3L" AS "Category", F3 FMT "10R" CONV "MTS" AS "RowTime" FROM @TMP SLIST 9 ORDER BY Category DESC, RowTime ASC;

        Row. Category RowTime...

           6 D          11:20:15
           3 D          11:20:24
           8 C          11:20:13
           1 C          11:20:22
           2 B          11:20:19
           7 B          11:20:28
           9 B          11:20:30
          10 A          11:20:11
           4 A          11:20:17
           5 A          11:20:26

        10 records listed.
        >

Gotchas to be aware of:
1. You cannot use the WHERE clause in the select statement. All the rows (attributes) in the select list will be output
2. You cannot use the "TO SLIST {n}" option to create another select list from the re-ordered output.
3. To order the output using non-default formatting (i.e. "10L" you must use the 'F1 FMT "{xx}" AS "{ColName}"' syntax in the output and then use {ColName} in the ORDER BY section
4. Only the SQL form of the SELECT verb works with the @TMP table - it does not exist for UV LIST & SELECT statements



_______________________________________________
U2-Users mailing list
[email protected]
http://listserver.u2ug.org/mailman/listinfo/u2-users