英文:
Column ambiguously defined even though I used aliases / odbc ignores aliases
问题
I am using SAP HANA database and this is my query, run from php file.
SELECT
T0."DocNum", T0."createDate",
T0."resolution",
T0."description"
FROM
T.PDVQ T0
LEFT OUTER JOIN
T.POYN T1 ON T1."It" = T0."i"
LEFT OUTER JOIN
T.PDVD T2 ON T2."stat" = T0."status"
LEFT JOIN
T.PIDT T3 ON T0."assignee" = T3.USER
LEFT JOIN
T.PJRQ T4 ON T0."tech" = T4."empID"
ORDER BY
T0."DocNum" DESC
LIMIT 10
I can see the values under column T0."createDate" but not T0."resolution" and T0."description".
Besides I am getting this error:
odbc_result(): SQL error: [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;268 column ambiguously defined: createDate
If I delete T0."createDate"
from the query, I can see the values under T0."resolution" and T0."description" without any error.
What could be the reason for that? I need all in the same query, how do I fix it?
Both T1
and T0
have createDate
. T0."DocNum" didn't make any difference.
ODBC functions I used:
$view = odbc_exec($conn, $query);
while (odbc_fetch_row($view)) {
for ($i = 1; $i <= odbc_num_fields($view); $i++) {
echo odbc_result($view, $i);
}
}
Edit 2: According to my observations, the query is working on SAP itself. It seems like ODBC ignores the aliases. But why can I get T0."createDate"
when I delete T0."resolution"
, T0."description"
from the query?
英文:
I am using SAP HANA database and this is my query, run from php file.
SELECT
T0."DocNum", T0."createDate",
T0."resolution",
T0."description"
FROM
T.PDVQ T0
LEFT OUTER JOIN
T.POYN T1 ON T1."It" = T0."i"
LEFT OUTER JOIN
T.PDVD T2 ON T2."stat" = T0."status"
LEFT JOIN
T.PIDT T3 ON T0."assignee" = T3.USER
LEFT JOIN
T.PJRQ T4 ON T0."tech" = T4."empID"
ORDER BY
T0."DocNum" DESC
LIMIT 10
I can see the values under column T0."createDate" but not T0."resolution" and T0."description".
Besides I am getting this error:
> odbc_result(): SQL error: [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;268 column ambiguously defined: createDate
If I delete T0."createDate"
from query, I can see the values under T0."resolution" and T0."description" without any error.
What could be the reason for that? I need all in the same query, how do i fix it?
Both T1
and T0
has createDate
T0."DocNum" didn't make any difference
odbc functions I used
$view=odbc_exec($conn,$query);
while(odbc_fetch_row($view))
{
for($i=1;$i<=odbc_num_fields($view);$i++)
{
echo odbc_result($view,$i);
}
}
Edit 2: According to my observations query is working on SAP itself. It seems like odbc ignores the aliases. But why I can get T0."createDate"
when I delete T0."resolution"
,
T0."description"
from query?
答案1
得分: 1
根据评论中的讨论,如果以下代码返回错误odbc_result(): SQL error: [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;268 column ambiguously defined: createDate
,这意味着你的HANA软件(无论是HANA Studio还是HANA数据库系统)存在严重问题,你应该重新安装。
SELECT
T0."DocNum", T0."createDate",
T0."resolution",
T0."description"
FROM
T.PDVQ T0
ORDER BY
T0."DocNum" DESC
LIMIT 10
请注意,在上面的查询中,必须在所有地方使用表名或别名(如查询中的T0
),以指示"DocNum",如SAP注释2695943 - SQL failed with error "column ambiguously defined"所述:
"在通过HANA Studio SQL控制台或hdbsql执行时,使用列时需要定义表名,否则将出现"column ambiguously defined"错误消息..."
"如果你的SQL语句包含
column_name
,例如ORDER BY "<column_name>"
,并且执行时出现"column ambiguously defined"错误,你需要修改语句以包括表名,例如ORDER BY <table_name>."<column_name>"
。"
这个查询还存在一个问题,无论你为'MY_TABLE'指定什么,都会返回错误invalid column name error
:
SELECT T.COLUMN_NAME, T.DATA_TYPE_NAME
FROM SYS.TABLE_COLUMNS T
WHERE T.TABLE_NAME = 'MY_TABLE';
(参考:TABLE_COLUMNS System View)
英文:
As per the discussion in the comments, if the following code returns the error odbc_result(): SQL error: [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;268 column ambiguously defined: createDate
, it means that either you have a big trouble with your HANA software (whatever it is HANA Studio or HANA database system) and you should reinstall.
SELECT
T0."DocNum", T0."createDate",
T0."resolution",
T0."description"
FROM
T.PDVQ T0
ORDER BY
T0."DocNum" DESC
LIMIT 10
Pay attention to using the table name or alias (T0
in the query above) at all places where "DocNum" is indicated as per the SAP note 2695943 - SQL failed with error "column ambiguously defined":
> "[When executing via HANA studio SQL console or hdbsql] When columns are used, table name needs to be defined, otherwise, error message "column ambiguously defined" will occur..."
>
> "If your SQL statement contains column_name, for example ORDER BY "<column_name>"
And the execution failed with error "column ambiguously defined", you need to modify the statement to include the table name, for example `ORDER BY <table_name>."<column_name>""
It's also a big problem as this query returns the error invalid column name error
whatever you indicate for 'MY_TABLE':
SELECT T.COLUMN_NAME, T.DATA_TYPE_NAME
FROM SYS.TABLE_COLUMNS T
WHERE T.TABLE_NAME = 'MY_TABLE';
(reference: TABLE_COLUMNS System View)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论