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
(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
srm_resources left join
convert(varchar, prtimeperiod.prstart,105) AS Period_Start,
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
and srm_resources.is_active= 1
and srm_resources.person_type > 0
) AS t