After you apply hot fixes E9BB98 and E9BB99, the following error might occur when you run an SQL procedure query:

This error can occur when the software tries to make correlated references between two different items in a single FROM clause.

This error has been known to occur in the following instances:

• when you have a DISTINCT argument, you reference a calculated variable in your query, and you have a join in the query that is accessing DBMS data via a LIBNAME engine.
• when you select a variable from an in-line view (where the view includes a DISTINCT argument and creates a new variable), and that the in-line view is the second table of a three-table outer join. That in-line view also includes a subsetting WHERE clause and accesses DBMS data via a LIBNAME engine.
• when you create new variables using constants and you create new variables that are used to create other new variables.

There is no workaround for this problem.

Select the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

/* You will need to update the libname statement with your connection 
parameters for userid, password, path and schema */
libname oralib oracle user=xxx pw=xxx path=xxx schema=xxx;

data oralib.sqlplan1;                                          
x=1;                                                        
birth_dt = '01jan2007:00:00:00'dt;    
format birth_dt datetime.;                      
run;   
                                                     
data oralib.sqlplan2;                                          
x=1;                                                        
run;                                                        
                                                            
proc sql;                                                   
create table test as                                        
select distinct                                             
int((datetime()-birth_dt)/(24*60*60*365)) as age,           
calculated age + 1 as nextage                               
from oralib.sqlplan1 a, oralib.sqlplan2 b                         
where a.x=b.x;    
quit; 

/* once again you'll need a libname statement in this example pointing to
a database like Oracle */
libname oralib oracle user=xxx pw=xxx path=xxx schema=xxx;

data dhl_final;
x=1;
y=2;
run;

data oralib.sqlplan_1;
x=1;
run;

data t;
x=1;
run;

proc sql;
 create table test as
 select
   b.new
   from
      dhl_final a
      left outer join
       (select distinct x,'a' as new
        from oralib.sqlplan_1)b
   on a.x = b.x
   left outer join t
   on t.x = b.x
   where y = 2;
quit;

/* once again you'll need a libname statement in this example pointing to a
database like Oracle */
libname oralib oracle user=xxx pw=xxx path=xxx schema=xxx;

data oralib.medical_claim;                                                
emp_id='123';                                                          
pat_birth_dt=dhms('01jan1970'd,0,0,0);                                 
format pat_birht_dt datetime.;                                         
pat_gender='M';                                                        
icd9_cd='11111';                                                       
proc_cd=1;                                                             
start_dt=dhms('01jan2008'd,0,0,0);                                     
format start_dt datetime.;                                             
paid_amt=1;                                                            
proc_type='Cognitive';                                                 
paid_dt=datetime(); 
format paid_dt datetime.;                          
run;                                                                   
                                                                       
data icd9_2;                                                           
icd9_3='111';                                                          
mdc='abc'; 
run;       
                                                     
data oralib.procedure_codes;                                        
procedure_cd=1;                                                  
proc_type='Cognitive';                                           
data employee3;                                                  
unique_id='123 01 Jan 1970M';                                    
start_dt=dhms('01jan2007'd,0,0,0);                               
end_dt = dhms('05jan2008'd,0,0,0);                               
format start_dt end_dt datetime.;                                
run;           
                                                  
proc sql;                                                        
create table mdc as                                              
select a.emp_id, a.unique_id,                                    
case when b.mdc='' then 'Other Conditions/Screenings/Exams'      
else  b.mdc end as mdc, sum(paid_amt) as mdc_cost,               
count(*) as services,                                            
sum(case when (a.proc_type='03' or c.proc_type='Instituti')      
then paid_amt else 0  end) as institutional,                     
sum(case when c.proc_type='Preventiv' then paid_amt else 0       
end) as preventive,                                              
sum(case when (a.proc_type ne '03' and (c.proc_type=             
'Technical' or  c.proc_type='')) then paid_amt else 0 end) as    
technical,                                                       
sum(case when c.proc_type='Cognitive' then paid_amt else 0       
end)  as cognitive                                               
from employee3 z                                                 
left join (select *, emp_id||(put((datepart(pat_birth_dt)),      
worddatx12.))||pat_gender  as unique_id    
      from oralib.medical_claim) a                          
on z.unique_id=a.unique_id                         
left join icd9_2 b                                 
on substr(a.icd9_cd,1,3)=b.icd9_3                  
left join oralib.procedure_codes c                    
on a.proc_cd=c.procedure_cd                        
where a.start_dt between z.start_dt and z.end_dt   
and a.paid_dt