How to fix ORA error - invalid weekday?

Asked

Viewed 73 times

0

When I run the direct query in Oracle returns correct, but when I do via Intelij/java, returns the error: ORA-01846: it is not a valid day of the week.

 SELECT analog,DEVTYP, trunc(data), Max(dis)dis
  FROM table a, table b
  WHERE a.table_index = b.table_index
      AND 
      b.substn = 'valor1'
      AND b.DEVTYP = 'valor2'
      and
      b.analog = 'valor3'
      AND   
   -- CONDITION FOR LAST THREE FRIDAYS
   TRUNC(data) IN (
        TRUNC(NEXT_DAY(SYSDATE - 8,'WED')),
        TRUNC(NEXT_DAY(SYSDATE - 15,'WED')),
        TRUNC(NEXT_DAY(SYSDATE - 22,'WED'))
    ) 
   -- CONDITION FOR 12 TO 18 HOURS
   AND data - TRUNC(data) BETWEEN 12/24 and 18/24
   GROUP BY trunc(data), analog,DEVTYP
oracle
  • 1

    The good thing is to always be in "ingrés", everything in general works , I found this treaty to solve this. https://community.oracle.com/thread/4023654?start=15&tstart=0

  • TRUNC(NEXT_DAY(SYSDATE - 8,4)), tb worked here in my version

  • Thank you! I changed my code in java to inform "Mon", "Ter", ..... for the days of the week, and then it worked.

1 answer

0

Solution: Use Portuguese abbreviated days of the week when creating the query in Java. **If run directly in the database, it works with abbreviations in English.

 SELECT analog,DEVTYP, trunc(data), Max(dis)dis
  FROM table a, table b
  WHERE a.table_index = b.table_index
      AND 
      b.substn = 'valor1'
      AND b.DEVTYP = 'valor2'
      and
      b.analog = 'valor3'
      AND   
   -- CONDITION FOR LAST THREE FRIDAYS
   TRUNC(data) IN (
        TRUNC(NEXT_DAY(SYSDATE - 8,'Qua')),
        TRUNC(NEXT_DAY(SYSDATE - 15,'Qua')),
        TRUNC(NEXT_DAY(SYSDATE - 22,'Qua'))
    ) 
   -- CONDITION FOR 12 TO 18 HOURS
   AND data - TRUNC(data) BETWEEN 12/24 and 18/24
   GROUP BY trunc(data), analog,DEVTYP

Browser other questions tagged oracle

You are not signed in. Login or sign up in order to post.