I query a Greenplum/Postgres database on a Boolean variable with a similar query in two different ways. Functionaly they are the same but I get different results. Can anybody explain this and perhaps help me with a solution?
Functionally I need the two different ways in the application as a before and after check
Unfortunately I cannot access the database directly.
Example 1 directly on the greenplum table
create table countdirect as
"before" as method length=15,
count(*) as countdirect
Example 2 in 2 steps via SAS table
create table temp_table (compress=yes) as select
base.c_custom_documentid as id ,
from lib.table base
where booleanfield ;
create table countindirect as
"after" as method length=15,
count(*) as countindirect
1. ‘booleanfield’ is a Greenplum/Postgres Boolean variable
2. Running in SAS DI and SAS EG both examples deliver matching results
3. Running in LSF the results of Example 1 and 2 are different
4. When running from LSF the results of the first example differ per run
5. The result of Example 2 are constant and seem correct
I assume the by "LSF" you mean either the IBM Load Sharing Facility, or the similar named facility from Platfor Computing (or maybe the two are the same?). I am not sure, but I think that what happens is that when querying in LSF, some records are not read, possibly because they are locked by other users, as the result from the first run is always smaller than the result from the second run.
Thx for your reaction. I think the 2 LSF's are indeed the same. Regarding your suggestion that records may be locked using the first example: this does not happen using the second example; to me this seems to indicate another cause.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.