Toad World® Forums

Parser did not identify the text Question?


#1

The Parser did not identify the text as a valid SQL statement. SELECT ‘TCO Active’ AS TYPE, DECODE( ecs.REQUEST_TYPE_ABBR, ‘CONSIT’, ‘Consumer’, ‘LTSPEED’, ‘Consumer’, ‘SOLINT’, ‘Consumer’, ‘ITO’, ‘ITO’, ‘Enterprise’) || ‘-’ || Team$.“Supervisor” AS BU, DECODE( ecs.REQUEST_TYPE_ABBR, ‘CONSIT’, ‘Consumer’, ‘LTSPEED’, ‘Consumer’, ‘SOLINT’, ‘Consumer’, ‘ITO’, ‘ITO’, ‘Enterprise’) AS BU2, --KS_Short$.“Project SUBTYPE”, Team$.“Supervisor”, ecs.REQUEST_TYPE_ABBR, ecs.VPMO_PROJ_ID, ecs.REQUEST_ID, ecs.VPMO_PROJ_NAME, ecs.ASSIGNED_TO_NAME, COUNT(ecs.VPMO_PROJ_ID) AS CountOfVPMO_PROJ_ID, ecs.TCO_START_DT, working_days(er.request_date,SYSDATE)/10 AS of_SLO, working_days(er.request_date,SYSDATE) AS TCO_Duration, case when (working_days(er.request_date,SYSDATE))<=10 then ‘Met’ else ‘Not Met’ end As WorkdaySLA, TO_CHAR(er.request_date, ‘YYYY’) as Year, TO_CHAR(er.request_date, ‘mm’) as Month, TO_CHAR(ecs.tco_completed_dt, ‘iw’)+1 as Week, KS_Short$.“Main Priority”, KS_Short$.“Client Priority”, er.REQUEST_DATE, ecs.TCO_COMPLETED_DT, ers.REQ_STATUS_ID, erl.REQ_STATUS_DESC, --qry_ASAP_ALL_ROLE.CURRENT_STEP, case when “(A,D,C,R) - Status” = ‘A’ then ‘Active’ when “(A,D,C,R) - Status” = ‘D’ then ‘Done’ when “(A,D,C,R) - Status” = ‘C’ then ‘Cancelled’ when “(A,D,C,R) - Status” = ‘H’ then ‘Hold’ when “(A,D,C,R) - Status” = ‘R’ then ‘Refer’ else null end as VPMO_STATUS, ecs.NOTES FROM cscape1.eng_cat_stat_2@test ecs, cscape1.eng_request@test er, cscape1.eng_req_list@test erl, cscape1.eng_req_status@test ers, KS_Short$@excel, Team$@excel where ecs.request_id = er.request_id and (ecs.cat_name = erl.cat_name and ecs.request_id = erl.request_id) and ecs.assigned_to_CUID = Team$.attuid and erl.req_status_desc = ers.req_status_desc and lower(ecs.ASSIGNED_TO_CUID) = lower(Team$.“ATTUID”) and ecs.cat_name = ‘TSA’ and ecs.vpmo_proj_type = ‘B’ and er.request_date >= ‘01-JUN-2009’ group by Team$.“Supervisor”, ecs.REQUEST_TYPE_ABBR, ecs.VPMO_PROJ_ID, ecs.REQUEST_ID, ecs.VPMO_PROJ_NAME, ecs.ASSIGNED_TO_NAME, ecs.TCO_START_DT, working_days(er.request_date,SYSDATE), TO_CHAR(er.request_date, ‘YYYY’), TO_CHAR(er.request_date, ‘mm’), TO_CHAR(ecs.tco_completed_dt, ‘iw’)+1, KS_Short$.“Main Priority”, KS_Short$.“Client Priority”, er.REQUEST_DATE, ecs.TCO_COMPLETED_DT, ers.REQ_STATUS_ID, erl.REQ_STATUS_DESC, ecs.notes, case when “(A,D,C,R) - Status” = ‘A’ then ‘Active’ when “(A,D,C,R) - Status” = ‘D’ then ‘Done’ when “(A,D,C,R) - Status” = ‘C’ then ‘Cancelled’ when “(A,D,C,R) - Status” = ‘H’ then ‘Hold’ when “(A,D,C,R) - Status” = ‘R’ then ‘Refer’ else null end

ORA-00911: invalid character

Invalid SQL statement. Verify that you have selected the right schema. Only one SQL statement can be optimized in this window. To analyze PL/SQL code or multiple SQL statements use the SQL Scanner.

Getting Error running SQL Optimizer but will run in the editor


#2

CASE WHEN “(A,D,C,R) - Status” = ‘A’ THEN

Since these case statements will not have any noticible impact on the SQL performance, you can remove them (one in select and one in group by) while you tune the statement.

I believe the 8.0 version of SQL Optimizer has corrected this issue and someone from development should be able to confirm this shortly.

Regards,
Greg

Hi,

It appears that the parser is having trouble with the quoted columns in two of the case statements:


#3

Hi,

I don’t see any particular syntax in your SQL that the parser not supporting:

  • external tables are supported (I guess some of your tables are external tables)
  • database links are supported
  • quoted identifiers are support
  • decode functions or case statements are supported

I did try your SQL and can confirm that it went through our parser OK at least in the syntax part. I don’t have the tables in my database so I was not able to get a plan and to do a thorough testing.

So I suspect that either there really are some invalid characters (may be invisible control characters) pasted into the editor when you tried optimize last time; or that there was a hidden problem on the parser to get plan for this SQL.

One option you can try is to save your SQL into a text file and then use Scan SQL to scan the file, check if Scan SQL will pick up the SQL. If Scan SQL can find the SQL statement, then your SQL’s syntax are all fine to our parser (Scan SQL can ignore control characters and it only picks up SQL with syntax that the parser supports). Check if there is a plan retrieved if Scan SQL found your SQL statement. You can send from Scan SQL to Optimize SQL to optimize then.

If Scan SQL cannot find the SQL, please contact Quest Support so we can have someone contact you direct to provide more help.

Thanks,
Alex


#4

Hi,

I can now see the possible cause of the problem. Thanks Greg for the tip about the case statement.

The problem could be the connection login not corect or the current schema not set properly. When the connection or schema is not right, the column “(A,D,C,R) - Status” would be treated as PL/SQL variable and this special name triggered another problem resulting in the “Invalid character” error. This error message is kind of misleading. The actual problem is the table not identified.

Please double check the connection and schema to be the correct ones to execute this SQL and try again. Let me know if this doesn’t help.

Thanks,
Alex


#5

Hi Greg,

Very happy with the solution. It worked wonders.

I was getting the same error “The Parser did not identify the text as a valid SQL statement.”.

Had seen several hits on google but none of them worked for me

Thanks & Regards,

Ravi