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 U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
