SQL queries on tables: Difference between revisions

From RadiWiki
Jump to navigation Jump to search
No edit summary
(Redirected page to Chapter 13#SQL queries on tables)
 
Line 1: Line 1:
The [[report generator]] of [[RadiMation]] supports the usage of [[wikipedia:SQL|SQL Queries]] on tables that are provided by the report generator.
#REDIRECT [[Chapter_13#SQL_queries_on_tables]]
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 '[    ]'
 
The general keywords can't be used for selection within a table. For an example of keywords used within the table selection see the [[Chapter_13#Radiated_Emission_Table | Radiated Emission Table]]
== Examples ==
 
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.
 
<TABLE border=1>
  <TR>
  <TD>Peak Number</TD>
  <TD>Peak Number_RAW</TD>
  <TD>Frequency</TD> 
  <TD>Frequency_RAW</TD>
  <TD>Peak</TD>   
  <TD>Peak_RAW</TD>
  <TD>Peak Limit</TD>
  <TD>Peak Limit_RAW</TD>
  <TD>Peak Difference</TD>
  <TD>Peak Difference_RAW</TD>
  <TD>Peak Correction</TD>
  <TD>Peak Correction_RAW</TD>
  <TD>Peak Status</TD>
  <TD>Peak Status_RAW</TD>
  <TD>Status</TD>
  <TD>Status_RAW</TD>
  <TD>Angle</TD>
  <TD>Angle_RAW</TD>
  <TD>Height</TD>
  <TD>Height_RAW</TD>
  <TD>Polarization</TD>
  <TD>Polarization_RAW</TD>       
  </TR>
  <TR>
  <TD>1</TD> 
  <TD>1</TD>     
  <TD>829,92 MHz</TD>
  <TD>829,92 MHz</TD>   
  <TD>80 dBµV</TD> 
  <TD>80 dBµV</TD>
  <TD>60 dBµV</TD>
  <TD>60 dBµV</TD>
  <TD>20 dB</TD>
  <TD>20 dB</TD>
  <TD>0,00 dB</TD>
  <TD>0,00 dB</TD>
  <TD>Fail</TD>
  <TD>Fail</TD>
  <TD>Fail</TD>
  <TD>Fail</TD>
  <TD>0 Degree</TD>
  <TD>0 Degree</TD>
  <TD>1 m</TD>
  <TD>1 m</TD>
  <TD>Horizontal</TD>
  <TD>Horizontal</TD>
</TR>
</TABLE>
 
We would like to have a table with:
*Peak 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 Peak Number,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 [Peak Number],Frequency,Peak,[Peak Limit],[Peak Difference],[Peak Status],Status FROM PEAKTABLE||
You will find that this does work
 
The result will look like:
<TABLE border=1>
  <TR>
  <TD>Peak Number</TD>
  <TD>Frequency</TD>
  <TD>Peak</TD>
  <TD>Peak Limit</TD>
  <TD>Peak Difference</TD>
  <TD>Peak Status</TD>
  <TD>Status</TD>       
  </TR>
  <TR>
  <TD>1</TD>     
  <TD>829,92 MHz</TD> 
  <TD>80 dBµV</TD> 
  <TD>60 dBµV</TD> 
  <TD>20 dB</TD>
  <TD>Fail</TD>
  <TD>Fail</TD>
</TR>
</TABLE>
 
==Limit the results==
 
to limit the result use :
||1|SELECT [Peak Number],Frequency,Peak,[Peak Limit],[Peak Difference],[Peak Status],Status FROM PEAKTABLE LIMIT 5||
 
== Sorting ==
||1|SELECT [Peak Number],Frequency,Peak,[Peak Limit],[Peak Difference],[Peak Status],Status FROM PEAKTABLE ORDER BY Angle DESC||
 
==Note==
Internally the SQL statements and the table data itself, are passed through the [[wikipedia:SQLite|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.
 
{{Version|5.2}}
 
[[Category:Version 5.2]]
[[Category:Report generator]]
[[Category:RadiMation]]
[[Category:Proposal for inclusion in manual]]

Latest revision as of 13:18, 22 December 2015

Redirect to:

Retrieved from "https://wiki.radimation.com/wiki/index.php?title=SQL_queries_on_tables&oldid=14496"