ERROR: ORACLE prepare error: ORA-00932: inconsistent datatypes

[buechler66]

Hi. I thought SAS dates were numbers, but I'm getting an error regardless. Can anyone help me to resolve the issue?

I'm attempting to filter based on these two conditions:

1. imb_code = imb_code AND

2. spm_calc_batch_date's are within 45 days of one another

So even if imb_codes match, but the spm_calc_batch_dates are greater than 45 days apart from one another then it wouldn't be filtered.

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

145  proc sql;
146  create table QueryData as
147  select subpad('PIECES MISSING IN IV',1,58) as RULE_NM,
148          a.actual_dlvry_date as AD_DT,
149          subpad(a.imb_code,1,31) as IMB_CODE,
150          999.1 as RULE_ORDER,
151          a.spm_calc_batch_date,
152          (a.spm_calc_batch_date - a.spm_calc_batch_date) as spm_calc_batch_date_day_diff
153  from BIDS_ora.bi_spm_piece_recon as a
154  WHERE imb_code not in(select imb_code from IV_ora.bi_spm_piece_recon b where abs(a.spm_calc_batch_date -
154! b.spm_calc_batch_date) <= 45);
ERROR: ORACLE prepare error: ORA-00932: inconsistent datatypes: expected NUMBER got DATE. SQL statement: SELECT  "IMB_CODE",
       "SPM_CALC_BATCH_DATE" FROM IVPRL.BI_SPM_PIECE_RECON  WHERE  ( ABS( ( 1766966400 -"SPM_CALC_BATCH_DATE" )) <=TO_DATE('
       01JAN1960:00:00:45','DDMONYYYY:HH24:MI:SS','NLS_DATE_LANGUAGE=American') ).

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
155  quit;
NOTE: The SAS System stopped processing this step because of errors.

[RW9]

Hi,

Sorry, there are several things wrong with your code.  Lets start with the first thing - you state you are trying to join data, but there is no joining in that code?  Secondly your diff variable will always be zero, you are subtracting a variable from itself?  Thirdly you cannot substract a date from a date, oracle doesn't work that way.  Finally, your variables there are datetime values, even if you did manage to subtract one from the other the difference would not be days?

Let me suggest you do this.  Firstly get all the data you need from OC = select * from OC and create a SAS dataset from it.  Then close OC and forget about that part.  Now you have the data in and can work with it.  If you want us to provide code to do some mapping from that, you would then need to post on here a datastep which will create some test data - which looks like or is your data, and what you want the output to look like, but I suspect a simple intck('day',45) would be a sufficient bit of code to check a window.

[buechler66]

Sorry, I meant Filter Out, not Merge.

[buechler66]

What is OC?

[RW9]

OC= Oracle Clinical - its a variant on Oracle databases used in Pharma.

[buechler66]

Looks like I'm going to use:

WHERE imb_code not in(select imb_code from BIDS_ora.bi_spm_piece_recon b where intck('dtday',a.spm_calc_batch_date,b.spm_calc_batch_date) <= 45)

So the complete query would look like this, do you see any potential problems?

proc sql;
create table QueryData as
select subpad('PIECES MISSING IN BIDS',1,58) as RULE_NM, 
       	a.actual_dlvry_date as AD_DT, 
		subpad(a.imb_code,1,31) as IMB_CODE, 
		999.6 as RULE_ORDER,
		a.spm_calc_batch_date
from IV_ora.bi_spm_piece_recon as a
WHERE imb_code not in(select imb_code from BIDS_ora.bi_spm_piece_recon b where intck('dtday',a.spm_calc_batch_date,b.spm_calc_batch_date) <= 45);
quit;

[RW9]

It might be fine, you will have to try it.  I would note that for normal pass-through (i.e. connect to, select from), you can't use SAS functions as the whole SQL code within the clause is sent to the database which obviously doesn't have the SAS functions.  Your still probably better pulling all data out to a SAS dataset, then processing that.