The AUX schema contains helpful tables for a variety of common tasks:
List all the queries saved by the trusted users
| SAVEDQUERIES | ||||||
|---|---|---|---|---|---|---|
| Column | ID | NAME | AUTHOR | MODIFIEDON | DESCRIPTION | QUERY |
| Type | BIGINT | VARCHAR | VARCHAR | TIMESTAMP | VARCHAR | VARCHAR |
| Index | x | x | ||||
It allows listing all the users' saved queries and search by ID or AUTHOR.
Example:
select * from AUX.SAVEDQUERIES where AUTHOR = 'admin'
| ID | NAME | AUTHOR | MODIFIEDON | DESCRIPTION | QUERY |
|---|---|---|---|---|---|
| 364 | folders | admin | 2020-01-29 19:53:05.633 | SELECT * FROM ZEPHYR.CYCLES c join ZEPHYR.CYCLEFOLDERS f on f.PROJECTID = c.PROJECTID and f.VERSIONID = c.VERSIONID and f.CYCLEID = c.ID where PROJECTKEY = 'MSP' | |
| 365 | cycles | admin | 2020-01-28 22:57:53.234 | SELECT * FROM ZEPHYR.CYCLES where projectkey = 'MSP' |
Generate all dates in a given period
TIMEBYDAY | |||||
|---|---|---|---|---|---|
Column | TIMEID | YYYYMMDD | THEDATE | THEDAY | THEMONTH |
Type | INT | INT | DATE | VARCHAR | VARCHAR |
Indexed | x | ||||
Column | THEYEAR | DAYOFMONTH | WEEKOFYEAR | MONTHOFYEAR | QUARTER |
Type | INT | VARCHAR | INT | INT | VARCHAR |
Indexed | |||||
Column | STARTDATE | ENDDATE | |||
Type | VARCHAR | VARCHAR | |||
Indexed | x | x | |||
It supports dynamic interval time creation values similarly as the Mondrian AutoGeneratedDateTable the functionality described in this Julian Hyde's article.
Example:
select * from AUX.TIME_BY_DAY_GENERATED where startDate='2016-01-01' and endDate='2016-12-31'