Skip to Content
avatar image
Former Member

Invalid character error ORA-00911 Crystal Reports Command

Hello,

I am trying to create a report in Crystal Reports. I copied the query from DBViz where I was able to run without any issues, and I am getting error ORA-00911. I cannot find where the error is being triggered as other queries with the same parameters are being used without any issues.

Thank you for your help.

Brandon

My query is as follows:

SELECT DISTINCT tso.WORK_DT, e.FIRST_NAME, e.LAST_NAME, pc.SHORT_DESCRIPTION, tso.START_DTTM, tso.END_DTTM, tso.HOURS, e.DISPLAY_EMPLOYEE, a.BASE_PAY_RATE, tse.SYSTEM_TIMESTAMP, ag.DESCRIPTION
FROM TIME_SHEET_OUTPUT tso, EMPLOYEE e, ASGNMT_MASTER am, ASGNMT a, PAY_CODE pc, RULE_SET rs, RULE_SET_DETAIL rsd, TIME_SHEET_EXCEPTION tse, EMPLOYEE_PERIODS ep, EMPLOYEE_PERIOD_VERSIONS epv, ASGNMT_GRP ag, ASGNMT_GRP_DETAIL agd


where e.employee = am.employee
AND am.EMPLOYEE = ep.EMPLOYEE
AND am.ASGNMT = ep.ASGNMT
AND ep.CALC_EMP_PERIOD_VERSION = tso.EMPLOYEE_PERIOD_VERSION
AND ep.CALC_EMP_PERIOD_VERSION = tse.EMPLOYEE_PERIOD_VERSION
AND ep.CALC_EMP_PERIOD_VERSION = epv.EMPLOYEE_PERIOD_VERSION
AND ep.ASGNMT = am.ASGNMT
AND am.ASGNMT = a.ASGNMT
AND e.EMPLOYEE = tso.EMPLOYEE
AND am.ASGNMT = tso.ASGNMT
AND ag.ASGNMT_GRP = agd.ASGNMT_GRP
AND agd.ASGNMT = am.ASGNMT
AND tso.WORK_DT between a.EFF_DT and a.END_EFF_DT
AND a.ASSIGNMENT_STATUS = 'A'
AND tso.PAY_CODE = pc.PAY_CODE
AND tso.WORK_DT between e.EFF_DT and e.END_EFF_DT
AND tso.TRANSACTION_TYPE in (10,20,30)
AND pc.PAY_CODE = rsd.RECORD_KEY
AND rsd.RULE_SET = 'CCW_COUNTS_TO_LUP'
AND rs.rule_set = rsd.RULE_SET
AND rs.SOURCE_TABLE = 'PAY_CODE'
AND {$STD_AS_OF_DATE_SQL} between rs.eff_dt and rs.end_eff_dt
AND tso.WORK_DT between {$STD_START_DATE_SQL} and {$STD_END_DATE_SQL}
AND rs.EFF_DT = rsd.EFF_DT
AND tse.SYSTEM_TIMESTAMP >= {$STD_START_DATE_TIME_SQL} and tse.SYSTEM_TIMESTAMP <= {$STD_END_DATE_TIME_SQL}
AND ({$STD_EMPLOYEE_ID_SQL} is null or {$STD_EMPLOYEE_ID_SQL} = '' or (upper(e.display_employee) like (upper({$STD_EMPLOYEE_ID_SQL}))))
AND ({$STD_EMP_LAST_NAME_SQL} is null or {$STD_EMP_LAST_NAME_SQL} = '' or (e.last_name_upper like (upper({$STD_EMP_LAST_NAME_SQL}))))
AND (' All' in ({$STD_PAY_CODE_SQL}) or ag.ASGNMT_GRP in ({$STD_PAY_CODE_SQL}))
AND ('.All' in ({$STD_ASSIGNMENT_GROUP_LIST_SQL}) or tso.pay_code in ({$STD_ASSIGNMENT_GROUP_LIST_SQL}))
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Apr 09 at 06:51 PM

    I don't directly see where this might be coming from. Have you tried troubleshooting this in SQL*Plus or a tool such as Toad? This is an Oracle error that is passed through Crystal, so you'll have to resolve it on the query side.

    I see that you have a number of parameters in the command. Have you created all of them in the Command Editor? Or did you create them in the Field Explorer? Parameters that have been created in the Field Explorer cannot be used in a command. Instead, they have to be created in the Command Editor because there are some internal properties that Crystal puts on them that aren't in parameters from the Field Editor. You can edit them in the Field Explorer, but you must create them in the Command Editor.

    If you haven't already, see my blog for information about how to work with Commands in Crystal: https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/

    -Dell

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 09 at 08:00 PM

    What is the connection to Oracle? Is it Native or ODBC?

    If it works in ODBC, but fails in Native, then the problem with Date filters.

    Add comment
    10|10000 characters needed characters exceeded