Page 1 of 1

Job parameters in user defined SQL

Posted: Tue Nov 15, 2016 3:24 pm
by chandu123
I am trying to run user defined SQL in ODBC stage. The query is as below:

Code: Select all

Insert into schema.Table_name 
(Column1, 
Column2) 
(Select Clm1, 
Case when #Param2#=0 Then 'Clm2' 
Else 'Test' end as Colm2
From Table2)
Note: Param2 is a job parameter

But I am seeing below error
ODBC function "SQLExecute" reported: SQLSTATE = HY000: Native Error Code = 911: Msg = [Oracle][ODBC][Ora]ORA-00911: invalid character

Any suggestion how to correct this error?

Posted: Tue Nov 15, 2016 3:34 pm
by chandu123
FYI, I am seeing the generated SQL in the log and it is correct. as below:

Code: Select all

Insert into schema.Table_name (
Column1,
Column2)
(select clm1,
Case when 0=0 Then 'Clm2' 
Else 'Test' End Colm2
From Table2)
But I don't know which is the invalid character :(

Posted: Tue Nov 15, 2016 4:21 pm
by chulett
Neither do we... can you post the actual, unedited SQL rather than the redacted version please?

Posted: Wed Nov 16, 2016 11:29 am
by UCDI
generally you want to run the sql and develop it with access to the database in your favorite sql editor/tool, then do a simple search and replace of the variables with datastage parameters and copy that into the stage.

I have never had the sql not work in datastage when it worked in the sql editor first. This is also a critical debugging technique, to see exactly what you are extracting esp if bad data is crashing your job.

Posted: Wed Nov 16, 2016 11:49 am
by chulett
In my experience, the general answer when Oracle throws that "invalid character" in a tool like this is because you've included a trailing semi-colon. Don't see that here, hence the desire to see the full / actual SQL.

Posted: Wed Nov 16, 2016 4:34 pm
by chandu123
Than you all for the responses.

I figured out that I was using Write mode as 'Insert' instead of User-defined SQL. Somehow Case statement was causing the issue in 'Insert' write mode. I chenaged it to User-defined SQL and it worked fine. Thanks.!!