Remote Submit on DB2 Server "data types of operands not compatible or comparable"

[mMel]

I'm getting the error :

"ERROR: CLI describe error: [IBM][CLI Driver][DB2] SQL0401N The data types of the operands for the operation "" are not compatible or comparable. SQLSTATE=42818"

I suspect that it has to do with my filter that uses a macro, when I read up on this I saw that it's probably because of the version of DB2 we are running. Is anyone able to help me fix this? I've bolded where I believe the problem is.  Ultimately I'm going to pull one month at a time summarize it and stack it all up. I want to be able to just say "do i=1 to 24" and it gives me back 2 years summarized how I want it. If relevant I'll be adding some procs after this one before I close the macro that does the summary/append. 

I have tried to insert some casts with the macro references but it hasn't worked. 

The do loop works on its own and the proc sql works on its own outside of a macro with the dates manually entered 

log error

/*IN PROGRESS FULL MACRO*/
%macro iscool();

%DO i = 1 %TO 2;

data _null_;
%LET INPUTDATE = %SYSFUNC(INTNX(MONTH,'01JAN2017'D,&i,B));
CALL SYMPUT('A', put(&INPUTDATE,mmddyy10.));
CALL SYMPUT('B', put(&INPUTDATE,mmddyy10.)); /*this will turn into EOM eventually*/
%PUT &INPUTDATE &A &i;
RUN;

/*RUNS ON DB2*/
PROC SQL;
CONNECT TO ODBC AS DB2 (dsn=dsn user="&USER" pwd="&PWD");

CREATE TABLE MainData AS 
SELECT * FROM CONNECTION TO DB2


[lots of code that worked before I adjusted the below]


where
C.LIAB_ASGN_IND = 'Y'
/*TESTING DATES*/
AND A.CLM_OPEN_DT between &A and &B
);QUIT;



%END;
%mend;

[jklaverstijn · Accepted solution]

Hi @mMel,

In the clause

AND A.CLM_OPEN_DT between &A and &B

wouldn't you need quotes around these values? Now you have syntax like

AND A.CLM_OPEN_DT between 03/01/2017 and 03/01/2017

which is likely incorrect.

Hope this helps,

-- Jan.

[mMel]

@jklaverstijn

Thank you Jan! I've been staring at this so long I didn't realize.

I added the quotes and got an error about the date format. I looked that up and according to this lesson I have the date's format correct. I've tried both '&A' and date('&A') and still get an error " The syntax of the string representation of a datetime value is incorrect.". These should translate to '02/01/2017' and date('02/01/2017') respectively which appears to be the correct format? 

I'm fiddling with it right now and am thinking about trying to manually create a string using month day year functions. I feel like there's a nicer way to do this though. I'm used to SAS EG doing most of this for me in the background. 

[jklaverstijn]

There are two things at play here. Firstsly, any reference to a macro variable must be between double quotes or no resolution takes place. In your case, using single quotes, DB2 will get the &a as literal text and that won't fly of course. So, use double quotes.

Secondly, the syntax of how you specify a date constant in a DB2 SQL where clause is relevant. DB2 will not accept just any format; it must be specific. Keep in mind that, since you're using Explicit SQL Passthrough, your SQL must comply to DB2 and not SAS standards. And as I remember that would be a syntax like:

date('1999-10-22')

IIRC the hyphens can also be slashes. So yes, you need the single quotes but in a way that doesn't interfere with macro variable resolution. That can be achieved using the %bquote() macro function:

AND A.CLM_OPEN_DT between %bquote(date('&A') and date('&B'))

Hope this helps,

-- Jan.