DQP supports a subset of SQL 92. The grammar that DQP aims to support is shown below. This release of DQP does not support the whole grammar. The following features are not supported:

  
statement
	: 	query_expression order_by? ';'? EOF 
	;
query_expression
	: 	query_term (set_op query_term)* 
	;
query_term
	:	query ( 'INTERSECT' query)*
	;
set_op	
	:	(setop='UNION' | setop='EXCEPT') 'ALL'? 
	;
query
	:	sub_query
		| 'SELECT' set_quantifier? select_list 
	  	  'FROM' table_expression 
	  	   ('WHERE' s1=search_condition)? 
	  	   ('GROUP BY' column_list)? 
	  	   ('HAVING' s2=search_condition)?
	;
set_quantifier
	:	'DISTINCT' | 'ALL';
sub_query
	:	'(' query_expression ')';

select_list
	:	'*'
	| 	derived_column (',' derived_column)*;
derived_column
	:	value_expression ('AS'? ID)?
	;
	
order_by
	:	'ORDER' 'BY' sort_specification (',' sort_specification)* ;
sort_specification
	:	column_name | INT | reserved_word_column_name
	;

reserved_word_column_name
	:	(tableid=ID'.')?
	        (s='DATE' | s='TIMESTAMP' | s='TIME' | s='INTERVAL' | s='YEAR' | 
	         s='MONTH' | s='DAY' | s='HOUR' | s='MINUTE' | s='SECOND' )	
	;
	
value_expression
	:	string_value_expression
	|	numeric_value_expression 
	;
numeric_value_expression
	: 	factor (('+'|'-') factor)* ;
	
factor	:	numeric_primary (('*'|'/') numeric_primary)*;

numeric_primary
	: 	('+'|'-')? value_expression_primary; 
value_expression_primary 
	:	'(' value_expression ')'
	| 	function
	| 	column_name
	| 	literal
	| 	sub_query
	;

literal	:	INT | FLOAT | NUMERIC | STRING | datetime | interval | 
                'NULL' | 'TRUE' | 'FALSE';
datetime
	:	('DATE' | 'TIMESTAMP' | 'TIME') STRING
	| 	(tableid=ID'.')?(s='DATE' | s='TIMESTAMP' | s='TIME')
	;
interval
	:	'INTERVAL' STRING ('YEAR' | 'MONTH' | 'DAY' | 'HOUR' | 'MINUTE' | 'SECOND')
	|	(tableid=ID'.')?(s='INTERVAL' | s='YEAR' | s='MONTH' | s='DAY' | s='HOUR' | 
	        s='MINUTE' | s='SECOND')
	;
		
function:	(name=ID) '(' value_expression? (',' value_expression)* ')' 
	| 	(name=ID) '(' '*' ')' 
	;

string_value_expression
	:	(column_name | STRING) ('||' (column_name | STRING))+
	;
	
table_expression
	:	table_reference
	;
table_reference
	:	table (',' table_reference)*
	;

join_type
	:	'RIGHT' 'OUTER'? 'JOIN' 
	|	'LEFT' 'OUTER'? 'JOIN' 
	|	'FULL' 'OUTER'? 'JOIN' 
	| 	'INNER'? 'JOIN' 
	;
table 	:	non_join_table (join_type non_join_table 'ON' search_condition)*
	;


non_join_table
	:	table_name correlation_specification? 
	| 	table_function correlation_specification 
	| 	sub_query correlation_specification 
	;
	
table_function
	:	name=ID '(' table_function_subquery? (',' table_function_subquery)* 
	        (','? table_function_param)* ')'
	;

table_function_subquery
	:	sub_query correlation_specification
	;

table_function_param
	:	search_condition
	|	value_expression
	;	

relation 
 	:	table_name 
 	|	table_function 
 	|	query 
 	;
	
search_condition
	:	boolean_factor ('OR' boolean_factor)*;
boolean_factor
	:	boolean_term ('AND' boolean_term)*
	;
boolean_term
	:	boolean_test
	|	'NOT' boolean_term ;
boolean_test
	:	boolean_primary;
boolean_primary
	:	predicate | '(' search_condition ')';	
predicate
	:	comparison_predicate | like_predicate | in_predicate | null_predicate | 
	        exists_predicate | between_predicate;
null_predicate
	:	row_value 'IS' 'NULL' 
	|	row_value 'IS' 'NOT' 'NULL' 
	;
in_predicate
	:	row_value 'NOT' 'IN' in_predicate_tail
	|	row_value 'IN' in_predicate_tail
	;
in_predicate_tail
	:	sub_query 
	| 	'(' (value_expression (',' value_expression)*) ')';
between_predicate
	:	value=row_value 'BETWEEN' btw1=row_value 'AND' btw2=row_value 
	|	value=row_value 'NOT' 'BETWEEN' btw1=row_value 'AND' btw2=row_value
	;
exists_predicate
	:	'EXISTS' sub_query;
comparison_predicate
	:	row_value ('=' | '<>' | '!=' | '<' | '>' | '>=' | '<=') 
	        ('ALL'|'SOME'|'ANY')? row_value;
