LISTAGG function in IJC

Dear ChemAxon,

 

I can make cview_ok and promote into molecular eneity in IJC.

But I cannot promote cview_fail into molecular entity.

I think the difference is only the use of LISTAGG function.

Am I missing something else?

 

Regards,

Chong Hak Chae,

 

 

 

create or replace view cview_ok

as

select c.cd_id, c.cd_structure, c.code

from compound c

where cd_id < 100

with read only;

 

create or replace view cview_fail

as

select c.cd_id, c.cd_structure

          , ( select LISTAGG( code, ';' ) within GROUP(ORDER BY cd_id)

              from compound

              where cd_id = c.cd_id

              GROUP BY cd_id

          ) code

from compound c

where cd_id < 100

with read only;

 

 

Regards,

 

Chong Hak Chae

LISTAGG is only supported on Oracle 11gR2 and onwards. Are you using a version of Oracle that's compatible with this?

Tim

Dear Tim,

Yes, I am using oracle11gr2 on centos 64-bit.

And I can do substructure search in SQLPLUS  :

select cd_id from cims.compound_view where JC_COMPARE( cd_structure, 'CN1CCN(Cc2ccc(cc2)C(=O)Nc2ccc(C)c(Nc3nccc(n3)-c3cccnc3)c2)CC1', 't:s' ) = 1;

 

But in IJC, I cannot promote this view into molecular table.

 

Regards,

Are you able to promote it, but not as a structure entity, only a standard entity?

Tim

Dear Tim,

Yes, I can promote this cview_fail into standard entity, not into structural entity.

Regards,

Chong Hak Chae,

Dear Chong Hak Chae,

from what you've written here I suspect that the SQL Parser we use for analysing database views fails to recognize the new LISTAGG syntax. Could you please send me the log file from IJC to see whether this is a correct assessment? Please, login to IJC and promote your view, then send the log file from that IJC session to vstejskal _at_ chemaxon.com address.

Here is a page that describes in detail how the get IJC log files.
https://www.chemaxon.com/instantjchem/ijc_latest/docs/admin/debug_info.html#logfile

In the meantime, would it be possible to split your view into two - one would define the aggregation and the other one would join your compound table with the aggregated information from the first view. That way you would only need to promote the view with the compound table and it would not have the LISTAGG function.

I am sorry about the troubles.

Thanks,
-vita

Dear TIm,

I have sent the logfile to you.

Hope this problem solved soon.

Regards,

Dear Chong,



thank you for the log file. I can confirm that the problem is on IJC
side. The SQL parser used in IJC does not understand the new LISTAGG
syntax as I suspected. It's a third party parser that we bundle to IJC
and I can't promise any fixes on their side. I'll file the issue in our
JIRA and we will do our best to fix it.



However, in the meantime, have you considered splitting the CIMS.CVIEW into two views? I think it could work like this:

create view PVIEW as

select corp_id, (LISTAGG( code, ';' ) within GROUP(ORDER BY corp_id)) as plate_position

from compound

GROUP BY corp_id



create view CVIEW

select c.cd_id, c.corp_id, c.code, c.cd_structure, p.plate_position

from compound c left join PVIEW p on c.corp_id = p.corp_id

where corp_id < 100

with read only

I am sorry if there are mistakes in the syntax, I haven't tried that in the database myself, I am just trying to show the idea.

I think that by decomposing your CVIEW into two views you could isolate the LISTAGG function into another view. IJC will then be able to parse your CVIEW and recognize it as a structure-based view.

Thanks,
-vita

Dear Tim,

Thank you for your effort to help me.

As you have suggested, I am trying to split the view into two.

But, the real ERD is much more complex, and will take some time.

Hope this problem fixed in the next release.

Regards,

Chong Hak Chae,