HydrologicEngineeringCenter / HydrologicEngineeringCenter/cwms-database
Unable to retrieve seasonal location level data for end-of-month dates
Nobody has claimed this yet.
- Dominant language
- Visual Basic 6.0
- Stars
- 4
- Forks
- 8
- Avg merge
- 8d 20h
- Merged PRs (30d)
- 4
Description
Test case in CDA can be found here: https://github.com/USACE/cwms-data-api/pull/1895
When storing and retrieving seasonal location level data, a DB error is thrown. The seasonal dates are set at the last day of each month.
org.jooq.exception.DataException: SQL [{ ? = call "CWMS_20"."CWMS_LEVEL"."RETRIEVE_LOCATION_LEVEL" (?, ?, cast(? as date), ?, ?, ?, ?, ?, ?, ?) }]; ORA-01839: date not valid for month specified
ORA-06512: at "CWMS_20.ZLOCATION_LEVEL_T", line 109
ORA-06512: at "CWMS_20.ZLOCATION_LEVEL_T", line 109
ORA-06512: at "CWMS_20.CWMS_LEVEL", line 3863
ORA-06512: at line 1
https://docs.oracle.com/error-help/db/ora-01839/
This appears to be caused by the offset-to-date conversion in the database.
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start with the CDA test case in pull request 1895 and reproduce retrieval of seasonal location-level data for dates on the last day of a month. Trace CWMS_LEVEL.RETRIEVE_LOCATION_LEVEL and the ZLOCATION_LEVEL_T error at line 109, focusing on the offset-to-date conversion that raises Oracle ORA-01839. Done means end-of-month seasonal dates can be stored and retrieved without the database error.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- database
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100