Hi!
Given the incompleteness of the description below, I am rather looking for your ideas of what might be wrong, rather than a full solution.
Situation:
-- X64_SRV12 WIN 6.2.9200 Server
-- Oracle database
-- SAS 9.4.3.0, SAS EG 7.12
We have a variable called "nosig" that is an integer between 0 and 400.
I have a very simple SAS PROC SQL query that extracts the "nosig" from the Oracle database.
In the subsequent data step, I filter on nosig outside the above range, i.e. below 0 or above 400.
This dataset should always be empty, as no such nosigs are expected and we know no such data are in the Oracle database.
I run this SAME IDENTICAL query 10 times.
Usually, I get very strange results in at least one of these 10 runs (see attached pictures).
Sometimes, the ten runs produce correct results (i.e. no strange "nosig" values).
Sometimes, up to 7 runs ouf of 10 produce incorrect results.
But I repeat, the query is always the same, the underlying data have not changed between the iterations, and the database actually does not include any of the shown incorrect nosig values.
The wrong nosig numbers are usually either extremely small or extremely high.
There are often hundreds of thousands of rows with incorrect nosigs, but only a handful of unique incorrect values (see pictures).
When the same query is run directly in the SQL, without passing through SAS proc SQL, no such error ever occurres.
Given this information, does this suggest it is a problem of Oracle client, or of the SAS driver ODBC?
How can I possibly find out what the problem is?
Any ideas why the results vary between runs?
Many thanks in advance.