SQL queries on tables: Difference between revisions
(→Note) |
(||1|SELECT Nr,Frequency,Peak,[Peak Limit],[Peak Difference],[Peak Status],Status FROM PEAKTABLE LIMIT 5||) |
||
| Line 132: | Line 132: | ||
==Limit the results== | ==Limit the results== | ||
to limit the result use : | |||
||1|SELECT Nr,Frequency,Peak,[Peak Limit],[Peak Difference],[Peak Status],Status FROM PEAKTABLE LIMIT 5|| | |||
== Sorting == | |||
||1|SELECT Nr,Frequency,Peak,[Peak Limit],[Peak Difference],[Peak Status],Status FROM PEAKTABLE ORDER BY Angle DESC|| | |||
==Note== | ==Note== | ||
Revision as of 21:19, 29 November 2012
The report generator of RadiMation supports the usage of SQL Queries on tables that are provided by the report generator. This makes it possible to include a customized table in the final report.
In combination with the 'SQL' queries, more advanced tables can be created like:
- Changing the order of the columns in the table
- Hiding selected columns from the table
- Change the sorting of the rows, based on a specific column
- Filtering of the rows, based on the data that is included
The most simple SQL statement is:
SELECT * FROM <TABLE>
Where '<TABLE>' is an existing report generator code that includes a table in the report. This simple SQL statement can be extended with:
- An optional 'WHERE' clause can be included that defines an additional filter that should be used on the rows.
- An optional 'ORDER BY' clause can be included that specifies any specific ordering of the rows.
- The names of the columns that can be used in the SQL queries is the same as they appear in the table itself. Any column names that include a space, should be included in '[ ]'
Examples[edit]
Only the frequencies above 50 MHz will be included:
||SELECT * FROM AD CHANNEL 4,3,2,1 LIST WHERE Frequency > 50E6||
Only the frequencies between 1 Hz and 20 MHz will be included, where the AD-channel was measured out of the specified range ('TABLE'), and the tables is reverse-sorted on the frequency column.
||SELECT * FROM AD CHANNEL 8,7,6,5,4,3,2,1 TABLE WHERE FREQUENCY >= 1 and FREQUENCY <= 20E6 order by Frequency desc||
Include the 'EQUIPMENT TABLE' including all the columns (also the hidden columns)
||SELECT ALL FROM EQUIPMENT TABLE||
Inserting the Peak limit into the table This example takes you step by step in configuring the table you want. We are creating the code "||1|SELECT ALL FROM PEAKTABLE||" in a template.
This will give us all the know information inside the table. The following table has been generated with an multiband test.
| Nr | Nr_RAW | Frequency | Frequency_RAW | Peak | Peak_RAW | Peak Limit | Peak Limit_RAW | Peak Difference | Peak Difference_RAW | Peak Correction | Peak Correction_RAW | Peak Status | Peak Status_RAW | Status | Status_RAW | Angle | Angle_RAW | Height | Height_RAW | Polarization | Polarization_RAW |
| 1 | 1 | 829,92 MHz | 829,92 MHz | 80 dBµV | 80 dBµV | 60 dBµV | 60 dBµV | 20 dB | 20 dB | 0,00 dB | 0,00 dB | Fail | Fail | Fail | Fail | 0 Degree | 0 Degree | 1 m | 1 m | Horizontal | Horizontal |
We would like to have a table with:
- Number
- Frequency
- Peak
- Peak Limit
- Peak difference to the limit
- Peak status
- Peak status overall
We try to do this with the following code:
||1|SELECT Nr,Frequency,Peak,Peak Limit,Peak Difference,Peak Status,Status FROM PEAKTABLE||
If you try this code, you will find out that it does not work.
How is this possible?
When you look closely to remarks made above you will find that you need to insert '[]' around keywords with spaces in them
Try:
||1|SELECT Nr,Frequency,Peak,[Peak Limit],[Peak Difference],[Peak Status],Status FROM PEAKTABLE||
You will find that this does work
The result will look like:
| Nr | Frequency | Peak | Peak Limit | Peak Difference | Peak Status | Status |
| 1 | 829,92 MHz | 80 dBµV | 60 dBµV | 20 dB | Fail | Fail |
Limit the results[edit]
to limit the result use :
||1|SELECT Nr,Frequency,Peak,[Peak Limit],[Peak Difference],[Peak Status],Status FROM PEAKTABLE LIMIT 5||
Sorting[edit]
||1|SELECT Nr,Frequency,Peak,[Peak Limit],[Peak Difference],[Peak Status],Status FROM PEAKTABLE ORDER BY Angle DESC||
Note[edit]
Internally the SQL statements and the table data itself, are passed through the SQLite database engine. Because the SQLite database engine is used, almost all variations of the SQL queries are supported. The SQLite database engine is included in the setup program of RadiMation, and will always be installed.
 |
|