SQL keywords

This list includes keywords currently supported by Network Reporting.

as

Provides a local alias for a SQL expression.

expression AS output_name

SELECT vid__v AS "Entity ID" FROM hcp

asc

Ascending; usually used in an ORDER BY clause.

ORDER BY expression ASC

SELECT first_name__v, last_name__v FROM hcp ORDER BY last_name__v ASC

desc

Descending; usually used in an ORDER BY clause.

ORDER BY expression DESC

SELECT first_name__v, last_name__v FROM hcp ORDER BY last_name__v DESC

distinct

Eliminates duplicate rows from a table.

SELECT DISTINCT column_name, ...

SELECT DISTINCT hco_type__v FROM hco

explode as

Enables a query to incorporate multiple related fields in Network as if they were a single field in the expression. For more information, see Field groups.

SELECT ... EXPLODE [field_set] AS [alias] ... FROM [table]

SELECT vid__v, EXPLODE specialties AS specialty FROM hcp WHERE specialty IS NOT NULL

Input

-

Result

Transforms a single record with field sets into multiple output rows. One can use the result set as a subquery and perform further aggregation.

from

Derives a table from one or more other tables given in a comma-separated table reference list.

FROM table_reference [, table_reference [, ...]]

SELECT vid__v FROM hco

on

Evaluates a Boolean condition. Can be used in conjunction with the JOIN types.

table1 JOIN table2 ON table1_column = table2_column

SELECT hcp.vid__v, parenthco.parent_hco_vid__v FROM hcp INNER JOIN parenthco ON hcp.vid__v = parenthco.entity_vid__v

using

Specifies the column to use when two identical tables are joined. Supported in the FROM clause.

The USING syntax can be used only when the join_column from the two tables are identical.

Supported in the following commands:

  • INNER JOIN (or just JOIN)

  • LEFT (OUTER) JOIN

  • RIGHT (OUTER) JOIN

table1 INNER JOIN table2 [ ON table1_column = table2_column | USING (join_column [, ...] ) ]

select hcp_revision.vid__v from hcp_revision join revision using(revision_id)