ORA-00905: Trouble converting Mysql syntax to oracle syntax

Question

I have a mysql code and need to convert it Oracle syntax and I faced with this error. Could anyone help me?

SELECT SUM(T.SEND_UNREAD_DRAFT) SEND_UNREAD_DRAFT, SUM(T.SEND_READ_DRAFT) SEND_READ_DRAFT, 
  SUM(T.SEND_APPROVED) SEND_APPROVED, SUM(T.SEND_COMPLETED) SEND_COMPLETED, 
    SUM(T.SEND_FAILED)SEND_FAILED,SUM(T.RECEIVED_DRAFT)RECEIVED_DRAFT,SUM(T.RECEIVED_APPROVED)RECEIVED_APPROVED, 
            Sum(T.Received_Accepted_Send)Received_Accepted_Send,Sum(T.Received_Rejected_Send)Received_Rejected_Send, Sum(T.Send_Canceled)Send_Canceled 
        FROM 
                 (SELECT 
            (CASE WHEN TYPE = 'OUT' THEN (CASE WHEN STATUS = 'DRAFT' THEN (CASE WHEN READ_FLAG = 'N' THEN 1 ELSE 0 END) ELSE 0 END) ELSE 0 END) SEND_UNREAD_DRAFT, 
            (CASE WHEN TYPE = 'OUT' THEN (CASE WHEN STATUS = 'DRAFT' THEN (CASE WHEN READ_FLAG = 'Y' THEN 1 ELSE 0 END) ELSE 0 END) ELSE 0 END) SEND_READ_DRAFT, 
            (CASE WHEN TYPE = 'OUT' THEN (CASE WHEN STATUS = 'APPROVED' THEN 1 ELSE 0 END) ELSE 0 END) SEND_APPROVED, 
            (CASE WHEN TYPE = 'OUT' THEN (CASE WHEN STATUS = 'COMPLETED' THEN 1 ELSE 0 END) ELSE 0 END) SEND_COMPLETED, 
            (CASE WHEN TYPE = 'OUT' THEN (CASE WHEN STATUS = 'FAILED' THEN 1 ELSE 0 END) ELSE 0 END) SEND_FAILED, 
            (CASE WHEN TYPE = 'OUT' THEN (CASE WHEN STATUS = 'CANCELED' THEN 1 ELSE 0 END) ELSE 0 END) SEND_CANCELED, 
            (CASE WHEN TYPE = 'OUT' THEN (CASE WHEN STATUS = 'DRAFT' THEN 1 ELSE 0 END) ELSE 0 END) RECEIVED_DRAFT, 
            (CASE WHEN TYPE = 'OUT' THEN (CASE WHEN STATUS = 'APPROVED' THEN 1 ELSE 0 END) ELSE 0 END) RECEIVED_APPROVED, 
            (CASE WHEN TYPE = 'IN' THEN (CASE WHEN STATUS = 'COMPLETED' THEN (CASE WHEN INVOICE_STATUS = 'ACCEPTED' THEN 1 ELSE 0 END) ELSE 0 END) ELSE 0 END) RECEIVED_ACCEPTED_SEND, 
            (CASE WHEN TYPE = 'IN' THEN (CASE WHEN STATUS = 'COMPLETED' THEN (CASE WHEN INVOICE_STATUS = 'REJECTED' THEN 1 ELSE 0 END) ELSE 0 END) ELSE 0 END) RECEIVED_REJECTED_SEND 
               From Eis_Invoice_Header      
                  Where Invoice_Date Between  Sysdate()-365 And (sysdate + Interval '3' Month from dual)) as T

it seems you have a problem in this code: 'WHERE Invoice_Date BETWEEN SYSDATE () - 365 AND (SYSDATE + INTERVAL '3' MONTH from dual)) as T' first you should write sysdate as I wrote not like a function. In the second part what do you want to do? if you want to add three month to sysdate you should use 'add_months(sysdate, 3)' — Canburak Tümer
– Canburak Tümer, Commented Aug 26, 2013 at 7:11
This was the mySql code 'SYSDATE()-365 AND DATE_ADD(sysdate(),INTERVAL 3 MONTH)) T ";' also add_month gives error. — user2682929
– user2682929, Commented Aug 26, 2013 at 7:19

ajmalmhd04 · Accepted Answer · 2013-08-26 07:27:18Z

1

the query select sysdate + interval '3' month , add_months(sysdate, 3) from dual; are same.
in your query, use where invoice_date between sysdate- 365 and sysdate + interval '3' month'

answered Aug 26, 2013 at 7:27

ajmalmhd04

2,6126 gold badges26 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2682929 Over a year ago

It worked, thank you but also I changed As which comes before T

ajmalmhd04 Over a year ago

using add_mohts() is better than using interval, because it may fails with the case for eg: to_date('30-jan-2013','dd-mon-yyyy') + interval '1' month'

Dba · Accepted Answer · 2013-08-26 07:32:29Z

Try the below query,

