On Clarity 13.2 I try to display project type name in my query and there is error. Below are error message and my nsql code.
Just wonder that every static/dynamic/parameterized lookup, we always use cmn_lookups_v to mapping with readable value. Please suggestion.
NPT-0103: Error when trying to execute the query. Native message: [CA Clarity][Oracle JDBC Driver]Invalid parameter binding(s). SQL Text: SELECT I.ID ID, I.CODE PRJ_CODE, I.NAME PRJ_NAME, I.IS_ACTIVE IS_ACTIVE, R.FULL_NAME PM, O.OBJ_REQUEST_TYPE PRJ_TYPE, project_type PROJECT_TYPE FROM INV_INVESTMENTS I, INV_PROJECTS P, SRM_RESOURCES R, odf_ca_project O, (SELECT name FROM cmn_lookups_v WHERE lookup_type = 'ODF_OBJECT_PROJECT.OBJ_REQUEST_TYPE' and language_code = ? and lookup_code = o.') project_type WHERE I.IS_ACTIVE = 1 AND I.odf_object_code = 'project' AND I.ID = P.prID AND I.ID = O.ID AND P.IS_TEMPLATE = 0 AND P.IS_PROGRAM = 0 AND I.MANAGER_ID = R.USER_ID AND 1=? and 1=1 .
SELECT
@SELECT:DIM:USER_DEF:IMPLIED:PRJ:I.ID:ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:I.CODE:PRJ_CODE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:I.NAME:PRJ_NAME@,
@SELECT:DIM_PROP:USER_DEF:BOOLEAN:PRJ:I.IS_ACTIVE:IS_ACTIVE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:R.FULL_NAME:PM@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:O.OBJ_REQUEST_TYPE:PRJ_TYPE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:project_type:PROJECT_TYPE@
FROM INV_INVESTMENTS I,
INV_PROJECTS P,
SRM_RESOURCES R,
odf_ca_project O,
(SELECT name FROM cmn_lookups_v
WHERE lookup_type = 'ODF_OBJECT_PROJECT.OBJ_REQUEST_TYPE'
and language_code = @WHERE:PARAM:LANGUAGE@
and lookup_code = o.') project_type
WHERE I.IS_ACTIVE = 1
AND I.odf_object_code = 'project'
AND I.ID = P.prID
AND I.ID = O.ID
AND P.IS_TEMPLATE = 0
AND P.IS_PROGRAM = 0
AND I.MANAGER_ID = R.USER_ID
AND @FILTER@