AnsweredAssumed Answered

Resource Browse Usage in NSQL

Question asked by cshah on Dec 2, 2010
Latest reply on Dec 10, 2010 by cshah
Hello,

This is an extension of NSQL Parameters Issue post. I needed to add the USER Value 1 column to the timesheet.

My portlet is going to be an extension/similar to the Timesheet Review Details out of the box portlet.

okay so with that said, I need to add the Resources Parameter to my query. Which I did. And i'm using the Resource browse SCH_BROWSE_RESOURCE lookup for that parameter. see code below. If I filter for a resource, I get an Error 500 internal error. I looked at my appniku log and get the error below.

Don't I need my Parameter to be an integer? The lookup is integer based.

What am i doing wrong? Thank you.
SELECT
@select:dim:user_def:implied:RTE:ROW_NUMBER()OVER(ORDER BY R.ID):UniqueID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RTE:R.FULL_NAME:RESOURCE_FULLNAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RTE:TP.PRSTART:PERIOD_STARTDATE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RTE:TP.PRFINISH-1:PERIOD_FINISHDATE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RTE:L.NAME:TIMESHEET_STATUS@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RTE:I.NAME:INVESTMENT_NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RTE:CD.PRNAME:CHARGE_CODE@,
@SELECT:METRIC:USER_DEF:IMPLIED:SUM(TE.PRACTSUM/3600):RESOURCE_HOURS@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RTE:TE.USER_LOV1:RHC_CODE@
FROM PRTIMESHEET TS
INNER JOIN SRM_RESOURCES R on TS.PRRESOURCEID = R.ID
INNER JOIN PRTIMEPERIOD TP on TS.PRTIMEPERIODID = TP.PRID
INNER JOIN CMN_LOOKUPS_V L on TS.PRSTATUS = L.LOOKUP_ENUM
AND L.LOOKUP_TYPE = 'Timesheet_status'
AND L.LANGUAGE_CODE = 'en'
INNER JOIN PRTIMEENTRY TE on TS.PRID = TE.PRTIMESHEETID
LEFT OUTER JOIN PRASSIGNMENT A on TE.PRASSIGNMENTID = A.PRID
LEFT OUTER JOIN PRTASK T on A.PRTASKID = T.PRID
LEFT OUTER JOIN INV_INVESTMENTS I on T.PRPROJECTID = I.ID
LEFT OUTER JOIN PRCHARGECODE CD on TE.PRCHARGECODEID = CD.PRID
WHERE
@WHERE:PARAM:USER_DEF:INTEGER:TS.PRSTATUS:TIMESHEET_STATUS@
AND @WHERE:PARAM:USER_DEF:INTEGER:TP.PRID:TIME_PERIOD@
AND @WHERE:PARAM:USER_DEF:INTEGER:R.PERSON_TYPE:RESOURCE_TYPE@
AND @WHERE:PARAM:USER_DEF:INTEGER:R.FULL_NAME:RESOURCE_FULLNAME@
AND (@WHERE:PARAM:USER_DEF:INTEGER:OBS@ is null 
OR EXISTS (SELECT 1 FROM prj_obs_associations ASSOC, prj_obs_units_flat FLAT 
WHERE R.id = ASSOC.record_id 
AND ASSOC.table_name = 'SRM_RESOURCES' AND ASSOC.unit_id = FLAT.unit_id AND FLAT.branch_unit_id = @WHERE:PARAM:USER_DEF:INTEGER:OBS@))
AND ((TE.USER_LOV1 IS NULL) OR (@WHERE:PARAM:USER_DEF:STRING:TE.USER_LOV1:RHC_CODE@))
AND @WHERE:SECURITY:RESOURCE:R.ID@
AND @FILTER@
GROUP BY R.ID, TE.USER_LOV1, I.NAME, R.FULL_NAME, 
TP.PRSTART, TP.PRFINISH-1,L.NAME, CD.PRNAME, TS.PRUID
HAVING @HAVING_FILTER@
ERROR 2010-12-02 08:20:26,987 [http-80-Processor3] dal.NSQLClient (cshah:9559171__435fea60:npt.gridFilter) Unable to retrieve NSQL cube. NSQLException was thrown.
com.niku.union.persistence.nsql.NSQLException: com.niku.union.persistence.PersistenceException:
SQL error code: 245
Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]Conversion failed when converting the nvarchar value 'Abate, Frank' to data type int.

Outcomes