topic Re: ERROR: ORACLE prepare error: ORA-00933: SQL command not properly ended. in SAS Programming

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

bhca60 — Sun, 18 Feb 2024 03:51:04 GMT

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.

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

Patrick — Sun, 18 Feb 2024 03:11:34 GMT

You're script is missing a comma.

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

bhca60 — Sun, 18 Feb 2024 03:31:41 GMT

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

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

bhca60 — Sun, 18 Feb 2024 04:11:38 GMT


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?

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

SASKiwi — Sun, 18 Feb 2024 04:40:17 GMT

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.

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

bhca60 — Sun, 18 Feb 2024 05:13:54 GMT

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?

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

Patrick — Sun, 18 Feb 2024 09:56:08 GMT

@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.

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

Amir — Sun, 18 Feb 2024 10:04:44 GMT

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 that uses the MULTI_DATASRC_OPT=IN_CLAUSE option ?

Thanks & kind regards,

Amir.

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

Kurt_Bremser — Sun, 18 Feb 2024 11:39:08 GMT

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.

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

bhca60 — Sun, 18 Feb 2024 15:02:57 GMT

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

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

Kurt_Bremser — Sun, 18 Feb 2024 15:44:55 GMT

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)

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

bhca60 — Sun, 18 Feb 2024 15:58:19 GMT

I dont see that error

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

bhca60 — Sun, 18 Feb 2024 16:09:46 GMT

Should one of them be removed?

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

Kurt_Bremser — Sun, 18 Feb 2024 17:21:25 GMT

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.

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

bhca60 — Sun, 18 Feb 2024 22:08:19 GMT

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.

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

SASKiwi — Sun, 18 Feb 2024 22:23:00 GMT

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.

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

Finn-Ol — Sun, 13 Oct 2024 12:31:44 GMT

ORA-00933 points to a sql syntax error. check the query for formatting issues or missing keywords. And if you're thinking to bringing cocker spaniel, first must go thought this comparison on show and working spaniels.