[
https://issues.apache.org/jira/browse/CALCITE-1332?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-1332:
---------------------------------
Summary: JDBC adapter for DB2 should always use aliases for tables: x.y.z
AS z (was: DB2 requires fully qualified names in join condition)
> JDBC adapter for DB2 should always use aliases for tables: x.y.z AS z
> ---------------------------------------------------------------------
>
> Key: CALCITE-1332
> URL: https://issues.apache.org/jira/browse/CALCITE-1332
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: MinJi Kim
> Assignee: Julian Hyde
> Fix For: 1.9.0
>
>
> I tried joining tables in DB2 and it looks like DB2 dialect requires the
> fully qualified (with db name, table name, and column name) in the join
> condition. Also, if quoted, the identifiers need to be separated, e.g.
> "MYDB.MYTABLE" will not work, but "MYDB"."MYTABLE" will.
> {code}
> db2 => select * from mydb.mytable inner join mydb.mytable2 on mytable.id =
> mytable2.id
> SQL0206N "MYTABLE.ID" is not valid in the context where it is used.
> db2 => select * from mydb.mytable inner join mydb.mytable2 on
> "mydb.mytable".id = "mydb.mytable2".id
> SQL0206N "mydb.mytable.ID" is not valid in the context where it is used.
> SQLSTATE=42703
> db2 => select name from mydb.mytable inner join mydb.mytable2 on
> mydb.mytable.id = mydb.mytable2.id
> NAME
> --------------------------------------------------
> Steven
> {code}
> {code}
> db2 => select * from "MYDB"."MYTABLE" inner join "MYDB"."MYTABLE2" on
> "MYTABLE"."ID" = "MYTABLE2"."ID"
> SQL0206N "MYTABLE.ID" is not valid in the context where it is used.
> SQLSTATE=42703
> db2 => select * from "MYDB"."MYTABLE" inner join "MYDB"."MYTABLE2" on
> "MYDB.MYTABLE"."ID" = "MYDB.MYTABLE2"."ID"
> SQL0206N "MYDB.MYTABLE.ID" is not valid in the context where it is used.
> SQLSTATE=42703
> db2 => select * from "MYDB"."MYTABLE" inner join "MYDB"."MYTABLE2" on
> "MYDB"."MYTABLE"."ID" = "MYDB"."MYTABLE2"."ID"
> ID NAME FN
>
> LN
> ID
> ----------- --------------------------------------------------
> ----------------------------------------------------------------------------------------------------
>
> ----------------------------------------------------------------------------------------------------
> -----------
> 100 Steven steven
>
> even
> 100
> 1 record(s) selected.
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
