An IAL grouping transaction data by YYYYMM works fine, but a second IAL layering totals from the first, or a Quick Report querying it, fails with '(full) year must be between -4713 and +9999 and not be 0' — even though every individual row has been confirmed to have a valid date.

All FAQ questions / Reporting and analytics

Answer

Check whether an over-broad or unconstrained date filter elsewhere in the query is unintentionally letting through some genuinely invalid or out-of-range date value in a JOIN or WHERE condition, even if the source rows themselves all look valid individually. A reliable way to correctly scope a query strictly to the current calendar year's actual valid date range, rather than risking a malformed comparison, is deriving the year's exact start and end boundaries directly from SYSDATE: use TRUNC(SYSDATE, 'YEAR') for the beginning of the year, and ADD_MONTHS(TRUNC(SYSDATE,'YEAR'), 12) - 1/24/60/60 for the very last second of the year, then use those two derived boundary values in a BETWEEN condition against the relevant date column, rather than a looser or less precisely bounded date comparison.

Documentation-based answer. Confirm the product and release before applying it. This answer has not been verified in an IFS environment.

← Browse Reporting and analytics questions