UniQuery error by CASE in ORDER BY on oracle db

[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

[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.

[jrheisler]

Is there a hot release? or when will the release be available?

[jrheisler]

I downloaded Universal Data Access Components Professional 6.4.16 21-Nov-2016

and it is still broken.

[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.

[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.

[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.

[jrheisler]

What is e-support?

[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