2

I am currently writing some Code to get a view over issued service-tickets from my company and where they have been routed. I am using Birt for Eclipse to create a report who uses that Code. While using Toad 12.0.10.30, no exception is thrown, same for Birt. When uploading the Report to Maximo (IBM) and running the report there, I am getting a failed report with the Log file stating that 

[ERROR] [MXQServer1] [] DataSet [Data_Set_Name] fetch failed: 
java.sql.SQLDataException: ORA-01843: not a valid month

I tried Fixing up the REGEX of my date values and checking if everything should work and is, in fact, a date.

These are the parts of my Code that use Dates:

and trunc(creationdate) between to_date('01.01.2019', 'dd.MM.RRRR') and TRUNC(sysdate-1)

This part of the Code (Code is written with "with" blocks) issues a list of the months till yesterday, I Need that part for grouping later.

SELECT     TO_CHAR ( ADD_MONTHS ( start_dt, LEVEL - 1), 'DD/MM/YYYY') AS monate 
FROM     (
SELECT  TRUNC ( TO_DATE ( '01/01/2019', 'DD/MM/YYYY')) AS start_dt, 
TRUNC ( sysdate-1) AS end_dt FROM dual
) 
CONNECT BY     LEVEL <= 1 + MONTHS_BETWEEN ( end_dt , start_dt)

This is how the report issued over Maximo is creating the sysdate:

2019-09-19 07:47:59.0

Could it be that, while running the report, the month is parsed wrong? Cause the sysdate is built different then my Formation (YYYY-MM-DD to DD/MM/YYYY). How would I be able to fix that?

Edit: While Running this Code "on Maximo"

SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT'

as suggested by user @MT0 I get 

DD-MON-RR

as a return. With this in mind, is there a way to modify my Code to make it work as intended?

Caenerys
  • 57
  • 1
  • 9
  • 1
    If you do `SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT'` on Maximo what does it return? – MT0 Sep 19 '19 at 07:48
  • I think you should provide more details.Are you using dataset parameters somewhere? Is your datea in the database actually declared as – hvb Sep 20 '19 at 07:12
  • I think you should provide more details: - The complete SQL query. - Are you using dataset parameters in the query? - If the report contains several datasets, are you sure that it is particular query which is causing the error? Are the corresponding columns in the database really of type DATE or TIMESTAMP or VARCHAR2? The reason could well be an implicit conversion from VARCHAR2 to DATE (Oracle does this if you search for a date in a VARCHAR2 column). – hvb Sep 20 '19 at 07:18
  • @MT0 when doing that on Maximo I get `DD-MON-RR` as a return, database Returns `DD-MM-RR`.... that might be the Problem? But how to adjust the Code then? – Caenerys Sep 20 '19 at 07:49
  • @Caenerys `MON` will match `MON` or `MONTH` but not `MM` formatted values. You are using `TO_CHAR( ...., 'DD/MM/YYYY' )` to convert the date value to a string. If Maximo is expecting your query to return a date then it could implicitly cast the string back to a date using the NLS session parameters as the format model and fail; if this is the case then you would be better removing the `TO_CHAR` and just returning a `DATE` data type so no conversion is necessary; if you do need to use `TO_CHAR` then the format model used in your output might need to match the session's default. – MT0 Sep 20 '19 at 08:14

1 Answers1

3

I would say, just remove the TO_CHAR(). If you generate a DATE then you should keep it as DATE value.

SELECT ADD_MONTHS(DATE '2019-01-01', LEVEL - 1) AS monate 
FROM dual 
CONNECT BY LEVEL <= 1 + MONTHS_BETWEEN(TRUNC(SYSDATE-1), DATE '2019-01-01');

or 

WITH dt AS 
    (SELECT DATE '2019-01-01' AS start_dt FROM dual)
SELECT ADD_MONTHS(start_dt, LEVEL - 1) AS monate 
FROM dt 
CONNECT BY LEVEL <= 1 + MONTHS_BETWEEN(TRUNC(SYSDATE-1), start_dt);
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Thanks a lot for this Code! I changed it up in all of my Data Sets and now its running like intended and doesnt throw and exception! – Caenerys Sep 20 '19 at 08:21