SQL command not properly ended.

[Ying]

Hello,

I couldn't figure out why I got this message.  Is it because PUT function used in query?  Could you please help?  Thank you!

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

  proc sql;
    &Database_Login.; 
    create table temp as
    select * from connection to oracle
    (select ID        
           ,STORE_NUMBER              
           ,QTY            
   FROM DB.PAYMENT_TBL as A,
        (SELECT DISTINCT put(STORE_NUMBER_n, z7.) as STORE_NUMBER FROM DB.REF_STORE_NUMBER) as C
   WHERE A.STORE_NUMBER = C.STORE_NUMBER
         and trunc(SALE_DATETIME) BETWEEN '01Oct2017' AND '01Nov2017'
   );
    Disconnect from oracle;
  quit; 

[Reeza]

and trunc(SALE_DATETIME) BETWEEN '01Oct2017' AND '01Nov2017'

I don't believe that's how you specify dates in Oracle. I think it's 'YY-MM-DD' instead of date9 format.

A good way to check is to test your query in Oracle and see if it runs or not.

---

@Ying wrote:

Hello,

I couldn't figure out why I got this message.  Is it because PUT function used in query?  Could you please help?  Thank you!

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

  proc sql;
    &Database_Login.; 
    create table temp as
    select * from connection to oracle
    (select ID        
           ,STORE_NUMBER              
           ,QTY            
   FROM DB.PAYMENT_TBL as A,
        (SELECT DISTINCT put(STORE_NUMBER_n, z7.) as STORE_NUMBER FROM DB.REF_STORE_NUMBER) as C
   WHERE A.STORE_NUMBER = C.STORE_NUMBER
         and trunc(SALE_DATETIME) BETWEEN '01Oct2017' AND '01Nov2017'
   );
    Disconnect from oracle;
  quit; 

---

[Ying]

Thank you, Reeza!  The date format is fine.  I use it all the time. 

[Reeza]

---

@Ying wrote:

Thank you, Reeza!  The date format is fine.  I use it all the time. 

---

In pass through SQL? Which version of Oracle are you using that support that?

[ChrisNZ]

proc sql;
    &Database_Login.;  
    create table temp as
    select * from connection to oracle
    (select ID         
           ,STORE_NUMBER               
           ,QTY             
   FROM DB.PAYMENT_TBL as A,
        (SELECT DISTINCT put(STORE_NUMBER_n, z7.) as STORE_NUMBER FROM DB.REF_STORE_NUMBER) as C
   WHERE A.STORE_NUMBER = C.STORE_NUMBER
         and trunc(SALE_DATETIME) BETWEEN '01Oct2017' AND '01Nov2017'
   );
    Disconnect from oracle;
  quit; 

I colored in blue the SAS code and in red the Oracle code.

Is there an Oracle put function? How do you expect Oracle to run a SAS function?

[Ying]

Thank you, Chris!

When run the inside piece on SQL developer, I don't need to convert the number to character, and it works.  I don't think the PUT function works in here.   

[Kurt_Bremser]

You also have to use Oracle-style date literals.

[art297]

I don't think you even need the put statement, but I do think that you have to identify the dates as date constants, add a format to the store_number_n variable, and check where you have closing parenthses.

I can't help with the Oracle part but, hopefully, this will give you some clues regarding the rest:

data db_ref_store_number;
  input store_number_n;
  cards;
1
1
2
2
;

data db_payment_tbl;
  format store_number z7.;
  input id store_number qty;
  cards;
1 0000001 5
1 0000001 10
2 0000002 3
2 0000002 5
;
 
data connection_to_oracle;
  informat sale_datetime anydtdtm.;
  input x y z sale_datetime;
  cards;
4 5 6 10oct2017:18:00
7 8 9 12oct2017:18:00
;

  
proc sql;
/*     &Database_Login.;   */
    create table temp as
    select * from connection_to_oracle
,
    (select ID,a.STORE_NUMBER,QTY             
         FROM DB_PAYMENT_TBL as A),
     (SELECT DISTINCT STORE_NUMBER_n as CSTORE_NUMBER format=z7. FROM DB_REF_STORE_NUMBER)
        WHERE A.STORE_NUMBER = CSTORE_NUMBER
         and datepart(SALE_DATETIME) BETWEEN '01Oct2017'd AND '01Nov2017'd
   ;
/*     Disconnect from oracle; */
  quit; 

Art, CEO, AnalystFinder.com

[Ying]

Thank you, art297! 

without running in Oracle, PUT(STORE_NUMBER_n, z7.) does the same as your FORMAT=Z7..  Either way gives me error.

[art297]

Then check into the other things I mentioned, especially the use of date constants (in the where statement), and where you have your parentheses.

Art, CEO, AnalystFinder.com

[Tom]

Why are you using the PUT() function in the query?  Looks like one table has it as a number and the other has it as a string?

Perhaps you can try doing the conversion the other way?  Although there might be some performance issues.

Also make sure your Oracle code uses proper Oracle syntax for dates.  For example you appear be just using DDMONYYYY as the default format for date constants, but what format works depends on your settings.  https://oracle-base.com/articles/misc/oracle-dates-timestamps-and-intervals  You could try using ANSI syntax instead.

You might want to also be clearer about which version of STORE_NUMBER you want.  Or for that matter where ID and QTY come from.

proc sql;
  &Database_Login.;
  create table temp as
    select * from connection to oracle
    (select A.ID
          , A.STORE_NUMBER
          , A.QTY
     FROM DB.PAYMENT_TBL as A
         , (SELECT DISTINCT STORE_NUMBER_n FROM DB.REF_STORE_NUMBER) as C
     WHERE cast(A.STORE_NUMBER as integer) = C.STORE_NUMBER_n
       and trunc(SALE_DATETIME) BETWEEN DATE '2017-10-01' AND DATE '2017-11-01'
    )
  ;
  disconnect from oracle;
quit;