BookmarkSubscribeRSS Feed
BijZuiderduin
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;
 
Remarks:
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:
 beforeafter
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
2 REPLIES 2
s_lassen
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.

BijZuiderduin
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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 602 views
  • 0 likes
  • 2 in conversation