datepart and intnx in oracle pass through using proc sql

[SkandaPannu]

Hi ,

I have the following code throwing errors.Can you please help me with correct code

proc sql;

connect to oracle

(DEFER=XXX PATH=XXXX AUTHDOMAIN="XXXXXXXX"

);

create table temp as 

select *

from connection to ORACLE

(

select  at_date,

           datepart(at_date) as date

           shop

from   ABC.GYGYGYG

where 

      datepart(at_date)=intnx('month','today()',-1,'e')/*want to get last date of last month*/

);

[PeterClemmensen]

Post your log, that makes it easier to see where your code fails..

But first off, the today() function in your INTNX function should not be in quotation marks 🙂

[SkandaPannu]

ERROR: ORACLE prepare error: ORA-00923: FROM keyword not found where expected.

[PeterClemmensen]

Post your entire log please 🙂

Also, you need a comma between date and shop in the code below

select  at_date,
           datepart(at_date) as date /* You need a comma here */
           shop
from   ABC.GYGYGYG

[SkandaPannu]

proc sql;

connect to oracle

(DEFER=XXX PATH=XXXX AUTHDOMAIN="XXXXXXXX"

);

create table temp as 

select *

from connection to ORACLE

(

select  at_date,

           shop,

           datepart(at_date) as date

from   ABC.GYGYGYG

where 

      datepart(at_date)=intnx('month',today(),-1,'e')  /*want to get last date of last month*/

);

log:

ERROR: ORACLE prepare error: ORA-00923: FROM keyword not found where expected. SQL Statement: select at_date,shop,datepart(at_date) as date from ABC.GYGYGYG 

where 

      datepart(at_date)=intnx('month',today(),-1,'e') 

[Shmuel]

You "want to get last date of last month" - 

you can do it by today() - day(today()).

[SkandaPannu]

same error sir .

Problem with FROM 

FROM keyword not found where expected

[RW9]

Corrected your code:

proc sql;
  connect to oracle (DEFER=XXX PATH=XXXX AUTHDOMAIN="XXXXXXXX");
  create table temp as 
  select *
  from connection to ORACLE
  (select  at_date,
           datepart(at_date) as date,
           shop
   from   ABC.GYGYGYG
   where datepart(at_date)=intnx('month',today(),-1,'e'));
  disconnect from oracle;
quit;

Note than I put a comma after the "as date" part which you had missing, and removed today() from quotes. 

Do note however, I doubt today() will work as that is a SAS function.  If your passing SQL through to the database, then you need to use SQL and functions appropriate to the SQL database, you would need to use SYSDATE or CURRENT_DATE according to the Oracle documents:

https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions037.htm#SQLRF00628

Of course you could drop the where clause completely, return all the records and then filter it in a simple datastep afterwards.

[SkandaPannu]

this code is not throwing errors or not returning output

[RW9]

And I can see these through the magic of guesswork?

Post logs outputs showing errors, post the code used which generates those errors!

As I said, today() will not work in Oracle - it doesn't exist, you need to use Oracle specific SQL.

[SkandaPannu]

should i use curent_date.

not returning errors or ourput