problem to calculate the tanimoto coefficient with a query

dear support,

I have this sql query : 

 select distinct stru_id,CD_MOLWEIGHT, jc_tanimoto(cd_structure, :requ_smile) as tanimoto from sub_structure ,sub_substance a where a.subs_stru_id=stru_id and a.subs_etat_suppression in (0,1) and jc_compare(cd_structure, :requ_smile, 't:t simThreshold:0.95') = 1 order by CD_MOLWEIGHT asc,stru_id

I would like to calculate the tanimoto coefficient for each query result by similarity.

This query returns error when i add : jc_tanimoto(cd_structure, :requ_smile) as tanimoto

Thanks for your help

Kiet

Dear Kiet,

Please, could you post

(1) the error you get

(2) the output of the following SQL:

select jchem_core_pkg.getenvironment from dual;

Thanks

Peter

select jchem_core_pkg.getenvironment from dual;

GETENVIRONMENT

--------------------------------------------------------------------------------

Oracle environment:

Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

JChem owner: SYSADMIN

JChem Server environment:

Java VM vendor: Oracle Corporation

GETENVIRONMENT

--------------------------------------------------------------------------------

Java version: 1.7.0_19

Java VM version: 23.7-b01

JChem version: 6.0.1

JChem Index version: 6000000

JDBC driver version: 11.1.0.7.0-Production

Don't you get any error message? Just no rows are returned?

What happens if you execute the SQL from SQL*Plus or SQL Developer -- after replacing :requ_smile with the actual query structure?

select distinct stru_id,CD_MOLWEIGHT from sub_structure ,sub_substance a where a.subs_stru_id=stru_id and a.subs_etat_suppression in (0,1) and jc_compare(cd_structure, 'C1=CC2=C(C=C1)C=CC=C2', 't:t simThreshold:0.95') = 1 order by CD_MOLWEIGHT asc,stru_id;

STRU_ID              CD_MOLWEIGHT

-------------------- ------------

AB-00320330              128.1705

jc_tanimoto(cd_structure, utl_raw.cast_to_raw('C1=CC2=C(C=C1)C=CC=C2'))

?

thanks perfect!