Using DATE or DATETIME functions with the COALESCE function might return unexpected results if passed directly to Greenplum


Using the SAS DATE() or DATETIME() functions to obtain date values to be passed to the COALESCE function might return unexpected results if the query is passed down to the Greenplum database for processing.

The Greenplum COALESCE function is not doing an implicit cast and does not throw an error when comparing dates with integer values. The result is that some passed down code comparing a date or datetime with the results of a COALESCE could be executed without errors but return results that are not consistent with the results that would be seen from SAS executed code.

This might be confusing because not all databases allow this comparison and they return an error pushing the query back to SAS to handle. SAS implicitly casts those values to date values in the COALESCE function and brings back all the data from the database to run the query in SAS.

Example:

/* The following can be used to get a date or datetime value */ %let now_dt = %sysfunc(datetime()); %let now_d = %sysfunc(date()); /* This produces the following values which are internal SAS dates */ %put &now_d; 19577 %put &now_dt; 1691494768.04281 /* These values WILL be evaluated without error if they get passed directly to Greenplum which can result in an unexpected value because Greenplum will not treat them as dates but instead integers and run successfully. */ SQL_IP_TRACE: passed down query: select TXT_1."name", TXT_1."timest" from public.JBN_SECURITYCHECK TXT_1, public.JBN_MYLOCAL TXT_2 where TXT_1."timest" >= COALESCE(19577, 1691494768.04281) SQL_IP_TRACE: The SELECT statement was passed to the DBMS. /* Other databases such as Oracle will throw an error on this type of query and force SAS to do the processing */ ACCESS ENGINE: ERROR: ORACLE prepare error: ORA-00932: inconsistent datatypes: expected TIMESTAMP got NUMBER. SQL statement: select TXT_1."NAME", TXT_1."TIMEST" from JBN_SECURITYCHECK TXT_1, JBN_MYLOCAL TXT_2 where TXT_1."TIMEST" >= COALESCE(19577, 1691494768.04281). SQL_IP_TRACE: None of the SQL was directly passed to the DBMS.

A different approach to ensure that the date values are passed down to the database and the processing is done in the database is to use code similar to the following:

data _null_;               
call symput('now_dt',"'"||put(datetime(),datetime22.6)||"'dt");
call symput('now_d',"'"||put(date(),date9.)||"'d");
run; 

/* which would result in values */
%put &now_d;
'07AUG2013'd

%put &now_dt;
'07AUG13:11:58:06.48033'dt

/* And these values can be pushed to Greenplum with results that are the same as in SAS */
SQL_IP_TRACE: passed down query:    select TXT_1."name", TXT_1."timest" from public.JBN_SECURITYCHECK TXT_1, public.JBN_MYLOCAL 
TXT_2 where TXT_1."timest" >= COALESCE( '2013-08-07',  '2013-08-07 11:58:06.480330')
SQL_IP_TRACE: The SELECT statement was passed to the DBMS.

Note: All of the above examples are correct. Every database/engine cannot be expected to work exactly the same as another, and each vendor has their reasons for the behavior of their product.