Syntax issue in script

[former_member203645]

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\')';

25166	4112746272	DBS-070404	6/19/2015 1:04:21 PM	SQL submitted to ODBC data source <apex> resulted in error <[IBM][CLI Driver][DB2/AIX64] SQL0104N  An unexpected token
25166	4112746272	DBS-070404	6/19/2015 1:04:21 PM	"status = 'TRANSMITTED'" was found following "a.CYC_DATE =  and".  Expected tokens may include:  "<space>".  SQLSTATE=42601
25166	4112746272	DBS-070404	6/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 =

[Former Member]

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]

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]

Yes, give that a try

[Former Member]

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]

How can we handle if the value is "NULL"

Job is failing for one of this reason

[former_member223526]

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

[severin_thelen]

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]

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.