Public Forums

Public write access is disabled. Please  login
open source crm Last post
user

winky286
10 posts
---
Location:
n.a.
16.07.2018 11:25

ERROR: 42601: subquery in FROM must have an alias (PostgreSQL)

Greetings,

After updating to the latest source, some part of functionality (especialy Search) stopped working with PostgreSQL error: ERROR: 42601: subquery in FROM must have an alias.

I was able to trace it down to ExecuteScalar inside of PostgreHelper.cs, line 1085, when trying to access count of elemenets for search result, for example when trying to match System element with Column.

Resulting into SQL:
"SELECT COUNT(*) FROM ( SELECT AD_Element_ID , ColumnName , Name , PrintName , Description , Help , ( CASE IsActive WHEN 'Y' THEN 'True' ELSE 'False' END ) AS IsActive FROM AD_Element WHERE AD_Element_ID=-1 AND AD_Element.AD_Client_ID=0 AND (COALESCE(AD_Element.AD_Org_ID,0) IN(0,0)) )"

which returns the mentioned postgresql error.

When I modify the sql during the runtime (as I am not yet sure where the sql is generated), I get this sql which is working correctly:
"SELECT COUNT(*) FROM ( SELECT AD_Element_ID , ColumnName , Name , PrintName , Description , Help , ( CASE IsActive WHEN 'Y' THEN 'True' ELSE 'False' END ) AS IsActive FROM AD_Element WHERE UPPER(ColumnName) LIKE '%AD_CLIENT_ID%' AND AD_Element.AD_Client_ID=0 AND (COALESCE(AD_Element.AD_Org_ID,0) IN(0,0)) ) as result"

This was not a problem with previous version of source code. I am writing this to notify you about this issue, before I am able to locate the source of generated SQL. (still trying to get the hang of the source :) )

Best Regards,
Winky

Edit: grammar

Edited by winky286 - 16.07.2018 09:26




 
user

winky286
10 posts
---
Location:
n.a.
16.07.2018 15:04

Re: ERROR: 42601: subquery in FROM must have an alias (PostgreSQL)

Greetings,

I was able to locate the source code issue with step-by step watching it into:
VIS\Areas\VIS\Models\InfoGeneralModel.cs
Line 204: .. DB.ExecuteScalar("SELECT COUNT(*) FROM ( "+sql+" )",null,null) ..
and added "as result" behind the sql into:
.. DB.ExecuteScalar("SELECT COUNT(*) FROM ( "+sql+" ) as result",null,null) ..

After that, the search started to work properly.

Best Regards,
Winky




 
user

viennadeveloper
108 posts
---
Location:
n.a.
27.07.2018 13:47

Re: ERROR: 42601: subquery in FROM must have an alias (PostgreSQL)

Thanks for the information.

We will verify and update accordingly.

Regards'
VIENNA Developer