Home » Tech Reference » SQL Support » SQL Grammar

SQL Support

MetaMatrix SQL Grammar

The following is specific to MetaMatrix release 5.5 or later.

TERMINALS

TOKEN: /* Data types */
{
    <STRING: "string">
|    <BOOLEAN: "boolean">
|    <BYTE: "byte">
|    <SHORT: "short">
|    <CHAR: "char">
|    <INTEGER: "integer">
|    <LONG: "long">
|    <BIGINTEGER: "biginteger">
|    <FLOAT: "float">
|    <DOUBLE: "double">
|    <BIGDECIMAL: "bigdecimal">
|    <DATE: "date">
|    <TIME: "time">
|    <TIMESTAMP: "timestamp">
|    <OBJECT: "object">
|    <BLOB: "blob">
|    <CLOB: "clob">
|    <XML: "xml">
}


TOKEN: /* Functions with special syntax */
{
    <CAST: "cast">
|    <CONVERT: "convert">
|   <TIMESTAMPADD: "timestampadd">
|   <TIMESTAMPDIFF: "timestampdiff">

/* aggregate */
|    <COUNT: "count">
|    <SUM: "sum">
|    <AVG: "avg">
|    <MIN: "min">
|    <MAX: "max">
}

TOKEN : /* Reserved words */
{
    <ALL: "all">
|   <AND: "and">
|   <ANY: "any">
|   <AS: "as">
|   <ASC: "asc">
|   <BEGIN: "begin">
|   <BETWEEN: "between">
|   <BREAK: "break">
|   <BY: "by">
|   <CASE: "case">
|   <CONTINUE: "continue">
|   <CREATE: "create">
|   <CRITERIA: "criteria">
|   <CROSS: "cross">
|   <DEBUG: "debug">
|   <DECLARE: "declare">
|   <DELETE: "delete">
|   <DESC: "desc">
|   <DISTINCT: "distinct">
|   <DROP: "drop">
|   <ELSE: "else">
|   <END: "end">
|   <ERROR: "error">
|   <ESCAPE: "escape">
|   <EXCEPT: "except">
|   <EXEC: "exec">
|   <EXECUTE: "execute">
|   <EXISTS: "exists">
|   <FALSE: "false">
|   <FN: "fn">
|   <FOR: "for">
|   <FROM: "from">
|   <FULL: "full">
|   <GROUP: "group">
|   <HAS: "has">
|   <HAVING: "having">
|   <IF: "if">
|   <IN: "in">
|   <INNER: "inner">
|   <INSERT: "insert">
|   <INTERSECT: "intersect">
|   <INTO: "into">
|   <IS: "is">
|   <JOIN: "join">
|   <LEFT: "left">
|   <LIKE: "like">
|   <LIMIT: "limit">
|   <LOCAL: "local">
|   <LOOP: "loop">
|   <MAKEDEP: "makedep">
|   <MAKENOTDEP: "makenotdep">
|    <NOCACHE: "nocache">
|   <NOT: "not">
|   <NULL: "null">
|   <ON: "on">
|   <OJ: "oj">
|   <OPTION: "option">
|   <OR: "or">
|   <ORDER: "order">
|   <OUTER: "outer">
|   <PLANONLY: "planonly">
|   <PROCEDURE: "procedure">
|   <RIGHT: "right">
|   <SELECT: "select">
|   <SET: "set">
|   <SHOWPLAN: "showplan">
|   <SOME: "some">
|   <TABLE: "table">
|   <TEMPORARY: "temporary">
|   <THEN: "then">
|   <TRANSLATE: "translate">
|   <TRUE: "true">
|   <UNION: "union">
|   <UNKNOWN: "unknown">
|   <UPDATE: "update">
|   <USING: "using">
|   <VALUES: "values">
|   <VIRTUAL: "virtual">
|   <WHEN: "when">
|   <WHERE: "where">
|   <WITH: "with">
|   <WHILE: "while">
}

