My Oracle Support Banner

DEFD1688 Is Failing Due To Error ORA-01841 during DIM_DATES Full Table Scan (Doc ID 2740309.1)

Last updated on JANUARY 01, 2021

Applies to:

Oracle Financial Services - Regulatory Reporting for US Federal Reserve - Lombard Risk Integration Pack - Version 8.0.9 and later
Information in this document applies to any platform.

Symptoms

REG REP USFED 8.0.9.5.x

Data set DSFD1688 has the below join condition in the query-


INNER JOIN dim_dates a_dim_daily_dates ON fct_reg_account_summary.n_mis_date_skey = a_dim_daily_dates.n_date_skey
INNER JOIN dim_dates ON a_dim_daily_dates.d_calendar_date >= add_months(dim_dates.d_calendar_date + 1, - 6)
AND a_dim_daily_dates.d_calendar_date <= dim_dates.d_calendar_date
 
During a full table scan of DIM_DATES, the condition (dim_dates.d_calendar_date + 1) is causing the value to go beyond the range of -4713 and +9999

Ex when When d_calendar_date = '31-DEC-9999' Expression (dim_dates.d_calendar_date + 1) will result in year 10,000 (01-JAN-10000, that causing the error - ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Changes

 NA

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.