列有歧义定义,尽管我使用了别名/ODBC忽略别名。

huangapple go评论60阅读模式
英文:

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.&quot;DocNum&quot;, T0.&quot;createDate&quot;,
    T0.&quot;resolution&quot;,
    T0.&quot;description&quot;	
FROM  
    T.PDVQ T0 
LEFT OUTER JOIN 
    T.POYN T1 ON T1.&quot;It&quot; = T0.&quot;i&quot;  
LEFT OUTER JOIN 
    T.PDVD T2 ON T2.&quot;stat&quot; = T0.&quot;status&quot; 
LEFT JOIN 
    T.PIDT T3 ON T0.&quot;assignee&quot; = T3.USER
LEFT JOIN 
    T.PJRQ T4 ON T0.&quot;tech&quot; = T4.&quot;empID&quot;
ORDER BY 
    T0.&quot;DocNum&quot; 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.&quot;createDate&quot; 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&lt;=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.&quot;createDate&quot; when I delete T0.&quot;resolution&quot;,
T0.&quot;description&quot; 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.&quot;DocNum&quot;, T0.&quot;createDate&quot;,
    T0.&quot;resolution&quot;,
    T0.&quot;description&quot;    
FROM  
    T.PDVQ T0 
ORDER BY 
    T0.&quot;DocNum&quot; 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 &quot;&lt;column_name&gt;&quot; 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 = &#39;MY_TABLE&#39;;

(reference: TABLE_COLUMNS System View)

huangapple
  • 本文由 发表于 2023年3月31日 18:49:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/75897674.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定