ERROR: CLI prepare error: ERROR: Cannot convert 'INT4' to 'BOOL'

[james_whanger]

Hello All,

I am having the following problem to which I found a simple solution, but am interested in understanding why it occurs in the first place.  Here is the issue:

1. Pull in a table from a shared server.

2. Select all variables.

3. Filter on a Numeric variable (0,1)

4. Receive this error: ERROR: CLI prepare error: ERROR:  Cannot convert 'INT4' to 'BOOL'

5. Solution: First run a query in which I  select all variables without filtering. Then run the original query and filter on the numeric variable. No error returned and the new table is properly filtered.

Why does this occur?

Thanks in advance,

James

[Doc_Duke · Accepted solution]

this is probably a server side error.  It could be that SAS is not generating proper code for SQL to transfer.  You need to turn on the SQL options that show the code actually generated and maybe post that.  It would also be helpful to know the SQL engine being used.  You might get the quickest solution by calling tech support.

Doc Muhlbaier

Duke

[james_whanger]

Doc,

You were right. It turns out it has to do with variable encoding on the server side. Pulling the data into project results in an automatric transformation that allows the subsququent query to work. Another solution is to use "NOT IS NULL" rather than "=1" when filtering directly from the server.

Thanks for your help,

James