ERROR: ORACLE prepare error: ORA-00933: SQL command not properly ended.

[bhca60]

proc sql;
     connect to oracle as test (user=%SUPERQ(dra_u) password="&dra_p." path=huvhg); 

        create table now1(drop=crdttm) as
           select *, 
		  crdttm as act_dttm format=datetime24.3
			from connection to test
                (select distinct a.act_dttm,
                                 a.id,
                                 a.acttpcd,
                                 a.actrscd,
                                 a.actotcom_cd,                            
                                 a.crusid as emp_id,
                                 a.cmtrid,
                                 a.act_dttm as crdttm,
         			 c.mktcd,
                                 c.srcstcd,
                                 d.pur_id,
                                 d.bol_cd,
                                 d.clmplcd,
			         d.ent,
                             case when d.plancd in ('MMM','RRR','DDDD') then 'RNM'
                                  else 'INE'
                             end as Business,
                                 d.plancd
          
                    from dra.rsh_dra_snart_act as a

                       left join dra.rsh_dra_case as c
                          on a.id = c.id

                       left join dra.rsh_dra_nonmdm_mbr as d
                          on c.id = d.id 
				
                          where trunc(a.act_dttm) between to_date(&start.,'yyyy-mm-dd') and to_date(&end.,'yyyy-mm-dd')
                            and a.crusid in (&role1.)
                            and (c.srcstcd not in ('5','6') or c.srcstcd is null)
                            and (d.pur_id not in ('0001234','0005678') or d.pur_id is null)
        
                          order by a.id, a.act_dttm);
                         
     disconnect from test;
quit;

ERROR: ORACLE prepare error: ORA-00933: SQL command not properly ended.

What could be causing this error? I've never seen this one before. I am in SAS Studio connecting to Oracle. I dont know if it doesnt like the order by or what.

[Patrick]

You're script is missing a comma.

[bhca60]

I accidentally removed that comma; it's not missing a comma in the original run and it still gives that error.

[SASKiwi]

There is an easy way to find errors in SQL since SQL is not very good in locating where errors are and that is to simplify your query to the point that it works, then step by step add more lines back in until you get the error. This would be be a good starting point:

proc sql;
     connect to oracle as test (user=%SUPERQ(dra_u) password="&dra_p." path=huvhg); 

        create table now1(drop=crdttm) as
           select *, 
		  crdttm as act_dttm format=datetime24.3
			from connection to test
                (select distinct a.act_dttm,
                                 a.id,
                                 a.acttpcd,
                                 a.actrscd,
                                 a.actotcom_cd,                            
                                 a.crusid as emp_id,
                                 a.cmtrid,
                                 a.act_dttm as crdttm

                    from dra.rsh_dra_snart_act as a

   where trunc(a.act_dttm) between to_date(&start.,'yyyy-mm-dd') and to_date(&end.,'yyyy-mm-dd')
                            and a.crusid in (&role1.)
                          order by a.id, a.act_dttm);
                         
     disconnect from test;
quit;

If that works, then add new code, line by line. If it doesn't then your macro variable values could be the problem. Replace these with hard-coded values to see if that fixes the error.

[bhca60]

The roleid is working fine - it looks like it is recognized in the program because a list of ids show in the log. It may have something do with the order by and it being a subquery? 

[Patrick]

---

@bhca60 wrote:

The roleid is working fine - it looks like it is recognized in the program because a list of ids show in the log. It may have something do with the order by and it being a subquery? 

---

Then may-be share the log with us - run with options mprint set so can see to what macro variables like &start resolve.

It's unlikely the order by clause because for Oracle that's not within a sub-query. But agree that it would be cleaner to have it on the SAS side.

[bhca60]

proc sql;
     connect to oracle as test (user=%SUPERQ(dra_u) password="&dra_p." path=huvhg); 

        create table now1(drop=crdttm) as
           select *, 
		  crdttm as act_dttm format=datetime24.3
			from connection to test
                (select distinct a.act_dttm,
                                 a.id,
                                 a.acttpcd,
                                 a.actrscd,
                                 a.actotcom_cd,                            
                                 a.crusid as emp_id,
                                 a.cmtrid,
                                 a.act_dttm as crdttm,
         			 c.mktcd,
                                 c.srcstcd,
                                 d.pur_id,
                                 d.bol_cd,
                                 d.clmplcd,
			         d.ent,
                             case when d.plancd in ('MMM','RRR','DDDD') then 'RNM'
                                  else 'INE'
                             end as Business,
                                 d.plancd
          
                    from dra.rsh_dra_snart_act as a

                       left join dra.rsh_dra_case as c
                          on a.id = c.id

                       left join dra.rsh_dra_nonmdm_mbr as d
                          on c.id = d.id 
				
                          where trunc(a.act_dttm) between to_date(&start.,'yyyy-mm-dd') and to_date(&end.,'yyyy-mm-dd')
                            and a.crusid in (&role1.)
                            and (c.srcstcd not in ('5','6') or c.srcstcd is null)
                            and (d.pur_id not in ('0001234','0005678') or d.pur_id is null)
        
                          order by a.id, a.act_dttm);
                         
     disconnect from test;
