Subscribe Bookmark RSS Feed

Query Builder Returning an 'Invalid Character' Message

d_barnett

Community Trekker

Joined:

Nov 30, 2011

I'm using Query Builder in JMP 12 query a database to retrieve information from.

We already have lots of already written code for doing this using different applications and my hope was that I could just copy & paste the code in JMP as 'custom SQL' and it would run. However it isn't working out as smoothly as I'd hoped and I get a message stating

Error running SQL Query:

[Oracle][ODBC]{Ora]ORA-00911: Invalid Character

[SQLSTATE=HY000]

I've asked a couple of people who know about SQL and they cannot see anything 'wrong' with this code

/* CREATION DATE FROM FORMULATION_SAMPLES TABLE AND MULTIPLE ERROR TRACKING FIGURES */

select

sample_value_summary.sample_identifier,order_in_workflow AS "ORDER",creation_date,

max (case when param_def_lov_id = 19 then value_string end ) "MATERIAL",

max (case when param_def_lov_id = 18 then value_string end ) "BATCH",

max (case when param_def_lov_id = 5 then value end ) "TARGET ",

max (case when param_def_lov_id = 24 then value end ) "ACTUAL ",

(max (case when param_def_lov_id = 24 then value end ) - max (case when param_def_lov_id = 5 then value end ))*1000 as "ERROR [mg]",

ABS((max (case when param_def_lov_id = 24 then value end ) - max (case when param_def_lov_id = 5 then value end ))*1000) as "ERROR [mg]",

ROUND(((max (case when param_def_lov_id = 24 then value end ) - max (case when param_def_lov_id = 5 then value end ))/max (case when param_def_lov_id = 5 then value end ))*100,3) as "ERROR [%]",

ABS(ROUND(((max (case when param_def_lov_id = 24 then value end ) - max (case when param_def_lov_id = 5 then value end ))/max (case when param_def_lov_id = 5 then value end ))*100,3)) as "ABSOLUTE ERROR [%]",

max (case when param_def_lov_id = 34  then value/86400 + to_timestamp ('01011970','dd-mm-yyyy') end ) "START TIME",

max (case when param_def_lov_id = 35 then value/86400 + to_timestamp ('01011970','dd-mm-yyyy') end ) "STOP TIME",

(max (case when param_def_lov_id = 35  then value end ) - max (case when param_def_lov_id = 34  then value end )) as "PROCESS TIME"

from SCO_HTFS.sample_value_summary,SCO_HTFS.formulation_samples

where sample_value_summary.sample_identifier = formulation_samples.sample_identifier and

param_def_lov_id in (5,18,19,24,34,35)and bms_module_id in (11,12)

and exp_plan_sample_id in (select exp_plan_sample_id from sco_htfs.exp_plan_samples where exp_plan_id in (

select exp_plan_id from sco_htfs.experiment_plans where experiment_id = /* ENTER EXPERIMENT ID HERE*/ 4742))

/*DELETE THIS LINE IF NO TIME CONDITION REQUIRED */ and creation_date between to_timestamp (/*ENTER START DATE HERE*/'01012014','dd-mm-yyyy') and current_timestamp

group by sample_value_summary.sample_identifier,order_in_workflow,creation_date

order by sample_value_summary.sample_identifier,order_in_workflow;

This code runs in other applications successfully so it is written 'correctly' but not compatible, could anyone suggest what might be stopping this running correctly? I have a few of these which give the same message so it implies to me that there is some subtle difference in the way these are written and the way JMP needs them.

Regards


David

2 REPLIES
pmroz

Super User

Joined:

Jun 23, 2011

Remove the comments.

Eric_Hill

Staff

Joined:

Oct 1, 2013

I personally doubt that the comments are the problem -- I added C-style comments to a working Oracle query and the query still worked.

When I take d_barnett's SQL and try to submit it to my Oracle, the error I get is:

[Oracle][ODBC][Ora]ORA-00933: SQL command not properly ended [SQLSTATE=HY000]

rather than Invalid Character.

This is on Windows, not Mac, right?

One thing to watch out for is styled quotes (single or double) sneaking in to SQL you are pasting around.  Paste your SQL into Notepad first, then copy/paste it from there into JMP.

I would suggest trying to work back to a simpler version of that SQL that does work and start adding in parts until it fails again.  It sure would be awesome if Oracle could just tell us what the invalid character is.

Please post back here if you find the invalid character.  Maybe JMP needs to scan custom SQL for styled quotes and convert them to normal quotes before submitting the SQL (if indeed your issue turns out to be styled quotes).

Thanks,

Eric