cliu
d7e30fa46e
New skill reporting: REPORT Service checks, interactive RQL, report definitions by quick load, file output devices. New task system-management/dss-tools.md: DSS processes, dss.sup, quick load directives, dssutl, DSS compiler syntax check. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
77 lines
3.5 KiB
Markdown
77 lines
3.5 KiB
Markdown
---
|
||
task: Try RQL queries in the interactive report generator; write CSV with SPOOL
|
||
version: R1.03
|
||
source: IM 36K10R40-01EN §4; IM 36K10P20-01EN §3–6, §9; IM 36K10C30-01EN §3.2.3; practiced on the test system and CI-Eng-Sample 2026-10-01
|
||
status: verified
|
||
---
|
||
|
||
# RQL in the interactive generator
|
||
|
||
`rptgen -s rptgen.rpt` starts a report generator in the console (prompt `RPT>`). It reads RQL,
|
||
prints results at once and needs no report definition — the quickest way to test a query or look
|
||
into a dataset. CI Server must be running. Read-only unless the query uses `SPV_*` or
|
||
`GPV_VALUE(…, <non-zero>)`.
|
||
|
||
## 1. Run a query file
|
||
|
||
Put the statements in a file (each ends with `;`) and pipe it in; end with `QUIT;`:
|
||
|
||
```
|
||
set @next 0;
|
||
set @width 132;
|
||
column t as 'DD-MMM-YYYY hh:mm:ss' heading 'Time';
|
||
select name, item_value, status from item_val where name like 'CI-Eng-Sample.TANK1.%';
|
||
select sample_time t, item_value, quality from item_history
|
||
where item_name = 'CI-Eng-Sample.TANK1.LEVEL' and group_name = 'CIENG_SCAN10S'
|
||
and sample_time > @date - 600 order by sample_time;
|
||
select count(item_value), min(item_value), max(item_value), avg(item_value) from item_history
|
||
where item_name = 'CI-Eng-Sample.TANK1.LEVEL' and sample_time > @date - 3600;
|
||
select gpv_value('CI-Eng-Sample.TANK1.LEVEL', 0), gpv_status('CI-Eng-Sample.TANK1.LEVEL');
|
||
quit;
|
||
```
|
||
|
||
```
|
||
rptgen -s rptgen.rpt < query.rql > query.out
|
||
```
|
||
|
||
(Git Bash: `timeout 60 rptgen -s rptgen.rpt < query.rql`; PowerShell does not support `<`, use
|
||
`cmd /c "rptgen -s rptgen.rpt < query.rql"`.)
|
||
|
||
- `set @next 0;` first, otherwise output stops after each page (`Press <RETURN> to continue`).
|
||
- `rptgen.rpt` has `@page_size 23`, so headings repeat every 23 lines; `set @page_size 0;` for one
|
||
long list.
|
||
- Errors show the code and a caret under the position, e.g. `RPT-E-ALIASSEL` (an alias used twice
|
||
in one SELECT), `RPT-E-UNKFLDSPC` (unknown field). Field names: `dssqld -f -d <DATASET>`.
|
||
- `LOOP` does not work here (`RPT-E-LPNOTAL`); test loops and cursors in a report definition
|
||
(`define-reports.md`).
|
||
- `@P0`…`@P7` and `@NAME` do not exist here; `SET` them to test a report query.
|
||
|
||
## 2. Dates
|
||
|
||
RQL dates are seconds since 1970 in **local time**. `@date` is now; constants like
|
||
`01-Oct-2026 15:00` or `12:00` (today) need no quotes; `number('01-Oct-2026 15:00')` converts text.
|
||
Show them with a date format (`column t as 'DD-MMM-YYYY hh:mm:ss'`). `to_gmt(@date)` gives real
|
||
Unix time (10 h less on a UTC+10 system) — use it only when another program needs UTC.
|
||
|
||
## 3. CSV file with SPOOL
|
||
|
||
```
|
||
spool 'C:\Users\Public\Yokogawa\tls\lst\cieng_levels.csv';
|
||
set @export 1;
|
||
select name, item_value, status from item_val where name like 'CI-Eng-Sample.TANK1.%';
|
||
spool;
|
||
set @export 0;
|
||
```
|
||
|
||
Result: one line per row, strings quoted (`"CI-Eng-Sample.TANK1.LEVEL",61,"NORMAL"`), no headings.
|
||
File name ≤ 79 characters, literal (no `%variables%`), overwritten each time, never cleaned up by
|
||
CI Server — delete it when done. The same statements work inside a report definition.
|
||
|
||
## Verified (2026-10-01)
|
||
|
||
Current values, history of `CIENG_EVENT` / `CIENG_SCAN10S` (count 299 in an hour at 10 s), group
|
||
functions, `GROUP BY` on `unit_df`, `GPV_VALUE`/`GPV_STATUS`/`GPV_QUALITY` (61, 9, 0), string and
|
||
date functions (`SUBSTR`, `DECODE`, `NUMBER`, `DRAW`, `LINE`, `MONTH_DAYS`, `TO_GMT`), integer vs
|
||
real arithmetic (`12/5+10.0` = 12), CSV via `SPOOL` (file removed afterwards). Aggregated history
|
||
(`interpolation = '30Minutes'`) returned no rows because the test system had no completed
|
||
aggregation periods (also empty through `dssqld`).
|