AnsweredAssumed Answered

Use of ROWNUM

Question asked by krista.frimel on Mar 10, 2011
Latest reply on Mar 17, 2011 by Dave
I'm trying to write some NSQL to summarize timesheet data by resource, project type, etc. I've tried using ROWNUM to get around using the timeentry ID or some other unique field on the timesheet table because in this case I want to group and summarize fields i.e. I do not care about having a unique ID. When previewing the NSQL below it tells me that ROWNUM needs to be part of the GROUP BY statement. How do I structure the NSQL so that I can group and summarize on resource, project type, etc and not be forced to group on a unique field?


SELECT @select:dim:user_def:implied:timeentry:ROWNUM:UQ_ID@,
@select:dim_prop:user_def:implied:timeentry:timeEntry.resource_name:resource_name@,
@select:dim_prop:user_def:implied:timeentry:timeentry.it_func:func_area@,
@select:dim_prop:user_def:implied:timeentry:timeentry.it_cust:customer@,
@select:dim_prop:user_def:implied:timeentry:timeentry.proj_type:proj_type@,
@select:metric:user_def:implied:SUM(@NVL@(timeEntry.practsum/3600,0)):total_actuals:AGG@
FROM
(SELECT DISTINCT
r.full_name resource_name,
v3.name it_func,
v4.name it_cust,
v5.name proj_type,
te.practsum,
te.prid timeentry_id
FROM inv_investments inv
LEFT OUTER JOIN odf_ca_inv odf on odf.id = inv.id
LEFT OUTER JOIN prtask t ON t.prprojectid = inv.id
LEFT OUTER JOIN prj_obs_associations assoc ON inv.id = assoc.record_id
AND assoc.table_name = 'SRM_PROJECTS'
LEFT OUTER JOIN prj_obs_units_flat flat ON assoc.unit_id = flat.unit_id AND @WHERE:PARAM:USER_DEF:INTEGER:OBS@ = flat.branch_unit_id
LEFT OUTER JOIN cmn_sec_users u ON u.id = inv.manager_id
LEFT OUTER JOIN srm_resources manager ON u.id = manager.user_id LEFT OUTER JOIN prassignment a ON a.prtaskid = t.prid
LEFT OUTER JOIN srm_resources r ON r.id = a.prresourceid
LEFT OUTER JOIN cmn_lookups_v v ON v.id = r.person_type AND v.lookup_type = 'SRM_RESOURCE_TYPE' AND v.language_code= @WHERE:PARAM:LANGUAGE@
LEFT OUTER JOIN prtimeentry te ON te.prassignmentid = a.prid
LEFT OUTER JOIN (SELECT te.prid entry_id, MAX(tn.prid) note_id
FROM prtimeentry te
INNER JOIN prnote tn ON te.prid = tn.prrecordid
AND tn.prtablename = 'PRTimeEntry'
GROUP BY te.prid) note ON note.entry_id= te.prid
LEFT OUTER JOIN prtimesheet ts ON ts.prid = te.prtimesheetid
LEFT OUTER JOIN cmn_lookups_v v2 ON v2.lookup_enum = ts.prstatus AND v2.lookup_type = 'TIMESHEET_STATUS' AND v2.language_code = @WHERE:PARAM:LANGUAGE@
LEFT OUTER JOIN cmn_lookups_v v3 ON v3.lookup_code = odf.e_it_func_ss AND v3.lookup_type = 'IT_FUNCT_SS' AND v3. language_code = @WHERE:PARAM:LANGUAGE@
LEFT OUTER JOIN cmn_lookups_v v4 ON v4.lookup_code = odf.e_it_cust_ss AND v4.lookup_type = 'E_ITCUST_SS' AND v4. language_code = @WHERE:PARAM:LANGUAGE@
LEFT OUTER JOIN cmn_lookups_v v5 ON v5.lookup_code = odf.e_proj_type AND v5.lookup_type = 'E_PROJ_TYPE' AND v5. language_code = @WHERE:PARAM:LANGUAGE@
LEFT OUTER JOIN prtimeperiod tp ON tp.prid = ts.prtimeperiodid
WHERE 1=1
AND te.prid IS NOT NULL
AND te.practsum > 0
AND inv.is_active <> 0
AND ( ( @WHERE:PARAM:USER_DEF:INTEGER:p_includeOther@ = 1 ) OR ( @WHERE:PARAM:USER_DEF:INTEGER:p_includeOther@ <> 1 AND inv.odf_object_code = 'project' ) OR ( @WHERE:PARAM:USER_DEF:INTEGER:p_includeOther@ IS NULL ) )
AND inv.id = CASE WHEN @WHERE:PARAM:XML:INTEGER:/data/id/@value@ IS NULL THEN inv.id ELSE @WHERE:PARAM:XML:INTEGER:/data/id/@value@ END
AND ( @WHERE:PARAM:USER_DEF:INTEGER:OBS@ IS NULL OR @WHERE:PARAM:USER_DEF:INTEGER:OBS@ = flat.branch_unit_id )
) timeEntry
WHERE @FILTER@
GROUP BY
timeEntry.resource_name,
timeEntry.it_func,
timeEntry.it_cust,
timeEntry.proj_type
HAVING @HAVING_FILTER@

Outcomes