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
![Sad :(](./images/smilies/icon_sad.gif)
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.!!