//
// Demonstration of how to use the various MaxDB functions
//
//   -> system supplied functions (special register)
SELECT ROWNO
      ,NULL
      ,USER
      ,USERGROUP
      ,SYSDBA
      ,CURRENT_SCHEMA
      ,UID
      ,DATE
      ,TIME
      ,TIMESTAMP
      ,UTCDATE
      ,TIMEZONE
      ,UTCDIFF
      ,TRUE
      ,FALSE
      ,TRANSACTION
  FROM dual
//
//   -> one user defined function  (an example to generate random data)
DROP   FUNCTION RAND
//
CREATE FUNCTION RAND (weight SMALLINT) RETURNS FIXED(38,37) AS
  VAR randval FIXED(38,37);
  SELECT ((134775818 * MICROSECOND(TIMESTAMP) + :weight) MOD 231) / 230
     INTO :randval
     FROM dbadmin.dual;
  RETURN randval;
//
//   -> embedding UDF in SQL
SELECT 
       RAND(1)                        AS r0
     , FIXED(RAND(1)*10,1)            AS r1
     , FIXED(RAND(1)*100,2)           AS r2
     , FIXED(RAND(1)*1000,3)          AS r3
     , FIXED(RAND(1)*10000,4)         AS r4
     , FIXED(RAND(1)*100000,5)        AS r5
     , FIXED(RAND(1)*1000000,6)       AS r6
     , FIXED(RAND(1)*10000000,7)      AS r7
     , FIXED(RAND(1)*100000000,8)     AS r8
     , FIXED(RAND(1)*1000000000,9)    AS r9
     , FIXED(RAND(1)*10000000000,10)  AS r10
  FROM dual
//

> SELECT ROWNO       ,NULL       ,USER       ,USERGROUP       ,SYSDBA       ,CURRENT_SCHEMA       ,UID       ,DATE       ,TIME       ,TIMESTAMP       ,UTCDATE       ,TIMEZONE       ,UTCDIFF       ,TRUE       ,FALSE       ,TRANSACTION   FROM dual
> | ROWNO            | EXPRESSION1              | EXPRESSION2                      | EXPRESSION3                      | EXPRESSION4                      | EXPRESSION5                      | EXPRESSION6        | EXPRESSION7 | EXPRESSION8 | EXPRESSION9                | EXPRESSION10               | EXPRESSION11        | EXPRESSION12         | EXPRE | EXPRE | EXPRESSION15               |
> | ---------------- | ------------------------ | -------------------------------- | -------------------------------- | -------------------------------- | -------------------------------- | ------------------ | ----------- | ----------- | -------------------------- | -------------------------- | ------------------- | -------------------- | ----- | ----- | -------------------------- |
> |                1 | ?                        | MONA                             | MONA                             | DBADMIN                          | MONA                             |                 29 | 2008-02-17  | 10:44:27    | 2008-02-17 10:44:27.953000 | 2008-02-17 09:44:27.953000 |               10000 |                 1.00 |  TRUE | FALSE | 0x0000000000BB             |
> DROP   FUNCTION RAND
> CREATE FUNCTION RAND (weight SMALLINT) RETURNS FIXED(38,37) AS   VAR randval FIXED(38,37);   SELECT ((134775818 * MICROSECOND(TIMESTAMP) + :weight) MOD 231) / 230      INTO :randval      FROM dbadmin.dual;   RETURN randval;
> SELECT        RAND(1)                        AS r0      , FIXED(RAND(1)*10,1)            AS r1      , FIXED(RAND(1)*100,2)           AS r2      , FIXED(RAND(1)*1000,3)          AS r3      , FIXED(RAND(1)*10000,4)         AS r4      , FIXED(RAND(1)*100000,5)        AS r5      , FIXED(RAND(1)*1000000,6)       AS r6      , FIXED(RAND(1)*10000000,7)      AS r7      , FIXED(RAND(1)*100000000,8)     AS r8      , FIXED(RAND(1)*1000000000,9)    AS r9      , FIXED(RAND(1)*10000000000,10)  AS r10   FROM dual
> | R0                                                      | R1     | R2     | R3      | R4       | R5         | R6          | R7           | R8             | R9              | R10              |
> | ------------------------------------------------------- | ------ | ------ | ------- | -------- | ---------- | ----------- | ------------ | -------------- | --------------- | ---------------- |
> |                 0.6695652173913043478261000000000000000 |      7 |     67 |     670 |     6696 |      66957 |      669565 |      6695652 |       66956522 |       669565217 |       6695652174 |