AnsweredAssumed Answered

NSQL How do I eliminate the number of duplicate rows that an NSQL returns

Question asked by stephen_forney on Nov 17, 2012
Latest reply on Nov 21, 2012 by stephen_forney
NSQL GURUs

How do I eliminate the number of duplicate rows that an NSQL returns?

Created an SQL to identify information on a users Timesheet. The SQL returned the number of rows expected.

I creates an NSQL and there are multiple "duplicate rows" being returned :-(

Any suggestings on how to prevent the multiple rows would be appreciated.

Here is the NSQL ....

select UNIQUE
@SELECT:DIM:USER_DEF:IMPLIED:TIMESHEET:rownum:row_num@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:srmprj.name:srmprj_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:srmprj.unique_name:srmprj_unique_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:srmprj.is_template:srmprj_is_template@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:srmprj.is_active:srmprj_is_active@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:tsk.prname:tsk_prname@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:asgn.prpendactsum:asgn_prpendactsum@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:r.unique_name:r_unique_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:r.full_name:r_full_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:mr.full_name:mr_full_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:mr.unique_name:mr_unique_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:sb.full_name:sb_full_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:sb.unique_name:sb_unique_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:ab.full_name:ab_full_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:ab.unique_name:ab_unique_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:tp.prstart:tp_prstart@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:tp.prfinish:tp_prfinish@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:te.prtimesheetid:te_prtimesheetid@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:ts.pruid:ts_pruid@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:ts.prstatus:ts_prstatus@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:ts.prapprovedby:ts_prapprovedby@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:ts.prmodtime:ts_prmodtime@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:ts.posted_time:ts_posted_time@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:ts.prsubmittedby:ts_prsubmittedby@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:ts.prresourceid:ts_prresourceid@

from
prtimeperiod tp,
prtimesheet ts,
prtimeentry te,
prassignment asgn,
prtask tsk,
prteam tm,
inv_investments inv,
prj_projects prj,
srm_projects srmprj,
srm_resources r,
SRM_RESOURCES mr,
SRM_RESOURCES sb,
SRM_RESOURCES ab


where asgn.prresourceid = tm.prresourceid
and tm.prresourceid = r.id
and asgn.prtaskid = tsk.prid
and tsk.prprojectid = prj.prid
and tsk.prprojectid = srmprj.id
and prj.prid = inv.id
and asgn.prid = te.prassignmentid
and ts.prtimeperiodid = tp.prid
and te.prtimesheetid = ts.prid
and ts.prsubmittedby = sb.user_id
and ts.prapprovedby = ab.user_id
and r.manager_id = mr.user_id

and @filter@

GROUP BY ts.posted_time, srmprj.name, srmprj.unique_name, srmprj.is_template,
srmprj.is_active, tsk.prname, asgn.prpendactsum,
r.full_name, r.unique_name, mr.full_name, mr.unique_name,
sb.full_name, sb.unique_name, ab.full_name, ab.unique_name,
tp.prstart, tp.prfinish, te.prtimesheetid, ts.pruid,
ts.prstatus, ts.prapprovedby, ts.prmodtime,
ts.prsubmittedby, ts.prresourceid, rownun


The attached file contains a screen shot of the portlet returing duplicate rows.

END

Attachments

Outcomes