topic Re: ERROR: The COALESCE function requires its arguments to be of the same data type. in SAS Programming

ERROR: The COALESCE function requires its arguments to be of the same data type.

buechler66 — Mon, 02 May 2016 15:21:36 GMT

Hi. I'm having trouble understanding the issue here. Both A.Actual_Dlvry_Date and B.Actual_Dlvry_Date come from Oracle database tables where they are defined as DATE types. I'm just trying to compare them to see if they mismatch and handle the circumstance where one or both are missing values. I don't see where I'm being inconsistant with the data types.

Any help would be greatly appreciated.

NOTE: Libref IV_ORA was successfully assigned as follows:
 Engine: ORACLE
 Physical Name: IVASPRD
1 libname iv_ora oracle user=&orauser pass=&orapass path="IVASPRD" schema="IVPRL" UPDATE_LOCK_TYPE=row;
NOTE: Libref BIDS_ORA was successfully assigned as follows:
 Engine: ORACLE
 Physical Name: IBSCRPRD
2 libname bids_ora oracle user=exfcread pass=XXXXXXXXXXXXXXXXXX path="IBSCRPRD" schema="IMAPSSCR" UPDATE_LOCK_TYPE=row;

9   + proc sql;
9   +                   create table QueryData as         (       select 'ACTUAL DELIVERY DATE MISMATCH' as RULE_NM,
      b.actual_dlvry_date as AD_DT,                 b.imb_code,                 2 as rule_order                 from
10  + iv_ora.bi_spm_piece_recon a,  bids_ora.bi_spm_piece_recon b                 where a.imb_code = b.imb_code
 and COALESCE(A.ACTUAL_DLVRY_DATE,PUT(today(), yymmddn8.)) <> COALESCE(B.ACTUAL_DLVRY_DATE,PUT(today(), yymmddn8.))         );
NOTE: The "<>" operator is interpreted as "not equals".
ERROR: The COALESCE function requires its arguments to be of the same data type.
ERROR: The COALESCE function requires its arguments to be of the same data type.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
11  +  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds

Re: ERROR: The COALESCE function requires its arguments to be of the same data type.

Astounding — Mon, 02 May 2016 15:26:44 GMT

The two dates may both be date-type, but the PUT function always returns a character string. Luckily, you can change that pretty easily by getting rid of the PUT function and just using the numeric part of that term (in two places):

today()

Re: ERROR: The COALESCE function requires its arguments to be of the same data type.

SteveM_UK — Mon, 02 May 2016 15:32:44 GMT

Hi,

The put(...) function converts your today() date value to a text string, hence it's a different type to the Oracle date value which SAS will provide as a SAS datetime value which is numeric.

Coalesce is a string function so requires both arguments to be text, hence you'll need to convert your Oracle date too.

A factor you might also consider is that Oracle only has a single data type DATE which is actually date-time - a date in Oracle simply has zero hours, minutes and seconds - so you might need to do a bit more transformation before comparing.

- Steve

Re: ERROR: The COALESCE function requires its arguments to be of the same data type.

buechler66 — Mon, 02 May 2016 16:55:13 GMT

Thank you all for the help!