TOKEN : /* Special function words */
{
    <SQL_TSI_FRAC_SECOND: "SQL_TSI_FRAC_SECOND">
|    <SQL_TSI_SECOND: "SQL_TSI_SECOND">
|    <SQL_TSI_MINUTE: "SQL_TSI_MINUTE">
|    <SQL_TSI_HOUR: "SQL_TSI_HOUR">
|    <SQL_TSI_DAY: "SQL_TSI_DAY">
|    <SQL_TSI_WEEK: "SQL_TSI_WEEK">
|    <SQL_TSI_MONTH: "SQL_TSI_MONTH">
|    <SQL_TSI_QUARTER: "SQL_TSI_QUARTER">
|    <SQL_TSI_YEAR: "SQL_TSI_YEAR">
}

TOKEN : /* User variables and literals */
{
    < ALL_IN_GROUP: (<GROUP_PART> | <MMUUID_PART>) <PERIOD> <STAR> >

|   < VARIABLE: <ID> | <MMUUID> >

|   < #ID: <GROUP_PART>                                       // Group name
           ( (<PERIOD>|<SLASH>) (<QUOTED_ID> | <MMUUID_PART>) )? >        // Element name extension

|    < #ELEMENT: <GROUP_PART> (<PERIOD>|<SLASH>) <QUOTED_ID> >
|    < #GROUP_PART: ("#")? (<QUOTED_ID> (<PERIOD>|<SLASH>))? <QUOTED_ID> >
|    < #QUOTED_ID: <DOTTED_ID> | ("\"" <DOTTED_ID> "\"") >          
|    < #DOTTED_ID: <ID_PART> ((<PERIOD>|<SLASH>) <ID_PART>)* >          
|   < #ID_PART: ("@")? <LETTER> (<ID_CHAR>)* >              
|   < #ID_CHAR: (<LETTER> | "_" | <DIGIT>) >   

|    < #MMUUID: <MMUUID_PART> (<PERIOD> <MMUUID_PART>)?    >      // Modeler-only mmuuid formats
|   < #MMUUID_PART: "mmuuid:" (<MMUUID_CHAR>)* >             
|   < #MMUUID_CHAR: ["a"-"f"] | ["0"-"9"] | "-">         

|     < DATETYPE: "{" "d" >
|     < TIMETYPE: "{" "t" >
|     < TIMESTAMPTYPE: "{" "ts" >
|     < BOOLEANTYPE: "{" "b" >

|    < INTEGERVAL: (<MINUS>)?(<DIGIT>)+ >
|   < FLOATVAL: (<MINUS>)? (<DIGIT>)* <PERIOD> (<DIGIT>)+
                ( ["e", "E"] (["+","-"])? (<DIGIT>)+ )? >
|   < STRINGVAL: (("N")? (<STRINGA> | <STRINGB>)) >
|    < #STRINGA: "'" (~["'"])* ( "''" (~["'"])* )* "'" >
|   < #STRINGB: "\"" (~["\""])* ( "\"\"" (~["\""])* )* "\"" >
|    < #LETTER: (["a"-"z","A"-"Z"] | ["\u0153"-"\ufffd"]) >
|    < #DIGIT: ["0"-"9"] >
|   < #COLON: ":">

}

TOKEN : /* Punctuation */
{
    <COMMA: ",">
|    <PERIOD: ".">
|    <LPAREN: "(">
|    <RPAREN: ")">
|   <LBRACE: "{">
|   <RBRACE: "}">
|    <EQ: "=">
|    <NE: "<>">
|    <NE2: "!=">
|    <LT: "<">
|    <LE: "<=">
|    <GT: ">">
|    <GE: ">=">
|    <STAR: "*">
|    <SLASH: "/">
|    <PLUS: "+">
|    <MINUS: "-">
|   <QMARK: "?">
|   <DOLLAR: "$">
|   <SEMICOLON: ";">
|    <CONCAT_OP: "||">
}
 

 

