Error in using ODBC/User-defined SQL (Type 2)

[mkiru23]

Hi
I searched all the threads , I still need some more ideas to fix this one,
HAsh-> Trans --> ODBC

I need to update ( date fields ) in the existing record (SQL Server) search for which ever is latest one and then Insert new one.

Error Log:
DSD.BCIPut call to SQLExecute failed.
SQL statement:IF (SELECT MAX(TERM_DT) FROM Table_A
WHERE (EMPL_D = ?) AND (TERM_DT >= GETDATE()) AND (DIV_D <> ?) AND (DEPT_D <> ?) AND (JOB_D <> ?)) = NOT NULL
BEGIN
UPDATE Table_A
SET LAST_UP_DT = GETDATE() , TERM_DT = ( GETDATE() -1 )
WHERE (EMPL_D = ?) AND ( TERM_DT = (SELECT MAX(TERM_DT) FROM Table_A
WHERE (EMPL_D = ?) AND (TERM_DT >= GETDATE()) AND (DIV_D <> ?) AND (DEPT_D <> ?) AND (JOB_D <> ?))

INSERT INTO TABLE_A (DIV_D, DEPT_D, JOB_D, EMPL_D, EFF_D, TERM_DT, LAST_UPDT_DT)
VALUES (?, ?, ?, ?, ?, ?, ? )
END

SQLSTATE=S1009, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC lib] Invalid argument value

DIV_D = "800"
DEPT_D = "2"
JOB_D = "108"
EMPL_D = "88"
EFF_DT = "2005-04-13"
TERM_DT = "2100-12-31"
LAST_UP_DT = "2005-04-13"

Thanks in advance. for any updates .

[nsm]

Can you post the SQL you are using?

[mkiru23]

Can you post the SQL you are using?

This code works fine in SQL DB, when I made updated with ? for DS, it shows ERROR, any quick changes needed!

SQLSTATE=S1009, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC lib] Invalid argument value

Code:
IF (SELECT ISDATE(MAX(TERM_DT))
FROM TABLE_A
WHERE (EMP_ID = '857') AND (TERM_DATE >= GETDATE()) AND (DIV_ID <> '08') AND (DEP_ID <> '23') AND (JOB_CODE <> '100')) = 1
BEGIN
UPDATE Table_A
SET LAST_UP_DT = GETDATE(), TERM_DT = (GETDATE() - 1)
WHERE (EMP_ID = '857') AND (TERM_DT = '12/31/2199')
INSERT INTO Table_A (DIV_ID, DEP_ID, JOB_CODE, EMP_ID, EMPL_NAME, TERM_DT, LAST_UP_DT)
VALUES ('08', '23', '100', '857', 'Test_name', '2005-04-13', '2005-04-14') END