topic Re: SQL command not properly ended. in SAS Programming

SQL command not properly ended.

Ying — Thu, 08 Aug 2019 22:31:26 GMT

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;

Re: SQL command not properly ended.

Reeza — Thu, 08 Aug 2019 22:38:55 GMT

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;

Re: SQL command not properly ended.

Ying — Thu, 08 Aug 2019 22:58:08 GMT

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

Re: SQL command not properly ended.

art297 — Fri, 09 Aug 2019 00:37:32 GMT

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

Re: SQL command not properly ended.

Ying — Fri, 09 Aug 2019 01:09:20 GMT

Thank you, art297!

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

Re: SQL command not properly ended.

art297 — Fri, 09 Aug 2019 01:16:36 GMT

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

Re: SQL command not properly ended.

ChrisNZ — Fri, 09 Aug 2019 02:14:59 GMT

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?

Re: SQL command not properly ended.

Reeza — Fri, 09 Aug 2019 02:39:23 GMT

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

Re: SQL command not properly ended.

Ying — Fri, 09 Aug 2019 03:08:53 GMT

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.

Re: SQL command not properly ended.

Kurt_Bremser — Fri, 09 Aug 2019 05:50:36 GMT

You also have to use Oracle-style date literals.

Re: SQL command not properly ended.

Tom — Fri, 09 Aug 2019 16:31:48 GMT

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;