BookmarkSubscribeRSS Feed
bhca60
Quartz | Level 8
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.

15 REPLIES 15
Patrick
Opal | Level 21

You're script is missing a comma.

Patrick_0-1708225866181.png

 

bhca60
Quartz | Level 8

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

SASKiwi
PROC Star

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
Quartz | Level 8

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
Opal | Level 21

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

Patrick_0-1708250118180.png

 

 

bhca60
Quartz | Level 8

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
Super User

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
Quartz | Level 8
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
Super User

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
Quartz | Level 8

Should one of them be removed?

Kurt_Bremser
Super User

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
Quartz | Level 8

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
PROC Star

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. 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 1653 views
  • 0 likes
  • 5 in conversation