cancel
Showing results for 
Search instead for 
Did you mean: 

Syntax issue in script

former_member203645
Active Participant
0 Kudos

Hi All,

Please correct my syntax below, In debug mode the below is throwing error.

$GV_SQL = 'select distinct FILE_TYPE from ADMIN.SCH_EXE_HISTORY a where a.CYC_DATE = [$GV_DATE_CYC] and status = \'TRANSMITTED\' and file_type in (\'REV\',\'AUM\')';

251664112746272DBS-0704046/19/2015 1:04:21 PMSQL submitted to ODBC data source <apex> resulted in error <[IBM][CLI Driver][DB2/AIX64] SQL0104N  An unexpected token
251664112746272DBS-0704046/19/2015 1:04:21 PM"status = 'TRANSMITTED'" was found following "a.CYC_DATE =  and".  Expected tokens may include:  "<space>".  SQLSTATE=42601
251664112746272DBS-0704046/19/2015 1:04:21 PM>. The SQL submitted is <select distinct FILE_TYPE from ADMIN.SCH_EXE_HISTORY a where a.CYC_DATE =  and status =

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Can't tell exactly, but the error messages suggests [$GV_DATE_CYC] is null and/or you may have to wrap your GV piece in escaped single quotes.

former_member203645
Active Participant
0 Kudos

Thanks

When you say single quotes, Please confirm below if the below is correct.

$GV_SQL = 'select distinct FILE_TYPE from ADMIN.SCH_EXE_HISTORY a where a.CYC_DATE = \'[$GV_DATE_CYC]\' and status = \'TRANSMITTED\' and file_type in (\'REV\',\'AUM\')';

Former Member
0 Kudos

Yes, give that a try

Former Member
0 Kudos

you can try following syntax also

enclosing a global variable in { } will enclose the value in quotes

$GV_SQL = 'select distinct FILE_TYPE from ADMIN.SCH_EXE_HISTORY a where a.CYC_DATE = {$GV_DATE_CYC} and status = \'TRANSMITTED\' and file_type in (\'REV\',\'AUM\')';

former_member203645
Active Participant
0 Kudos

How can we handle if the value is "NULL"

Job is failing for one of this reason

former_member223526
Participant
0 Kudos

Try using NVL() to handle null values from SQL statement.

severin_thelen
Contributor
0 Kudos

I do not understand, why the job should fail because of a NULL.

Normally the logic expressions should give the following results.

"A"="A"     true

"A"=NULL     false

NULL=NULL false

NULL IS NULL true

Like madhusudhan said you could use NVL() to remove NULLs. Another possibility is to include a NULL check in the WHERE CLAUSE.

your statement...

WHERE columnX IS NOT NULL AND

a.CYC_DATE = {$GV_DATE_CYC} and status = \'TRANSMITTED\' and file_type in (\'REV\',\'AUM\')';

columnX is the column with NULL value and could be any column(s).

Regards

Severin

Former Member
0 Kudos

Looking at the errors, problem seems to be a NULL value in the global variables, which than leads to an invalid sql-syntax like, which causes the error in the parser of the target database:


select distinct FILE_TYPE from ADMIN.SCH_EXE_HISTORY a where  a.CYC_DATE = and status = 'TRANSMITTED' and file_type in ('REV','AUM')';


Addionally it might be very interesting what kind of datatype the column a.CYC_DATE and the global variable $GV_DATE_CYC has. If both are a date datetype, I don't think it is a good idea to leave the code workin on the internal date string conversion default, which might be different within data services and the target database. I would suggest to include the conversion to the statement like (Oracle and DataService syntax):


a.CYC_DATE = to_date({to_char(nvl($GV_DATA_CYC,to_Date(\'99991231\',\'yyyymmdd\'),\'yyyymmdd\')},\'yyyymmdd\')


Hope it might be of any help.