Job parameters in user defined SQL

[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?

[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

[chulett]

Neither do we... can you post the actual, unedited SQL rather than the redacted version please?

[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.

[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.

[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.!!