NON-TERMINALS

command ::= ( ( setQuery | storedProcedure | insert | update | delete ) | ( createTempTable | createUpdateProcedure | dropTable ) ) ( <SEMICOLON> )? <EOF>
dropTable ::= <DROP> <TABLE> <VARIABLE>
createTempTable ::= <CREATE> <LOCAL> <TEMPORARY> <TABLE> <VARIABLE> <LPAREN> createElementsWithTypes <RPAREN>
errorStatement ::= <ERROR> expression
statement ::= ( ifStatement | loopStatement | whileStatement | delimitedStatement )
delimitedStatement ::= ( sqlStatement | errorStatement | assignStatement | declareStatement | continueStatement | breakStatement ) <SEMICOLON>
block ::= ( statement | ( <BEGIN> ( statement )* <END> ) )
breakStatement ::= <BREAK>
continueStatement ::= <CONTINUE>
whileStatement ::= <WHILE> <LPAREN> criteria <RPAREN> block
loopStatement ::= <LOOP> <ON> <LPAREN> setQuery <RPAREN> <AS> <VARIABLE> block
ifStatement ::= <IF> <LPAREN> criteria <RPAREN> block ( <ELSE> block )?
criteriaSelector ::= ( ( <EQ> | <NE> | <NE2> | <LE> | <GE> | <LT> | <GT> | <IN> | <LIKE> | ( <IS> <NULL> ) | <BETWEEN> ) )? <CRITERIA> ( <ON> <LPAREN> <VARIABLE> ( <COMMA> <VARIABLE> )* <RPAREN> )?
hasCriteria ::= <HAS> criteriaSelector
declareStatement ::= <DECLARE> dataType <VARIABLE> ( <EQ> assignStatementOperand )?
assignStatement ::= <VARIABLE> <EQ> assignStatementOperand
assignStatementOperand ::= ( ( insert ) | update | delete | storedProcedure | ( expression ) | setQuery )
sqlStatement ::= ( setQuery | ( dynamicCommand ) | storedProcedure | insert | update | delete | createTempTable | dropTable )
translateCriteria ::= <TRANSLATE> criteriaSelector ( <WITH> <LPAREN> <VARIABLE> <EQ> expression ( <COMMA> <VARIABLE> <EQ> expression )* <RPAREN> )?
createUpdateProcedure ::= <CREATE> ( <VIRTUAL> )? ( <UPDATE> )? <PROCEDURE> block
dynamicCommand ::= <EXECUTE> <STRING> expression ( <AS> createElementsWithTypes ( <INTO> <VARIABLE> )? )? ( <USING> <VARIABLE> <EQ> ( <COMMA> <VARIABLE> <EQ> )* )? ( <UPDATE> ( ( <INTEGERVAL> ) | ( <STAR> ) ) )?
createElementsWithTypes ::= <VARIABLE> dataType ( <COMMA> <VARIABLE> dataType )*
storedProcedure ::= ( ( <EXEC> | <EXECUTE> ) <VARIABLE> <LPAREN> ( executeNamedParams | executeUnnamedParams ) <RPAREN> ) ( option )?
executeUnnamedParams ::= ( expression ( <COMMA> expression )* )?
executeNamedParams ::= ( paramName <EQ> expression ( <COMMA> paramName <EQ> expression )* )
paramName ::= <VARIABLE>
insert ::= <INSERT> <INTO> <VARIABLE> <LPAREN> <VARIABLE> ( <COMMA> <VARIABLE> )* <RPAREN> <VALUES> rowValues ( option )?
rowValues ::= <LPAREN> expression ( <COMMA> expression )* <RPAREN>
update ::= <UPDATE> <VARIABLE> <SET> <VARIABLE> <EQ> expression ( <COMMA> <VARIABLE> <EQ> expression )* ( where )? ( option )?
delete ::= <DELETE> <FROM> <VARIABLE> ( where )? ( option )?
setQuery ::= basicQuery ( ( <UNION> ( <ALL> )? basicQuery )+ )? ( orderby )? ( limit )? ( option )?
basicQuery ::= ( query | ( <LPAREN> setQuery <RPAREN> ) )
query ::= select ( into )? ( from ( where )? ( groupBy )? ( having )? )?
into ::= <INTO> ( <VARIABLE> )
select ::= <SELECT> ( <ALL> | ( <DISTINCT> ) )? ( <STAR> | ( selectSymbol ( <COMMA> selectSymbol )* ) )
selectSymbol ::= ( ( <ALL_IN_GROUP> ) | ( expression ) ( ( <AS> )? ( <VARIABLE> | <STRINGVAL> ) )? )
aggregateSymbol ::= ( ( <COUNT> <LPAREN> <STAR> <RPAREN> ) | ( ( <COUNT> | <SUM> | <AVG> | <MIN> | <MAX> ) <LPAREN> ( <DISTINCT> )? expression <RPAREN> ) )
from ::= <FROM> ( fromClause ( <COMMA> fromClause )* )
fromClause ::= ( ( <LBRACE> <OJ> fromClauseUnescaped <RBRACE> ) | fromClauseUnescaped )
fromClauseUnescaped ::= basicFromClause ( ( ( ( <CROSS> <JOIN> | <UNION> <JOIN> ) fromClauseUnescaped ) | ( ( <RIGHT> ( <OUTER> )? | <LEFT> ( <OUTER> )? | <FULL> ( <OUTER> )? | <INNER> )? <JOIN> fromClauseUnescaped <ON> criteria ) ) )*
basicFromClause ::= ( unaryFromClause | subqueryFromClause | ( <LPAREN> fromClause <RPAREN> ) ) ( ( <MAKEDEP> ) | ( <MAKENOTDEP> ) )?
subqueryFromClause ::= <LPAREN> ( setQuery | storedProcedure ) <RPAREN> ( <AS> )? <VARIABLE>
unaryFromClause ::= ( <VARIABLE> ( ( <AS> )? <VARIABLE> )? )
where ::= <WHERE> criteria
criteria ::= compoundCritOr
compoundCritOr ::= compoundCritAnd ( <OR> compoundCritAnd )*
compoundCritAnd ::= notCrit ( <AND> notCrit )*
notCrit ::= ( <NOT> )? primary
primary ::= ( predicate | ( <LPAREN> criteria <RPAREN> ) )
predicate ::= ( subqueryCompareCriteria | compareCrit | matchCrit | betweenCrit | setCrit | existsCriteria | hasCriteria | translateCriteria | isNullCrit )
compareCrit ::= expression ( <EQ> | <NE> | <NE2> | <LT> | <LE> | <GT> | <GE> ) expression
subquery ::= <LPAREN> ( setQuery | storedProcedure ) <RPAREN>
subqueryCompareCriteria ::= expression ( <EQ> | <NE> | <NE2> | <LT> | <LE> | <GT> | <GE> ) ( <ANY> | <SOME> | <ALL> ) subquery
matchCrit ::= ( expression ( <NOT> )? <LIKE> expression ( ( <ESCAPE> <STRINGVAL> ) | ( <LBRACE> <ESCAPE> <STRINGVAL> <RBRACE> ) )? )
betweenCrit ::= expression ( <NOT> )? <BETWEEN> expression <AND> expression
isNullCrit ::= expression <IS> ( <NOT> )? <NULL>
setCrit ::= expression ( <NOT> )? <IN> ( ( subquery ) | ( <LPAREN> expression ( <COMMA> expression )* <RPAREN> ) )
existsCriteria ::= <EXISTS> subquery
groupBy ::= <GROUP> <BY> ( groupByItem ( <COMMA> groupByItem )* )
groupByItem ::= expression
having ::= <HAVING> criteria
orderby ::= <ORDER> <BY> ( <VARIABLE> | <STRINGVAL> | <INTEGERVAL> ) ( <ASC> | <DESC> )? ( <COMMA> ( <VARIABLE> | <STRINGVAL> | <INTEGERVAL> ) ( <ASC> | <DESC> )? )*
limit ::= <LIMIT> ( <INTEGERVAL> | <QMARK> ) ( <COMMA> ( <INTEGERVAL> | <QMARK> ) )?
option ::= <OPTION> ( <SHOWPLAN> | <PLANONLY> | <DEBUG> | <MAKEDEP> <VARIABLE> ( <COMMA> <VARIABLE> )* | <MAKENOTDEP> <VARIABLE> ( <COMMA> <VARIABLE> )* | <NOCACHE> ( <VARIABLE> ( <COMMA> <VARIABLE> )* )? )*
expression ::= concatExpression
concatExpression ::= ( plusExpression ( <CONCAT_OP> plusExpression )* )
plusExpression ::= ( timesExpression ( plusOperator timesExpression )* )
plusOperator ::= ( <PLUS> | <MINUS> )
timesExpression ::= ( basicExpression ( timesOperator basicExpression )* )
timesOperator ::= ( <STAR> | <SLASH> )
basicExpression ::= ( <QMARK> | literal | ( <LBRACE> <FN> function <RBRACE> ) | ( aggregateSymbol ) | ( function ) | ( <VARIABLE> ) | ( <LPAREN> expression <RPAREN> ) | subquery | caseExpression | searchedCaseExpression )
caseExpression ::= <CASE> expression ( <WHEN> expression <THEN> expression )+ ( <ELSE> expression )? <END>
searchedCaseExpression ::= <CASE> ( <WHEN> criteria <THEN> expression )+ ( <ELSE> expression )? <END>
function ::= ( ( <CONVERT> <LPAREN> expression <COMMA> dataType <RPAREN> ) | ( <CAST> <LPAREN> expression <AS> dataType <RPAREN> ) | ( ( <TIMESTAMPADD> | <TIMESTAMPDIFF> ) <LPAREN> intervalType <COMMA> expression <COMMA> expression <RPAREN> ) | ( ( <LEFT> | <RIGHT> | <CHAR> ) <LPAREN> ( expression ( <COMMA> expression )* )? <RPAREN> ) | ( ( <INSERT> ) <LPAREN> ( expression ( <COMMA> expression )* )? <RPAREN> ) | ( ( <TRANSLATE> ) <LPAREN> ( expression ( <COMMA> expression )* )? <RPAREN> ) | ( <VARIABLE> <LPAREN> ( expression ( <COMMA> expression )* )? <RPAREN> ) )
dataType ::= ( <STRING> | <BOOLEAN> | <BYTE> | <SHORT> | <CHAR> | <INTEGER> | <LONG> | <BIGINTEGER> | <FLOAT> | <DOUBLE> | <BIGDECIMAL> | <DATE> | <TIME> | <TIMESTAMP> | <OBJECT> | <BLOB> | <CLOB> | <XML> )
intervalType ::= ( <SQL_TSI_FRAC_SECOND> | <SQL_TSI_SECOND> | <SQL_TSI_MINUTE> | <SQL_TSI_HOUR> | <SQL_TSI_DAY> | <SQL_TSI_WEEK> | <SQL_TSI_MONTH> | <SQL_TSI_QUARTER> | <SQL_TSI_YEAR> )
literal ::= ( <STRINGVAL> | <INTEGERVAL> | <FLOATVAL> | <FALSE> | <TRUE> | <NULL> | ( <BOOLEANTYPE> <STRINGVAL> <RBRACE> ) | ( <TIMESTAMPTYPE> <STRINGVAL> <RBRACE> ) | ( <DATETYPE> <STRINGVAL> <RBRACE> ) | ( <TIMETYPE> <STRINGVAL> <RBRACE> ) )