Datepart in EG 5.1 SQL

[D_Z_]

Hey All,

I have an issue that i cannot seem to figure out.  I am trying to do a datepart as part of my original SQL code.  Whenever i do, I get the following that show up in my log.  I have used several varying combinations but cannot figure out how to make it work.  I am trying to simply split the date away from the time so all i see is the date and DATEPART is not working for me.... I cannot figure out what i am doing wrong.  I would appreciate any help from the community on this one.

LOG:

16         Proc SQL noprint;

17         connect to oracle (user='SASAPP' orapw=XXXXXXXXXXX path=R800P preserve_comments);

18         create table work.CSI_CHECK as select * from connection to oracle

19           ( SELECT distinct

20           mq.dt_crtd,

21           ao.agreed_delivery_datetime,

22           datepart(ao.agreed_delivery_datetime) FORMAT=date9. as Delivery_Date,

23           MQ.ODR_ODR_NUMBER,

24           SP.SCRATCH_PAD AS ORDER_NOTES,

25           case

26           when ao.express_product_indicator ='Y' then 'GPT' else 'FPT' end as Channel,

27           ao.express_product_indicator,

28           MQ.USR_CRTD

29        

30        

31        

32           FROM ATS_MASTER_QUEUES       MQ,

33                ATS_ORD_SCRATCH_PAD     SP,

34           ATS_ORDERS              AO

35        

36        

37        

38         WHERE MQ.QUE_TYPE IN ('CSIN')

39         AND  MQ.ODR_ODR_NUMBER = SP.ODR_ODR_NUMBER

40         AND  MQ.USR_CRTD = SP.USR_CRTD

41         AND   MQ.ODR_ODR_NUMBER = AO.ODR_NUMBER

42          );

ERROR: ORACLE prepare error: ORA-00904: "DATEPART": invalid identifier. SQL statement: SELECT distinct mq.dt_crtd,

       ao.agreed_delivery_datetime, datepart(max(ao.agreed_delivery_datetime)) as Delivery_Date, MQ.ODR_ODR_NUMBER, SP.SCRATCH_PAD

       AS ORDER_NOTES, case when ao.express_product_indicator ='Y' then 'GPT' else 'FPT' end as Channel,

       ao.express_product_indicator, MQ.USR_CRTD FROM ATS_MASTER_QUEUES MQ, ATS_ORD_SCRATCH_PAD SP, ATS_ORDERS AO WHERE MQ.QUE_TYPE

       IN ('CSIN') AND MQ.ODR_ODR_NUMBER = SP.ODR_ODR_NUMBER AND MQ.USR_CRTD = SP.USR_CRTD AND MQ.ODR_ODR_NUMBER = AO.ODR_NUMBER.

[Linlin]

Hi

try changing

datepart(ao.agreed_delivery_datetime) FORMAT=date9. as Delivery_Date,

to

datepart(ao.agreed_delivery_datetime)  as Delivery_Date FORMAT=date9.,

[D_Z_]

Thank you Linlin!

I get the following in my log now:  (FYI, if i take out the datepart statement the query works as normal)

16         Proc SQL noprint;

17         connect to oracle (user='SASAPP' orapw=XXXXXXXXXXX path=R800P preserve_comments);

18         create table work.CSI_CHECK as select * from connection to oracle

19           ( SELECT distinct

20           mq.dt_crtd,

21           ao.agreed_delivery_datetime,

22           datepart(ao.agreed_delivery_datetime)  as Delivery_Date FORMAT=date9.,

23           MQ.ODR_ODR_NUMBER,

24           SP.SCRATCH_PAD AS ORDER_NOTES,

25           case

26           when ao.express_product_indicator ='Y' then 'GPT' else 'FPT' end as Channel,

27           ao.express_product_indicator,

28           MQ.USR_CRTD

29        

30        

31        

32           FROM ATS_MASTER_QUEUES       MQ,

33                ATS_ORD_SCRATCH_PAD     SP,

34           ATS_ORDERS              AO

35        

36        

37        

38         WHERE MQ.QUE_TYPE IN ('CSIN')

39         AND  MQ.ODR_ODR_NUMBER = SP.ODR_ODR_NUMBER

40         AND  MQ.USR_CRTD = SP.USR_CRTD

41         AND   MQ.ODR_ODR_NUMBER = AO.ODR_NUMBER

42         and   ao.agreed_delivery_datetime=datepart(ao.agreed_delivery_datetime)

43          );

ERROR: ORACLE prepare error: ORA-00923: FROM keyword not found where expected. SQL statement: SELECT distinct mq.dt_crtd,

       ao.agreed_delivery_datetime, datepart(ao.agreed_delivery_datetime) as Delivery_Date FORMAT=date9., MQ.ODR_ODR_NUMBER,

       SP.SCRATCH_PAD AS ORDER_NOTES, case when ao.express_product_indicator ='Y' then 'GPT' else 'FPT' end as Channel,

       ao.express_product_indicator, MQ.USR_CRTD FROM ATS_MASTER_QUEUES MQ, ATS_ORD_SCRATCH_PAD SP, ATS_ORDERS AO WHERE MQ.QUE_TYPE

       IN ('CSIN') AND MQ.ODR_ODR_NUMBER = SP.ODR_ODR_NUMBER AND MQ.USR_CRTD = SP.USR_CRTD AND MQ.ODR_ODR_NUMBER = AO.ODR_NUMBER

       and ao.agreed_delivery_datetime=datepart(ao.agreed_delivery_datetime).