quit;

ERROR: ORACLE prepare error: ORA-00933: SQL command not properly ended.

What could be wrong ? Is it because it's a subquery using an order by? 

[Kurt_Bremser]

Please do not double-post.

What is contained in your macro variables? Run

%put &=start.;
%put &=end.;
%put &=role1.;

immediately before invoking PROC SQL, and post the log.

[bhca60]

1     %studio_hide_wrapper;
MLOGIC(STUDIO_HIDE_WRAPPER):  Beginning execution.
NOTE: The macro STUDIO_HIDE_WRAPPER is executing from memory.
      48 instructions 1088 bytes.
MLOGIC(STUDIO_HIDE_WRAPPER):  %GLOBAL  _STUDIONOTES
MLOGIC(STUDIO_HIDE_WRAPPER):  %GLOBAL  _STUDIOSOURCE
MLOGIC(STUDIO_HIDE_WRAPPER):  %GLOBAL  _STUDIOSTIMER
MLOGIC(STUDIO_HIDE_WRAPPER):  %IF condition &_studionotes = is TRUE
MLOGIC(STUDIO_HIDE_WRAPPER):  %LET (variable name is _STUDIONOTES)
MPRINT(STUDIO_HIDE_WRAPPER):   options nonotes;
MLOGIC(STUDIO_HIDE_WRAPPER):  %IF condition &_studiosource = is TRUE
MLOGIC(STUDIO_HIDE_WRAPPER):  %LET (variable name is _STUDIOSOURCE)
MPRINT(STUDIO_HIDE_WRAPPER):   options nosource;
MLOGIC(STUDIO_HIDE_WRAPPER):  %IF condition &_studiostimer = is TRUE
MLOGIC(STUDIO_HIDE_WRAPPER):  %LET (variable name is _STUDIOSTIMER)
MPRINT(STUDIO_HIDE_WRAPPER):   options nostimer;
MLOGIC(STUDIO_HIDE_WRAPPER):  Ending execution.
MLOGIC(STUDIO_RESTORE_WRAPPER):  Beginning execution.
MLOGIC(STUDIO_RESTORE_WRAPPER):  %GLOBAL  _STUDIONOTES
MLOGIC(STUDIO_RESTORE_WRAPPER):  %GLOBAL  _STUDIOSOURCE
MLOGIC(STUDIO_RESTORE_WRAPPER):  %GLOBAL  _STUDIOSTIMER
MPRINT(STUDIO_RESTORE_WRAPPER):   options NOTES;
MPRINT(STUDIO_RESTORE_WRAPPER):   options SOURCE;
MPRINT(STUDIO_RESTORE_WRAPPER):   options STIMER;
MLOGIC(STUDIO_RESTORE_WRAPPER):  %LET (variable name is _STUDIONOTES)
MLOGIC(STUDIO_RESTORE_WRAPPER):  %LET (variable name is _STUDIOSOURCE)
MLOGIC(STUDIO_RESTORE_WRAPPER):  %LET (variable name is _STUDIOSTIMER)
MLOGIC(STUDIO_RESTORE_WRAPPER):  Ending execution.
83    %put &=start.;
START='2023-02-01'
84    %put &=end.;
END='2024-02-10'
85    %put &=role1.;
ROLE1='000666666','000777777','000555555','000444444','999KR','A222','AARRRRRR','AHAJER8','EINSKIL9','ECURIP9','EMCEER7'
86    
87    
88    %studio_hide_wrapper;
MLOGIC(STUDIO_HIDE_WRAPPER):  Beginning execution.
NOTE: The macro STUDIO_HIDE_WRAPPER is executing from memory.
      48 instructions 1088 bytes.
