AnsweredAssumed Answered

Report returning incorrect data since upgrade to 13.3

Question asked by TrinaHorgan4206939 on Jul 30, 2014
Latest reply on Jul 31, 2014 by TrinaHorgan4206939

We have two in-house developed reports which are not returing the correct data since we upgraded from 12.1 to 13.3.

The reports included a field which has been removed from the 13.3 database i.e PRJ_BLB_SLICES.ID. The field was part of the select statement but was not used in the join or as part of the criteria. I removed the field from the reports and they run ok but the data is incorrect.

Below is the code before I removed the field which I have highlighted in red.

Does anyone have any idea why removing this field would have such an impact?

thanks,

Trina

 

 

SELECT

  DISTINCT(ALIAS_CMN_SEC_USERS.USER_NAME),

  ALIAS_INV_INVESTMENTS2.NAME,

  ALIAS_PRTASK2.PRNAME,

  ALIAS_SRM_RESOURCES2.FULL_NAME,

  CASE(DATEPART(mm,ALIAS_PRJ_BLB_SLICES2.slice_date))  WHEN '01' THEN ALIAS_PRJ_BLB_SLICES2.Slice END as January,

  CASE(DATEPART(mm,ALIAS_PRJ_BLB_SLICES2.slice_date))  WHEN '02' THEN ALIAS_PRJ_BLB_SLICES2.Slice END as February,

  CASE(DATEPART(mm,ALIAS_PRJ_BLB_SLICES2.slice_date))  WHEN '03' THEN ALIAS_PRJ_BLB_SLICES2.Slice END as March,

  CASE(DATEPART(mm,ALIAS_PRJ_BLB_SLICES2.slice_date))  WHEN '04' THEN ALIAS_PRJ_BLB_SLICES2.Slice END as April,

  CASE(DATEPART(mm,ALIAS_PRJ_BLB_SLICES2.slice_date))  WHEN '05' THEN ALIAS_PRJ_BLB_SLICES2.Slice END as May,

  CASE(DATEPART(mm,ALIAS_PRJ_BLB_SLICES2.slice_date))  WHEN '06' THEN ALIAS_PRJ_BLB_SLICES2.Slice END as June,

  CASE(DATEPART(mm,ALIAS_PRJ_BLB_SLICES2.slice_date))  WHEN '07' THEN ALIAS_PRJ_BLB_SLICES2.Slice END as July,

  CASE(DATEPART(mm,ALIAS_PRJ_BLB_SLICES2.slice_date))  WHEN '08' THEN ALIAS_PRJ_BLB_SLICES2.Slice END as August,

  CASE(DATEPART(mm,ALIAS_PRJ_BLB_SLICES2.slice_date))  WHEN '09' THEN ALIAS_PRJ_BLB_SLICES2.Slice END as September,

  CASE(DATEPART(mm,ALIAS_PRJ_BLB_SLICES2.slice_date))  WHEN '10' THEN ALIAS_PRJ_BLB_SLICES2.Slice END as October,

  CASE(DATEPART(mm,ALIAS_PRJ_BLB_SLICES2.slice_date))  WHEN '11' THEN ALIAS_PRJ_BLB_SLICES2.Slice END as November,

  CASE(DATEPART(mm,ALIAS_PRJ_BLB_SLICES2.slice_date))  WHEN '12' THEN ALIAS_PRJ_BLB_SLICES2.Slice END as December,

ALIAS_PRJ_BLB_SLICES2.ID

FROM

  CMN_SEC_USERS  ALIAS_CMN_SEC_USERS INNER JOIN SRM_RESOURCES  ALIAS_SRM_RESOURCES2 ON (ALIAS_SRM_RESOURCES2.USER_ID=ALIAS_CMN_SEC_USERS.ID)

   INNER JOIN PRJ_OBS_ASSOCIATIONS  ALIAS_PRJ_OBS_ASSOCIATIONS2 ON (ALIAS_PRJ_OBS_ASSOCIATIONS2.RECORD_ID=ALIAS_SRM_RESOURCES2.ID)

   INNER JOIN PRASSIGNMENT  ALIAS_PRASSIGNMENT ON (ALIAS_PRASSIGNMENT.PRRESOURCEID=ALIAS_SRM_RESOURCES2.ID)

   INNER JOIN PRTIMEENTRY  ALIAS_PRTIMEENTRY2 ON (ALIAS_PRASSIGNMENT.PRID=ALIAS_PRTIMEENTRY2.PRASSIGNMENTID)

   INNER JOIN PRTIMESHEET  ALIAS_PRTIMESHEET ON (ALIAS_PRTIMEENTRY2.PRTIMESHEETID=ALIAS_PRTIMESHEET.PRID  AND  ALIAS_PRTIMESHEET.PRRESOURCEID=ALIAS_SRM_RESOURCES2.ID)

   INNER JOIN PRTIMEPERIOD  ALIAS_PRTIMEPERIOD ON (ALIAS_PRTIMEPERIOD.PRID=ALIAS_PRTIMESHEET.PRTIMEPERIODID)

   INNER JOIN PRJ_BLB_SLICES  ALIAS_PRJ_BLB_SLICES2 ON (ALIAS_PRJ_BLB_SLICES2.PRJ_OBJECT_ID=ALIAS_PRTIMEENTRY2.PRID)

   INNER JOIN PRTASK  ALIAS_PRTASK2 ON (ALIAS_PRTASK2.PRID=ALIAS_PRASSIGNMENT.PRTASKID)

   INNER JOIN INV_INVESTMENTS  ALIAS_INV_INVESTMENTS2 ON (ALIAS_INV_INVESTMENTS2.ID=ALIAS_PRTASK2.PRPROJECTID)

 

WHERE

  (

   year(ALIAS_PRJ_BLB_SLICES2.SLICE_DATE)  =  @prompt('Date_param','N','Objects\Slice_Date_Year_Param',Mono,Free,Persistent,,User:0)

   AND

   ALIAS_INV_INVESTMENTS2.ID  =  @prompt('project_param','A','Objects\Project_ID',Mono,Free,Persistent,,User:1)

   AND

   ALIAS_PRJ_BLB_SLICES2.SLICE_REQUEST_ID  =  '5000002'

   AND

   ALIAS_PRTIMESHEET.PRSTATUS  =  '4'

   AND

   ALIAS_PRJ_OBS_ASSOCIATIONS2.TABLE_NAME  =  'SRM_RESOURCES'

   AND

   ALIAS_PRJ_BLB_SLICES2.SLICE_DATE  >=  ALIAS_PRTIMEPERIOD.PRSTART

   AND

   ALIAS_PRJ_BLB_SLICES2.SLICE_DATE  <  ALIAS_PRTIMEPERIOD.PRFINISH

  )

and

ALIAS_INV_INVESTMENTS2.id IN

(select object_instance_id from niku.Z_PRJ_RPT_PROJECTS_V where user_id =

(select user_id from niku.CMN_SEC_USERS where user_name = @variable('BOUSER')))

Outcomes