SQL Reference

The ER2 ODBC Driver supports common SQL syntax and keywords that can be used to create custom SQL statements, giving you the ability to filter and manipulate the data fetched from the Master Server.

The table below describes the SQL support matrix for the Enterprise Recon ODBC Driver.

Keyword	Description	Example
SELECT	Beginning of a row retrieve request.	SELECT * FROM SCANREPORT_SUMMARY
`column_name`	Specify the column name to retrieve from a specific table.	SELECT `GUID` FROM DATA_GROUP
`column_name`.`table_name`	Specify the column name to retrieve from a specific table. Used to eliminate column name conflicts across multiple data tables.	SELECT `GUID`.`DATA_TARGET` FROM DATA_TARGET
*	Alias for all columns.	SELECT * FROM DATA_TARGET
AS `new_column_name`	Rename a selected column in the output table.	SELECT `GUID`.`DATA_TARGET` AS `GUID` FROM DATA_TARGET
`column_name`, `column_name`	Separator for selecting multiple columns.	SELECT `TUID`, `GUID` FROM DATA_TARGET
FROM	Specify one or more tables to retrieve data from.	SELECT * FROM DATA_TARGET
`table`	Specify table name to retrieve data from.	SELECT * FROM `DATA_TARGET`
AS `new_table_name`	Rename a selected table.	SELECT `GUID`.`DATA_TARGET` FROM `DATA_TARGET` AS `NEW_TABLE`
`table_name`, `table_name`	Separator for selecting multiple tables.	SELECT * `FROM DATA_ACTION_LOG`, `DATA_ACTION_TYPES`
WHERE	Specify one or more conditions to filter the returned data set. To filter the data set based on timestamp (e.g. SCANREPORT_MATCH.START), specify the value in Unix time format. To display match information for scans that started later than 00:00 AM on 1 January 2021 (GMT timezone), use SCANREPORT_MATCH.START > 1609459200.	SELECT * FROM DATA_TARGET WHERE TUID = 6622288671022978211 SELECT * FROM SCANREPORT_MATCH WHERE SCANREPORT_MATCH.START > 1609459200
AND / &&	Separator for multiple conditions.	SELECT * FROM DATA_TARGET WHERE TUID = 6622288671022978211 AND GUID = 2471766758845071288
NOT / !	Include results that do not meet the specified conditions.	SELECT * FROM DATA_TARGET WHERE NOT TUID = 6622288671022978211
=	Equality condition.	SELECT * FROM DATA_TARGET WHERE LABEL = 'MY-WINDOWS-MACHINE'
!= / <>	Inequality condition.	SELECT * FROM DATA_ACTION_TYPES WHERE ACTIONID <> 2
<	Less-than condition.	SELECT * FROM SCANREPORT_SUMMARY WHERE MATCHES < 2000
<=	Less-than or equal-to condition.	SELECT * FROM SCANREPORT_SUMMARY WHERE MATCHES <= 2000
>	Greater-than condition.	SELECT * FROM SCANREPORT_SUMMARY WHERE MATCHES > 2000
>=	Greater-than or equal-to condition.	SELECT * FROM SCANREPORT_SUMMARY WHERE MATCHES >= 2000
IN	Equality condition with one or more possible values.	SELECT * FROM SCANREPORT_SUMMARY WHERE CUID IN (1,20,25,60)
LIKE	Equality condition with partial or wildcard matching.	SELECT * FROM SCANREPORT_SUMMARY WHERE ROOT LIKE '%MARIADB%'
DISTINCT	Return only unique rows from the data set.	SELECT DISTINCT REPORTID FROM SCANREPORT_SUMMARY
INNER JOIN	Explicit join of two tables.	SELECT * FROM FOO INNER JOIN BAR ON FOO.X = BAR.Y
ON	Define criteria for explicit join of two tables.	SELECT * FROM FOO INNER JOIN BAR ON FOO.X = BAR.Y
LIMIT	Limit the number of rows returned from the data set.	SELECT * FROM SCANREPORT_SAMPLE LIMIT 10 OFFSET 20
OFFSET	Specify the starting point to return rows from the data set.	SELECT * FROM SCANREPORT_SAMPLE LIMIT 10 OFFSET 20