SELECT SUM(T.SEND_UNREAD_DRAFT) SEND_UNREAD_DRAFT, 
       SUM(T.SEND_READ_DRAFT) SEND_READ_DRAFT, 
       SUM(T.SEND_APPROVED) SEND_APPROVED, 
       SUM(T.SEND_COMPLETED) SEND_COMPLETED, 
       SUM(T.SEND_FAILED) SEND_FAILED,
       SUM(T.RECEIVED_DRAFT) RECEIVED_DRAFT,
       SUM(T.RECEIVED_APPROVED) RECEIVED_APPROVED, 
       Sum(T.Received_Accepted_Send) Received_Accepted_Send,
       Sum(T.Received_Rejected_Send) Received_Rejected_Send, 
       Sum(T.Send_Canceled) Send_Canceled 
  FROM 
       (SELECT (CASE WHEN TYPE = 'OUT' THEN (CASE WHEN STATUS = 'DRAFT' THEN (CASE WHEN READ_FLAG = 'N' THEN 1 ELSE 0 END) ELSE 0 END) ELSE 0 END) SEND_UNREAD_DRAFT, 
               (CASE WHEN TYPE = 'OUT' THEN (CASE WHEN STATUS = 'DRAFT' THEN (CASE WHEN READ_FLAG = 'Y' THEN 1 ELSE 0 END) ELSE 0 END) ELSE 0 END) SEND_READ_DRAFT, 
               (CASE WHEN TYPE = 'OUT' THEN (CASE WHEN STATUS = 'APPROVED' THEN 1 ELSE 0 END) ELSE 0 END) SEND_APPROVED, 
               (CASE WHEN TYPE = 'OUT' THEN (CASE WHEN STATUS = 'COMPLETED' THEN 1 ELSE 0 END) ELSE 0 END) SEND_COMPLETED, 
               (CASE WHEN TYPE = 'OUT' THEN (CASE WHEN STATUS = 'FAILED' THEN 1 ELSE 0 END) ELSE 0 END) SEND_FAILED, 
               (CASE WHEN TYPE = 'OUT' THEN (CASE WHEN STATUS = 'CANCELED' THEN 1 ELSE 0 END) ELSE 0 END) SEND_CANCELED, 
               (CASE WHEN TYPE = 'OUT' THEN (CASE WHEN STATUS = 'DRAFT' THEN 1 ELSE 0 END) ELSE 0 END) RECEIVED_DRAFT, 
               (CASE WHEN TYPE = 'OUT' THEN (CASE WHEN STATUS = 'APPROVED' THEN 1 ELSE 0 END) ELSE 0 END) RECEIVED_APPROVED, 
               (CASE WHEN TYPE = 'IN' THEN (CASE WHEN STATUS = 'COMPLETED' THEN (CASE WHEN INVOICE_STATUS = 'ACCEPTED' THEN 1 ELSE 0 END) ELSE 0 END) ELSE 0 END) RECEIVED_ACCEPTED_SEND, 
               (CASE WHEN TYPE = 'IN' THEN (CASE WHEN STATUS = 'COMPLETED' THEN (CASE WHEN INVOICE_STATUS = 'REJECTED' THEN 1 ELSE 0 END) ELSE 0 END) ELSE 0 END) RECEIVED_REJECTED_SEND 
          From Eis_Invoice_Header   
         WHERE invoice_date BETWEEN add_months(sysdate, -12) --SYSDATE - 365
           AND add_months(sysdate, 3));

DB_learner · Accepted Answer · 2013-08-26 07:32:34Z

You have to convert interval to add_months and sysdate() to sysdate. And you could also change to case to make it simple.

SELECT SUM(T.SEND_UNREAD_DRAFT) SEND_UNREAD_DRAFT, SUM(T.SEND_READ_DRAFT) SEND_READ_DRAFT, SUM(T.SEND_APPROVED) SEND_APPROVED, SUM(T.SEND_COMPLETED)     SEND_COMPLETED, SUM(T.SEND_FAILED) SEND_FAILED,SUM(T.RECEIVED_DRAFT) RECEIVED_DRAFT,SUM(T.RECEIVED_APPROVED) RECEIVED_APPROVED, Sum(T.Received_Accepted_Send)     Received_Accepted_Send, Sum(T.Received_Rejected_Send) Received_Rejected_Send, Sum(T.Send_Canceled) Send_Canceled 
FROM( 
SELECT CASE WHEN TYPE = 'OUT' and STATUS = 'DRAFT' and READ_FLAG = 'N' THEN 1 ELSE 0 END SEND_UNREAD_DRAFT, CASE WHEN TYPE = 'OUT' and STATUS = 'DRAFT' and     READ_FLAG = 'Y' THEN 1 ELSE 0 END SEND_READ_DRAFT, CASE WHEN TYPE = 'OUT' and STATUS = 'APPROVED' THEN 1 ELSE 0 END SEND_APPROVED, CASE WHEN TYPE = 'OUT' and     STATUS = 'COMPLETED' THEN 1 ELSE 0 END  SEND_COMPLETED, CASE WHEN TYPE = 'OUT' and STATUS = 'FAILED' THEN 1 ELSE 0 END SEND_FAILED, CASE WHEN TYPE = 'OUT' and     STATUS = 'CANCELED' THEN 1 ELSE 0 END SEND_CANCELED, CASE WHEN TYPE = 'OUT' and STATUS = 'DRAFT' THEN 1 ELSE 0 END RECEIVED_DRAFT, CASE WHEN TYPE = 'OUT' and     STATUS = 'APPROVED' THEN 1 ELSE 0 END RECEIVED_APPROVED, CASE WHEN TYPE = 'IN' and STATUS = 'COMPLETED' and INVOICE_STATUS = 'ACCEPTED' THEN 1 ELSE 0 END     RECEIVED_ACCEPTED_SEND, CASE WHEN TYPE = 'IN' and STATUS = 'COMPLETED' and INVOICE_STATUS = 'REJECTED' THEN 1 ELSE 0 END RECEIVED_REJECTED_SEND From     Eis_Invoice_Header Where Invoice_Date Between  Sysdate-365 And add_months(sysdate ,3)
) as T

sysdate + Interval '3' Month will work just fine for Oracle.

Collectives™ on Stack Overflow

ORA-00905: Trouble converting Mysql syntax to oracle syntax

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related