topic ERROR: CLI cursor fetch error: Numeric value out of range in SAS Programming

ERROR: CLI cursor fetch error: Numeric value out of range

HaveFunWSAS — Tue, 13 Oct 2020 20:59:01 GMT

Dear Community,

I was using SAS EG to pull data from a Netezza database using below code:

proc sql outobs=10;

connect to odbc(dsn=Netezza_PROD uid=xxxxx password = xxxxx); * Retail Deposit;

create table test as

select * from connection to odbc(

select *

from database.dbadmin.table

);

disconnect from odbc;

quit;

I got this error "ERROR: CLI cursor fetch error: Numeric value out of range".

I found the answer in this post is helpful https://communities.sas.com/t5/SAS-Programming/quot-CLI-cursor-extended-fetch-error-Numeric-value-out-of-range/td-p/323830

But in my case, I have lots of variables and it's hard to check which variable is the issue one.. Need your help please.

Thanks.

Re: ERROR: CLI cursor fetch error: Numeric value out of range

SASKiwi — Tue, 13 Oct 2020 21:12:01 GMT

SAS sets the variable value to missing for these errors. Check your output variables for missing values. AFAIK there is no shortcut to identifying the problematic variable.

Re: ERROR: CLI cursor fetch error: Numeric value out of range

HaveFunWSAS — Tue, 13 Oct 2020 21:22:16 GMT

Thanks for your reply.

I didn't see output, sas stopped when meet this error.

-----------------------------------------------------

37 disconnect from odbc;
NOTE: Statement not executed due to NOEXEC option.
38 quit;
NOTE: The SAS System stopped processing this step because of errors.

Re: ERROR: CLI cursor fetch error: Numeric value out of range

SASKiwi — Tue, 13 Oct 2020 22:14:52 GMT

It is likely you are going to have to cast columns with large numerics to character so SAS can handle them.

Run your test query selecting just one column, then add one column at a time to it until you get the error. I know this is tedious, but as I said I don't think there is a shortcut to identify the problematic columns.

Re: ERROR: CLI cursor fetch error: Numeric value out of range

Kurt_Bremser — Wed, 14 Oct 2020 11:57:57 GMT

Netezza should maintain DICTIONARY tables, similar to those provided by SAS. These should give you the information you need to identify the problem variables.

Re: ERROR: CLI cursor fetch error: Numeric value out of range

Kurt_Bremser — Wed, 14 Oct 2020 12:02:06 GMT

According to https://dwgeek.com/netezza-system-tables-views.html/, the view is called _v_sys_columns.