BookmarkSubscribeRSS Feed
Fluorite | Level 6
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 
from lib.table
where booleanfield;
  • 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 
from temp_table;
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
The results per run:
Day 1 run 11.285.5851.287.365
Day 1 run 21.285.5211.287.365
Day 2 run 11.285.5861.287.365
Day 2 run 21.285.5211.287.365
Meteorite | Level 14

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.


What to do about it? Sorry, no idea.

Fluorite | Level 6
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. 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 2 in conversation