MLOGIC(STUDIO_HIDE_WRAPPER):  %GLOBAL  _STUDIONOTES
MLOGIC(STUDIO_HIDE_WRAPPER):  %GLOBAL  _STUDIOSOURCE
MLOGIC(STUDIO_HIDE_WRAPPER):  %GLOBAL  _STUDIOSTIMER
MLOGIC(STUDIO_HIDE_WRAPPER):  %IF condition &_studionotes = is TRUE
MLOGIC(STUDIO_HIDE_WRAPPER):  %LET (variable name is _STUDIONOTES)
MPRINT(STUDIO_HIDE_WRAPPER):   options nonotes;
MLOGIC(STUDIO_HIDE_WRAPPER):  %IF condition &_studiosource = is TRUE
MLOGIC(STUDIO_HIDE_WRAPPER):  %LET (variable name is _STUDIOSOURCE)
MPRINT(STUDIO_HIDE_WRAPPER):   options nosource;
MLOGIC(STUDIO_HIDE_WRAPPER):  %IF condition &_studiostimer = is TRUE
MLOGIC(STUDIO_HIDE_WRAPPER):  %LET (variable name is _STUDIOSTIMER)
MPRINT(STUDIO_HIDE_WRAPPER):   options nostimer;
MLOGIC(STUDIO_HIDE_WRAPPER):  Ending execution.
MLOGIC(STUDIO_RESTORE_WRAPPER):  Beginning execution.
MLOGIC(STUDIO_RESTORE_WRAPPER):  %GLOBAL  _STUDIONOTES
MLOGIC(STUDIO_RESTORE_WRAPPER):  %GLOBAL  _STUDIOSOURCE
MLOGIC(STUDIO_RESTORE_WRAPPER):  %GLOBAL  _STUDIOSTIMER
MPRINT(STUDIO_RESTORE_WRAPPER):   options NOTES;
MPRINT(STUDIO_RESTORE_WRAPPER):   options SOURCE;
MPRINT(STUDIO_RESTORE_WRAPPER):   options STIMER;
MLOGIC(STUDIO_RESTORE_WRAPPER):  %LET (variable name is _STUDIONOTES)
MLOGIC(STUDIO_RESTORE_WRAPPER):  %LET (variable name is _STUDIOSOURCE)
MLOGIC(STUDIO_RESTORE_WRAPPER):  %LET (variable name is _STUDIOSTIMER)
MLOGIC(STUDIO_RESTORE_WRAPPER):  Ending execution.
99    
100   

[Kurt_Bremser]

One thing I see: you create act_dttm on the SAS side:

crdttm as act_dttm format=datetime24.3

but you also use it in the pass-through on the Oracle side

where trunc(a.act_dttm)

[bhca60]

Should one of them be removed?

[Kurt_Bremser]

You need to know your data (Maxim 3).

Is act_dttm actually present in the database table? If not, this could be the root cause of your problems.

If it is, in what relation is it to crddtm? If it contains different logical data, I find it problematic to give it the same name.

[bhca60]

You're right there was a field that needed to be updated; now crusid is inptid ; I replaced in the two spots - in the select and the where statement and still got the error:

proc sql;
     connect to oracle as test (user=%SUPERQ(dra_u) password="&dra_p." path=huvhg); 

        create table now1(drop=crdttm) as
           select *, 
		  crdttm as act_dttm format=datetime24.3
			from connection to test
                (select distinct a.avdttm,
                                 a.id,
                                 a.acttpcd,
                                 a.actrscd,
                                 a.actotcom_cd,                            
                                 a.inptid as emp_id,
                                 a.cmtrid,
                                 a.act_dttm as crdttm,
         			 c.mktcd,
                                 c.srcstcd,
                                 d.pur_id,
                                 d.bol_cd,
                                 d.clmplcd,
			         d.ent,
                             case when d.plancd in ('MMM','RRR','DDDD') then 'RNM'
                                  else 'INE'
                             end as Business,
                                 d.plancd
          
                    from dra.rsh_dra_snart_act as a

                       left join dra.rsh_dra_case as c
                          on a.id = c.id

                       left join dra.rsh_dra_nonmdm_mbr as d
                          on c.id = d.id 
				
                          where trunc(a.act_dttm) between to_date(&start.,'yyyy-mm-dd') and to_date(&end.,'yyyy-mm-dd')
                            and a.inptid in (&role1.)
                            and (c.srcstcd not in ('5','6') or c.srcstcd is null)
                            and (d.pur_id not in ('0001234','0005678') or d.pur_id is null)
        
                          order by a.id, a.act_dttm);
                         
     disconnect from test;
quit;

ERROR: ORACLE prepare error: ORA-00933: SQL command not properly ended.

[SASKiwi]

As I've already suggested, simplify your query until it works, then progressively add lines back in until it fails. It's a lot easier to fix SQL when you know which line(s) is causing the problem. 

[Amir]

Hi,

Does your situation fit the SAS Problem Note 39997: A database error can occur when you submit an SQL query with a LIBNAME statement... ?

Thanks & kind regards,

Amir.