Page 1 of 1
UniQuery error by CASE in ORDER BY on oracle db
Posted: Thu 13 Oct 2016 15:51
by tutko
Hi
I have this simple SQL example:
Code: Select all
select jobid from updatejobqueue
order by
case
when jobid > 1000 then 0
else 1
end
if the property Options.QueryRecCount = True, I receive this error on oracle db:
ORA-00907: missing right parenthesis
Important is the CASE conditional in ORDER BY, this causes always an error on oracle db. On SQLServer works without error.
Can you help me?
Thanks
Willi
Re: UniQuery error by CASE in ORDER BY on oracle db
Posted: Fri 21 Oct 2016 10:23
by MaximG
Thank you for the information. We have reproduced the problem and fixed the bug. The fix will be included in the next UniDAC build.
Re: UniQuery error by CASE in ORDER BY on oracle db
Posted: Thu 01 Dec 2016 23:51
by jrheisler
Is there a hot release? or when will the release be available?
Re: UniQuery error by CASE in ORDER BY on oracle db
Posted: Fri 02 Dec 2016 00:37
by jrheisler
I downloaded Universal Data Access Components Professional 6.4.16 21-Nov-2016
and it is still broken.
Re: UniQuery error by CASE in ORDER BY on oracle db
Posted: Fri 02 Dec 2016 08:58
by MaximG
We checked UniDAC 6.4.16 behavior when using the CASE construction in queries and found no issues. As an example we chose the demo table DEPT :
Code: Select all
var
Query: TUniQuery;
begin
Query := TUniQuery.Create(Nil);
try
Query.Connection := UniConnection;
Query.Options.QueryRecCount := True;
Query.SQL.Text := 'select deptno from dept order by case when deptno > 1000 then 0 else 1 end';
Query.Open;
finally
Query.Free;
end;
end;
Please make sure that the mentioned code fragment works correctly in your test environment. Change it to make the issue occur. Inform us about the obtained results.
Re: UniQuery error by CASE in ORDER BY on oracle db
Posted: Fri 02 Dec 2016 14:33
by jrheisler
After upgrading from 6.3.x to 6.4.x the sql give the error ora-00907: missing right parenthesis
select distinct a.* ,x.obj_id
from chg_master a,object_ids x
where a.chg_num = ''
order by a.ECP_NUM ASC,a.CHG_CAGE ASC ,a.TYPE ASC ,a.REV DESC,a.CORR ASC,a.CHG_TYPE ASC
by changing the sql to this (commenting out the order by), the error goes away:
select distinct a.* ,x.obj_id
from chg_master a,object_ids x
where a.chg_num = ''
--order by a.ECP_NUM ASC,a.CHG_CAGE ASC ,a.TYPE ASC ,a.REV DESC,a.CORR ASC,a.CHG_TYPE ASC
That is the most isolated way I could show it. Unfortunately, there are other parts of our code where many queries are run to and I would likely spend a week going through it all.
Perhaps it has nothing to do with case construction, I don't know, but I do know that a paid upgrade from 6.3.x to 6.4.x resulted in this.
Re: UniQuery error by CASE in ORDER BY on oracle db
Posted: Mon 05 Dec 2016 11:55
by MaximG
We checked the similar query work using UniDAC 6.4.16 and found no issues. To investigate the issue in details, please send us via e-support a script to create the chg_master and object_ids tables and also a sample of populating them with data.
Re: UniQuery error by CASE in ORDER BY on oracle db
Posted: Mon 05 Dec 2016 16:14
by jrheisler
What is e-support?
Re: UniQuery error by CASE in ORDER BY on oracle db
Posted: Tue 06 Dec 2016 09:45
by MaximG
E-support form is available on our site:
https://www.devart.com (меню «Support»\«Request Support»). You can post the script to create the chg_master and object_ids tables on this forum directly