like_predicate
	:	row_value 'LIKE' row_value
	|	v1=row_value 'NOT' 'LIKE' v2=row_value;

row_value
	:	value_expression | 'NULL' ;

correlation_specification
	:	('AS')? ID;	
table_name
	:	ID;
column_list
	:	(column_name | reserved_word_column_name) 
	        (',' (column_name | reserved_word_column_name))*;
column_name
	:	(tableid=ID'.')?columnid=ID
	;

ID	:	('a'..'z' | 'A'..'Z') ( ('a'..'z' | 'A'..'Z') | ('0'..'9') | '_' )* 
	|	'`' (~('\''|'\n'|'\r'|'`')) ( (~('\''|'\n'|'\r'|'`')) )* '`' ;
FLOAT	:	('0'..'9')+ '.' ('0'..'9')+ ;
INT	:	('0'..'9')+ ;
NUMERIC	:	(INT | FLOAT) 'E' INT ;
STRING	:	'"' (~('"'|'\n'|'\r'))*  '"'
	|	'\'' (~('\''|'\n'|'\r'))*  '\'';
WS	:	(' ' | '\t' | '\r' | '\n' ) {skip();} ;

DQP supports aggregation and scalar functions. These are extensibility points of DQP so if the function you require is not provided it is possible to write your own and deploy them into DQP, see Chapter 151, DQP for service developers.

DQP supports the aggregation functions shown in Table 147.1, “DQP aggregation functions.”. All the aggregation functions ignore null values except COUNT(*) which counts all the rows regardless of the data values they contain.

DQP supports the scalar functions shown in Table 147.2, “DQP scalar functions.”.

This section shows some example SQL queries that DQP supports. If you are having difficulty forming a query that DQP can execute try adapting those shown here.

Simple SELECT... FROM...WHERE... queries:

SELECT * FROM MyResource_People

SELECT name, age, gender FROM MyResource_People

SELECT name, age FROM MyResource_People WHERE age > 18 AND gender = 'F'

SELECT name, age FROM MyResource_People WHERE name LIKE 'J%'

SELECT DISTINCT age FROM MyResource_People

Implicit join queries:

SELECT * 
FROM MyResource_People p, OtherResource_PostcodesStats pc
WHERE p.postcode = pc.postcode

Explicit join queries:

SELECT * 
FROM 
  MyResource_People p 
  JOIN OtherResource_PostcodesStats pc ON p.postcode = pc.postcode

Union queries:

SELECT intT, dateTimeT
FROM 
((select intT, datetimeT from MySQLResource_mysql_types)  
  UNION ALL 
 (select intT, datetimeT from Resource2_mysql_types) 
) sub

Using aggregate functions:

SELECT COUNT(*), AVG(p.age) 
FROM 
  MyResource_People p 
  JOIN OtherResource_PostcodesStats pc ON p.postcode = pc.postcode

Using scalar functions:

SELECT GetHourOfDay(startTime) FROM MyResource_runRecord

SELECT * 
FROM MyResource_runRecord 
WHERE GetHourOfDay(startTime) > 10

Using grouping:

SELECT C.eid, MAX(A.cruisingrange) 
FROM MySQLResource_certified C, MySQLResource_aircraft A
WHERE C.aid = A.aid AND A.aid>10 
GROUP BY C.eid

SELECT C.eid, MAX(A.cruisingrange)
FROM MySQLResource_certified C, Resource2_aircraft A 
WHERE C.aid = A.aid 
GROUP BY C.eid
HAVING COUNT(C.eid) > 3

SELECT d, h, AVG(i) FROM 
 (SELECT
   GetDate(startTime) AS d, GetHourOfDay(startTime) AS h, numValues AS i 
   FROM MyResource_runRecord) AS sub 
GROUP BY d, h
ORDER BY d, h

Ordering:

SELECT name, age, gender FROM MyResource_People
ORDER BY age

Using subqueries:

SELECT DISTINCT E.ename
FROM MySQLResource_employees E 
WHERE E.salary < 
 ( SELECT MIN (F.price) 
   FROM MySQLResource_flights F
   WHERE F.origin = 'Los Angeles' AND F.destination = 'Honolulu' )

SELECT Temp.name, Temp.AvgSalary
FROM 
( SELECT A.aid, A.aname AS name, AVG (E.salary) AS AvgSalary 
  FROM MySQLResource_aircraft A, Resource2_certified C, Resource3_employees E 
  WHERE A.aid = C.aid AND C.eid = E.eid AND A.cruisingrange > 1000 
  GROUP BY A.aid, A.aname ) AS Temp 
ORDER BY Temp.name

Queryies using various combinations of supported functionality:

SELECT d, h, AVG(i) FROM
(SELECT 
  GetDate(datetimeT) AS d, GetHourOfDay(datetimeT) AS h, intT AS i 
  FROM 
  ((select intT, datetimeT from Resource1_runRecord)
   UNION ALL 
   (select intT, datetimeT from Resource2_runRecord) 
  ) sub1 
  WHERE datetimeT >= '1978-09-29' and datetimeT < '1978-11-30'
) AS sub2 
GROUP BY d, h
ORDER BY d, h