AnsweredAssumed Answered

The Autosuggest for a dependent feild in not working

Question asked by sravani_nidamanuri on Jul 19, 2016
Latest reply on Jul 21, 2016 by Dave_3.0

The Autosuggest for one of the field in not showing any resultand giving below error in app-ca logs. But it is working while using binocular search  This field is using a dynamic lookup. I'm providing the NSQL of the query as well. This field is actually dependent on other field I mean a dependent lookup is being used.  I think the query is breaking at AND 1=? and 1=1 and 2 = 2  and (  ) ) q) i.e at @filter@ in NSQL.

Can anyone please help me to get the auto suggest work for this field also correct me if my understanding is wrong.

 

NSQL Query:

SELECT CODE, NAME, ID, OPT_IS_ACTIVE

FROM ODF_CA_OPT_CHILD_APP A

WHERE @WHERE:PARAM:USER_DEF:STRING:APP_CODE@ IS NOT NULL

AND EXISTS (SELECT 1 FROM ODF_MULTI_VALUED_LOOKUPS L

   WHERE ATTRIBUTE = 'opt_bill_group' AND A.ID = L.PK_ID AND VALUE = @WHERE:PARAM:USER_DEF:STRING:APP_CODE@)

AND @FILTER@

 

 

ERROR 2016-07-19 13:09:07,072 [http-bio-80-exec-101] osf.LookupSuggestService (clarity:SNIDAMAN:21264359__24636B44-043A-4BA0-9F8D-44A36C4E9192:odata.GetSuggestionsForLookup)

com.niku.union.persistence.nsql.NSQLException: com.niku.union.persistence.PersistenceException:

SQL error code: 936

Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00936: missing expression

 

Executed:

select * from (select row_number() over ( order by name  asc) row_num, count(*) over () num_rows, q.* from ( SELECT CODE, NAME, ID, OPT_IS_ACTIVE

FROM ODF_CA_OPT_CHILD_APP A

WHERE ? IS NOT NULL

AND EXISTS (SELECT 1 FROM ODF_MULTI_VALUED_LOOKUPS L

  WHERE ATTRIBUTE = 'opt_bill_group' AND A.ID = L.PK_ID AND VALUE = ?)

AND 1=? and 1=1 and 2 = 2  and (  ) ) q) q where q.row_num between ? and ? order by q.row_num

Derived from statement:

<?xml version="1.0" encoding="UTF-8"?>

<statement id="null:5105005" sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" inputSource="map" xmlns="http://schemas.niku.com/2002/pmd">

  <sql dbVendor="all">

    <text>SELECT CODE, NAME, ID, OPT_IS_ACTIVE

FROM ODF_CA_OPT_CHILD_APP A

WHERE ? IS NOT NULL

AND EXISTS (SELECT 1 FROM ODF_MULTI_VALUED_LOOKUPS L

  WHERE ATTRIBUTE = 'opt_bill_group' AND A.ID = L.PK_ID AND VALUE = ?)

AND 1=? and ~FILTER_EXPRESSION~ and @ODATA_FILTER@</text>

    <param name="param_app_code" type="string" direction="IN" expressionListDelimiter=","/>

    <param name="param_app_code" type="string" direction="IN" expressionListDelimiter=","/>

    <param name="nsql.execFlag" type="int" direction="IN" expressionListDelimiter=","/>

    <param direction="IN" dynamicReplacement="@ODATA_FILTER@" expressionListDelimiter=","/>

    <param name="code_wildcard" type="searchString" direction="IN" expressionListDelimiter=",">

      <variation text="AND NLS_UPPER(code) LIKE NLS_UPPER(?) ESCAPE '\'"/>

    </param>

Outcomes