AnsweredAssumed Answered

CA PPM query open timesheets

Question asked by JMSnider on Jun 30, 2016
Latest reply on Jun 30, 2016 by JMSnider

I need to create a report of all open timesheets for the current time period. I understand that timesheets don't get a record until a task is added to them, so I need to include even those that don't have records yet. I found a query in the community yesterday. I am trying it. I had to make some tweaks to it. When I run the query in the derived table tab of Advanced Reporting, I get an error:

com.jaspersoft.commons.semantic.metaapi.MetaDataException: Cannot execute JDBC Query.

java.sql.SQLSyntaxErrorException: ORA-00936: missing expression

I can't see anything missing. There are FROMs for all the SELECTS. If someone could look at it, or maybe share a query that they know works, I'd really appreciate it.

Thanks!!!  - Joanne

 

 

SELECT t.subUsername AS myUsername,

t.FULL_NAME AS myName,

t.Period_start AS Period_start,

t.Timesheet_status AS Timesheet_status

 

FROM

(select srm_resources.full_name AS FULL_NAME,

  srm_resources.unique_name AS subUsername,

  ISNULL(timesheets.Period_Start,'') AS Period_start,

  ISNULL(timesheets.Timesheet_status,'') AS Timesheet_status

  from

  prj_resources,

  srm_resources left join

   (Select prresourceid,

     convert(varchar, prtimeperiod.prstart,105) AS Period_Start, 

     prtimesheet.prstatus,

     CASE WHEN prtimesheet.prstatus = 0 THEN 'Open saved'

     WHEN prtimesheet.prstatus = 1 THEN 'Submitted'

     WHEN prtimesheet.prstatus = 2 THEN 'Submitted'

     WHEN prtimesheet.prstatus = 3 THEN 'Approved'

     WHEN prtimesheet.prstatus = 4 THEN 'Posted'

     WHEN prtimesheet.prstatus = 5 THEN 'Adjusted'

     ELSE 'Does not exist'

     END AS Timesheet_status

    from prtimeperiod, PRTIMESHEET

    WHERE prtimeperiod.prid = prtimesheet.prtimeperiodid

   ) 

   AS timesheets on srm_resources.id = timesheets.prresourceid

  where prj_resources.prid=srm_resources.id

   and srm_resources.is_active= 1

   and prj_resources.prisopen=1

   and prj_resources.prisrole=0

   and srm_resources.person_type > 0

) AS t

Outcomes