AnsweredAssumed Answered

NPT-0103: Error when trying to execute the query.

Question asked by dave_somick on Oct 19, 2013
Latest reply on Oct 21, 2013 by dave_somick
I was searching the boards beause I thought the error was related to embedding user defined params into a sub query, but I saw Dave's post in another thread, and that is not the case.
so here is my problem.
I am getting an error on preview or save of my query:NPT-0103: Error when trying to execute the query. Native message: [CA Clarity][Oracle JDBC Driver]
[Oracle]ORA-00936: missing expression which is think is related to Clarity not translating my user variables which I am finding very bizzare:

I would have thought this would be a simple portlet it's a 1 table query :*)

Original NSQL snippet:
[...] other code removed for conciseness
@select:metric:user_def:implied:(select count(a.PST_TYP_DTL)
from odf_ca_et_pms_track a
where a.PST_LOB =@WHERE:PARAM:USER_DEF:STRING:USRLOB@
AND pst_typ ='pm_research' and a.pst_typ_dtl = pms.PST_TYP_DTL
and ((a.PST_USR_REQ_DT >= @WHERE:PARAM:USER_DEF:DATE:STARTDT@) or (a.PST_USR_REQ_DT <= @WHERE:PARAM:USER_DEF:DATE:ENDDT@))):Research:AGG@,

@select:metric:user_def:implied:count(pms.PST_TYP_DTL):Total:AGG@

from odf_ca_et_pms_track pms
where pms.PST_LOB = @WHERE:PARAM:USER_DEF:STRING:USRLOB@
and ((a.PST_USR_REQ_DT >= @WHERE:PARAM:USER_DEF:DATE:STARTDT@) or (a.PST_USR_REQ_DT <= @WHERE:PARAM:USER_DEF:DATE:ENDDT@))
AND @FILTER@
group by pms.PST_TYP_DTL,pms.PST_LOB

Preview sql snippet :
[...] other code removed for conciseness
(select count(a.PST_TYP_DTL)
from odf_ca_et_pms_track a
where a.PST_LOB = @WHERE PARAM
AND pst_typ ='pm_research' and a.pst_typ_dtl = pms.PST_TYP_DTL
and ((a.PST_USR_REQ_DT >= :param_startdt) or (a.PST_USR_REQ_DT <= :param_enddt))):Research:AGG@,
count(pms.PST_TYP_DTL) Total

from odf_ca_et_pms_track pms
where pms.PST_LOB = :param_usrlob
and ((a.PST_USR_REQ_DT >= :param_startdt) or (a.PST_USR_REQ_DT <= :param_enddt))
AND 1=1
group by pms.PST_TYP_DTL,pms.PST_LOB


and the SQL in the Error msg:
[...] other code removed for conciseness
(select count(a.PST_TYP_DTL) from odf_ca_et_pms_track a where pst_typ ='pm_research' and a.pst_typ_dtl = pms.PST_TYP_DTL and a.PST_LOB = @WHERE PARAM and ((a.PST_USR_REQ_DT >= ?) or (a.PST_USR_REQ_DT <= ?))):Research@AGG@,
count(pms.PST_TYP_DTL) TotalAGG@ from odf_ca_et_pms_track pms where pms.PST_LOB = ? and ((a.PST_USR_REQ_DT >= ?) or (a.PST_USR_REQ_DT <= ?)) AND 1=? and 1=1 group by pms.PST_TYP_DTL,pms.PST_LOB .

so it's interpreting my USRLOB param correctly in the outer where clause but not the inner? Yet its working fine for the enddt and startdt params...
Please help!!!

Outcomes