[
https://issues.apache.org/jira/browse/CALCITE-1332?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15396144#comment-15396144
]
Julian Hyde commented on CALCITE-1332:
--------------------------------------
Putting the database (or any other qualifier like schema) inside the
double-quotes is always wrong. Remember that a table name can legitimately
contain a '.', so we would not be able to tell where the database name ended
and the table name started.
I'm pretty sure that the solution is to use a table alias, i.e.
{code}
select *
from "MYDB"."MYTABLE" AS "T1"
inner join "MYDB"."MYTABLE2" AS "T2" on "T1"."ID" = "T2"."ID"
{code}
Your solution would not work if the same table occurs more than once in the
FROM clause (i.e. a self-join). So let's use aliases all the time. We'll
obviously need to use aliases for other occurrences of the columns, in say the
SELECT and WHERE clauses.
> DB2 requires fully qualified names in join condition
> ----------------------------------------------------
>
> 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
>
> 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)
