SQL Grammar for JD Edwards EnterpriseOne Connection Modes
The JDBC drivers implement JD Edwards EnterpriseOne connection modes using JDBj, which is a Java data access API. The JDBC drivers parse SQL statements and transforms them into JDBj operations.
In general, the JDBC driver using the EnterpriseOne connection accepts only SELECT statements. All other operations, such as INSERT, UPDATE, DELETE, ALTER, DROP, and CREATE statements are not supported. If the driver cannot parse the SQL statement, then the JDBC driver throws an SQLException with a message that explains the parsing error.
The following table describes the SQL grammar that the parser recognizes. In this table, SQL keywords are in bold font (SELECT.) SQL keywords are not case sensitive. Rule names are listed in italics (where-clause.) Terminal symbols are noted. Optional clauses are listed in square brackets (,[ order-by-clause ].) Clauses that may repeat 0 or more times are listed in parenthesis followed by an asterisk (( , database-object-with-alias )*.) A vertical bar indicates that one of a set of options is valid (* | fields).
Rule |
Definition |
---|---|
select-statement |
SELECT fields-clause FROM database-objects [ where-clause ] [group-by-clause ] [ order-by-clause ] |
subquery-clause |
SELECT fields-clause FROM database-object-with-alias [ where-clause ] [ group-by-clause ] |
database-objects |
database-object-with-alias ( , database-object-with-alias )* |
database-object-with-alias |
database-object [ ID ] Note: ID is a terminal symbol. |
database-object |
ID Note: ID is a terminal symbol. Database object names are table and business view names. Do not qualify these with an owner or schema. The JDBC driver uses its own data source resolution mechanisms (such as an ERP system's OCM) to resolve database object name qualifiers. However, if you require a schema to satisfy some third-party software requirements, you qualify the table or business view names with JDE as the schema. JD Edwards EnterpriseOne does not have a schema or catalog concept and this qualification is ignored at runtime. |
fields-clause |
*|fields| field-function-expressions |
fields |
field ( , field )* |field AS alias ( , field AS alias)* |
field |
database-object [ . ID [ . field-instance ]] Note: ID is a terminal symbol. Field names are in the format database-object.field.instance, where database-object and instance are optional. Field names match data dictionary names rather than physical column names. For example, use AN8 (the data dictionary name for address book number) rather than ABAN8 (the physical F0101 column name). Instance is an integer that refers to the instance of a particular field when used in a self-join. |
field-instance |
INTEGER_LITERAL Note: INTEGER_LITERAL is a terminal symbol. |
field-function-expressions |
field-function-expression ( , field-function-expression )* | field-function-expression AS alias( , field-function-expression AS alias )* |
field-function-expression |
type1-field-function-expression | type2-field-function-expression | type3-field-function-expression |
type1-field-function-expression |
AVG|COUNT|SUM[DISTINCT] ( field ) Note: See the examples provided in the following table. |
type2-field-function-expression |
MIN|MAX ( field ) Note: See the examples provided in the following table. |
type3-field-function-expression |
COUNT ( * ) |
field-reference |
field |
literals |
literal ( , literals )* |
literal |
STRING_LITERAL | INTEGER_LITERAL | FLOATING_POINT_LITERAL | NULL | ? Note: STRING_LITERAL, INTEGER_LITERAL, and FLOATING_POINT_LITERAL are terminal symbols. |
where-clause |
WHERE or-expression |
group-by-clause |
GROUP BY group-by-fields |
order-by-clause |
ORDER BY order-by-fields |
order-by-fields |
order-by-field-and-direction( , order-by-field-and-direction )* |
order-by-field-and-direction |
field-reference [ order-by-direction ] |
order-by-direction |
ASC | DESC |
or-expression |
and-expression ( OR and-expression )* |
and-expression |
not-expression ( AND not-expression )* |
not-expression |
[ NOT ] sub-expression |
sub-expression |
exists-clause | relational-expression | (or-expression) |
exists-clause |
EXISTS ( subquery-clause ) |
relational-expression |
field field-expression | in-expression | between-expression | like-expression | is-null-expression Note: Inconsistent results might occur if you use a field that requires decimal scrubbing within a relational expression. |
field-expression |
comparison-op ( ( [ ALL | ANY ] ( subquery-clause ) ) | element ) |
in-expression |
[ NOT ] IN(subquery-clause | elements ) |
between-expression |
[ NOT ] BETWEENelement AND element |
like-expression |
LIKE element |
is-null-expression |
IS [ NOT ] NULL |
elements |
element ( , element )* |
element |
field-reference | literal |
comparison-op |
= | != | <> | > | >= | < | <= | *= | =* | *=* |
The following are some examples of SQL statements that are allowed:
Object Type |
Statement |
---|---|
Table |
select AN8 from F0101 or select AN8 AS AddressBookNumber from F0101 |
Select All Table |
select * from F0101 |
Table Join |
select avg(t1.an8), min(t1.an8),max(t1.an8), count (t1.Name), sum(t1.an8), avg(distinct t1.an8), count (distinct t1.name),sum(distinct t1.an8),t1.an8 from F0101 to, F0010 t1 where t0.an8=t1.an8 group by t1.an8 |
Table Union |
select F4211.KCOO, F4211.DOCO, F4211.DCTO , MAX (F4211.LNID), COUNT(F4211.DOCO), MIN(F4211.LNID), min (F4211.AN8) from F4211 group by F4211.LNID,F4211.DOCO, F4211.DCTO,F4211.KCOO UNION select F42119.KCOO, F42119.DOCO, F42119.DCTO , MAX(F42119.LNID), COUNT (F42119.DOCO), MIN(F42119.LNID), min(F42119.AN8) from F42119 group by F42119.LNID, F42119.DOCO, F42119.DCTO,F42119.KCOO order by F4211.DOCO DESC, F4211.KCOO asc |
Single Table Business View |
select AN8 from V0101C |
Multiple Table Business View |
select F0101.AN8, F0116.AN8 from V0101JE |
Union Business View |
select max(F4211.KCOO), max(F4211.KCOO) from V4211A |