[Patrick]

You are using explicit SQL pass-through so the SQL within the "connection to oracle" bit must be Oracle SQL flavour. You obviously can't use any SAS syntax like SAS functions and formats there.

I believe what would work is below code:

Proc SQL noprint;

  connect to oracle (user='SASAPP' orapw=XXXXXXXXXXX path=R800P preserve_comments);

  create table work.CSI_CHECK as

    select

      dt_crtd,

      agreed_delivery_datetime as agreed_delivery_datetime format=datetime21.,

      datepart(ao.agreed_delivery_datetime)  as Delivery_Date FORMAT=date9.,

      ODR_ODR_NUMBER,

      SCRATCH_PAD AS ORDER_NOTES,

      Channel,

      express_product_indicator,

      USR_CRTD

    from connection to oracle

      (

    SELECT distinct

      mq.dt_crtd,

      ao.agreed_delivery_datetime,

      ao.agreed_delivery_datetime,

      MQ.ODR_ODR_NUMBER,

      SP.SCRATCH_PAD AS ORDER_NOTES,

      case

        when ao.express_product_indicator ='Y' then 'GPT'

        else 'FPT'

      end

      as Channel,

      ao.express_product_indicator,

      MQ.USR_CRTD

    FROM ATS_MASTER_QUEUES       MQ,

      ATS_ORD_SCRATCH_PAD     SP,

      ATS_ORDERS              AO

    WHERE MQ.QUE_TYPE IN ('CSIN')

      AND  MQ.ODR_ODR_NUMBER = SP.ODR_ODR_NUMBER

      AND  MQ.USR_CRTD = SP.USR_CRTD

      AND   MQ.ODR_ODR_NUMBER = AO.ODR_NUMBER

      and   trunc(ao.agreed_delivery_datetime,'day')=trunc(ao.agreed_delivery_datetime,'day')

      );

quit;

Link to the docu: TRUNCATE

[D_Z_]

I tried what you suggested as well Patrick, and got the error below.  I cannot figure out why i would get a FROM Keyword not found where expected error but if i take it out the query runs just fine.

ERROR: ORACLE prepare error: ORA-00923: FROM keyword not found where expected. SQL statement: SELECT distinct mq.dt_crtd,

       agreed_delivery_datetime as agreed_delivery_datetime format=datetime21., datepart(ao.agreed_delivery_datetime) as

       Delivery_Date FORMAT=date9., MQ.ODR_ODR_NUMBER, SP.SCRATCH_PAD AS ORDER_NOTES, case when ao.express_product_indicator ='Y'

       then 'GPT' else 'FPT' end as Channel, ao.express_product_indicator, MQ.USR_CRTD FROM ATS_MASTER_QUEUES MQ,

       ATS_ORD_SCRATCH_PAD SP, ATS_ORDERS AO WHERE MQ.QUE_TYPE IN ('CSIN') AND MQ.ODR_ODR_NUMBER = SP.ODR_ODR_NUMBER AND

       MQ.USR_CRTD = SP.USR_CRTD AND MQ.ODR_ODR_NUMBER = AO.ODR_NUMBER and

       trunc(ao.agreed_delivery_datetime,'day')=trunc(ao.agreed_delivery_datetime,'day').

Dean

[Patrick]

The Error log you posted is not from executing the code I've posted. There is still a datepart() function and the format= in the select clause.

In the code I have posted was actually still a glitch which in below version should be removed. I can't test the code but the reason why you're getting errors is exactly as I said previously.

How did you create the code in first place? Was this just via EG wizard and then this "analyze" bit which converts the code into pass-through SQL? Do you understand the generated SQL? Because if you understand the SQL and where you can use SAS functions, formats,... and where not, then it's really no more difficult to fix.

Proc SQL noprint;

  connect to oracle (user='SASAPP' orapw=XXXXXXXXXXX path=R800P preserve_comments);

  create table work.CSI_CHECK as

    select

      dt_crtd,

      agreed_delivery_datetime as agreed_delivery_datetime format=datetime21.,

      datepart(agreed_delivery_datetime) as Delivery_Date FORMAT=date9.,

      ODR_ODR_NUMBER,

      SCRATCH_PAD AS ORDER_NOTES,

      Channel,

      express_product_indicator,

      USR_CRTD

    from connection to oracle

      (

      SELECT distinct

        mq.dt_crtd,

        ao.agreed_delivery_datetime,

        MQ.ODR_ODR_NUMBER,

        SP.SCRATCH_PAD AS ORDER_NOTES,

      case

        when ao.express_product_indicator ='Y' then 'GPT'

        else 'FPT'

      end

      as Channel,

        ao.express_product_indicator,

        MQ.USR_CRTD

      FROM ATS_MASTER_QUEUES MQ,

        ATS_ORD_SCRATCH_PAD SP,

        ATS_ORDERS AO

      WHERE MQ.QUE_TYPE IN ('CSIN')

        AND MQ.ODR_ODR_NUMBER = SP.ODR_ODR_NUMBER

        AND MQ.USR_CRTD = SP.USR_CRTD

        AND MQ.ODR_ODR_NUMBER = AO.ODR_NUMBER

        and trunc(ao.agreed_delivery_datetime,'day')=trunc(ao.agreed_delivery_datetime,'day')

      );

quit;