No question at this time
DBA Top 10
1 A. Kavsek 10000
2 M. Cadot 9900
3 B. Vroman 5700
4 P. Wisse 4300
5 J. PĂ©ran 1800
6 . Lauri 1200
7 J. Schnackenberg 600
8 F. Pachot 500
9 Z. Hudec 400
9 G. Lambregts 400
9 N. Havard 400
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48503
Total active users1429
Act. users last 24h2
Act. users last hour0
Registered user hits last week104
Registered user hits last month599
Go up

ORA-01839: date not valid for month specified
Next thread: Can we have NON CDB databases in Oracle 19C
Prev thread: ACTIVE_SESSION_HISTORY

Message Score Author Date
Dear Guru's While selecting view ( used unpiov...... dattatraya walgude May 19, 2020, 11:49
Most likely the error comes from an implicit con...... Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts Michel Cadot May 19, 2020, 12:49
Thanks Michel Will check and update you... dattatraya walgude May 19, 2020, 13:21
Same error <pre>SELECT TO_CHAR(DATES,'DD/MM/...... dattatraya walgude May 19, 2020, 13:51
I suspect a bug in Oracle optimizer which pushes...... Michel Cadot May 19, 2020, 14:48
Thanks Michel Will check with development team.... dattatraya walgude May 19, 2020, 15:55
If you are a DBA you can create a new view by yo...... Michel Cadot May 19, 2020, 18:16
Thanks Michel.. Query execute successfully with...... dattatraya walgude May 20, 2020, 06:42
<b> Without Order by </b> <pre> Plan hash value...... dattatraya walgude May 20, 2020, 06:42
You have an ORDER BY in the view which should be...... Michel Cadot May 20, 2020, 07:20
Sir, If we comment out where clause then order ...... dattatraya walgude May 20, 2020, 07:39
<b>After removing order by from view</b> <pre...... dattatraya walgude May 20, 2020, 07:40
Tested with Release 19.0.0.0.0 , but no luck, same...... dattatraya walgude May 20, 2020, 07:43
You have to first change the view as I mentioned...... Michel Cadot May 20, 2020, 08:02
Yes Sir , view already changed Plan also share...... dattatraya walgude May 20, 2020, 09:16
Michel Sir Issue resolved. We have changed v...... dattatraya walgude May 20, 2020, 13:30
Thanks for the feedback. Regards Michel ... Michel Cadot May 20, 2020, 14:51

Follow up by mail Click here


Subject: ORA-01839: date not valid for month specified
Author: dattatraya walgude, India
Date: May 19, 2020, 11:49, 9 days ago
Os info: Linux
Oracle info: 12.1.0.1.0
Message: Dear Guru's

While selecting view ( used unpiovt) getting error

ORA-01839: date not valid for month specified

Query:
SELECT TO_CHAR(DATES,'DD/MM/YYYY') XAX, CNT YAX0, CNT YAX1

FROM (SELECT CNT,DATES FROM V_XXX_CALLS)
WHERE ((TRUNC(TO_DATE(TO_CHAR(DATES,'DD/MM/YYYY'),'DD/MM/YYYY')) = TO_DATE('18/02/2020','DD/MM/YYYY')))
GROUP BY TO_CHAR(DATES,'DD/MM/YYYY'), CNT, CNT
ORDER BY YAX0 asc;

--Note Comment order by clause query is running successfully.

View:
 CREATE OR REPLACE VIEW V_XXX_CALLS AS

SELECT "CNT","DATES"
FROM
(SELECT CNT,to_date(DATES,'DD-MM-RRRR') DATES
FROM
(SELECT NVL(CNT,0) CNT,QUANTITY DATES FROM (
SELECT COUNT(*) CNT, CHF.DTACTION
FROM XXX_XXX_FOLLOWUP CHF
WHERE CHF.SZACTIONCODE IN ('OC','IC')
AND(DTACTION BETWEEN TRUNC(GET_BUSINESSDATE('001'), 'MM') AND
GET_BUSINESSDATE('001'))
GROUP BY CHF.DTACTION) CH
FULL OUTER JOIN
(SELECT SZDATES,QUANTITY FROM (
SELECT
('01'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "01",
('02'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "02",
('03'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "03",
('04'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "04",
('05'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "05",
('06'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "06",
('07'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "07",
('08'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "08",
('09'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "09",
('10'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "10",
('11'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "11",
('12'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "12",
('13'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "13",
('14'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "14",
('15'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "15",
('16'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "16",
('17'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "17",
('18'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "18",
('19'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "19",
('20'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "20",
('21'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "21",
('22'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "22",
('23'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "23",
('24'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "24",
('25'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "25",
('26'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "26",
('27'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "27",
('28'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "28",
('29'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "29",
('30'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "30",
('31'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "31"
FROM DUAL) A
UNPIVOT(QUANTITY FOR SZDATES IN ("01","02","03","04","05","06","07","08","09","10",
"11","12","13","14","15","16","17","18","19","20",
"21","22","23","24","25","26","27","28","29","30","31"))B) DD
ON DD.QUANTITY=TO_CHAR(CH.DTACTION,'DD-MM-RRRR'))FF
WHERE FF.DATES<=TO_CHAR(GET_BUSINESSDATE('001'),'DD-MM-RRRR')
ORDER BY FF.DATES);
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01839: date not valid for month specified
Author: Michel Cadot, France
Date: May 19, 2020, 12:49, 9 days ago
Score:   Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts
Message:
Most likely the error comes from an implicit conversion in the query.

Note that:
(TRUNC(TO_DATE(TO_CHAR(DATES,'DD/MM/YYYY'),'DD/MM/YYYY'))
is just:
TRUNC(DATES)
(assuming that DATES is of DATE datatype).

And better use
GROUP BY TRUNC(DATES), ...
than
GROUP BY TO_CHAR(DATES,'DD/MM/YYYY'), ...

In the end, there should not be an ORDER BY in a view definition.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01839: date not valid for month specified
Author: dattatraya walgude, India
Date: May 19, 2020, 13:21, 9 days ago
Message: Thanks Michel

Will check and update you
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01839: date not valid for month specified
Author: dattatraya walgude, India
Date: May 19, 2020, 13:51, 9 days ago
Message:
Same error

SELECT TO_CHAR(DATES,'DD/MM/YYYY') XAX, CNT YAX0, CNT YAX1

FROM (SELECT CNT,DATES FROM V_XXX_CALLS)
WHERE TRUNC(DATES) = TO_DATE('18/02/2020','DD/MM/YYYY')
GROUP BY TO_CHAR(DATES,'DD/MM/YYYY'), CNT, CNT
ORDER BY YAX0 asc;


 SQL> desc V_XXX_CALLS;

Name Type Nullable Default Comments
----- ------ -------- ------- --------
CNT NUMBER Y
DATES DATE Y


--comment out where clause query work
 

SQL> SELECT TO_CHAR(DATES,'DD/MM/YYYY') XAX, CNT YAX0, CNT YAX1
2 FROM (SELECT CNT,DATES FROM V_XXX_CALLS)
3 --WHERE TRUNC(DATES) = TO_DATE('18/02/2020','DD/MM/YYYY')
4 GROUP BY TO_CHAR(DATES,'DD/MM/YYYY'), CNT, CNT
5 ORDER BY YAX0 asc;
XAX YAX0 YAX1
---------- ---------- ----------
01/02/2020 0 0
02/02/2020 0 0
03/02/2020 0 0
04/02/2020 0 0
05/02/2020 0 0
06/02/2020 0 0
07/02/2020 0 0
08/02/2020 0 0
09/02/2020 0 0
10/02/2020 0 0
11/02/2020 0 0
12/02/2020 0 0

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01839: date not valid for month specified
Author: Michel Cadot, France
Date: May 19, 2020, 14:48, 9 days ago
Message:
I suspect a bug in Oracle optimizer which pushes the condition in the subquery where DATES is of VARCHAR2 datatype.
Try changing the name of the column in the view for something else.

For instance, keep QUANTITY in the definition of the view until to:
to_date(DATES,'DD-MM-RRRR') DATES
which becomes
to_date(QUANTITY,'DD-MM-RRRR') DATES

If you can post a test case we can reproduce it will be easier to compare in different versions (CREATE TABLE XXX_XXX_FOLLOWUP and INSERT statements for a small set of rows and CREATE FUNCTION GET_BUSINESSDATE).

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01839: date not valid for month specified
Author: dattatraya walgude, India
Date: May 19, 2020, 15:55, 9 days ago
Message: Thanks Michel
Will check with development team.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01839: date not valid for month specified
Author: Michel Cadot, France
Date: May 19, 2020, 18:16, 9 days ago
Message:
If you are a DBA you can create a new view by yourself with what I mentioned and test with it. ;)

As I said, if you post a test case I can test your query in different Oracle versions (from 8i to 19c :)

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01839: date not valid for month specified
Author: dattatraya walgude, India
Date: May 20, 2020, 06:42, 8 days ago
Message: Thanks Michel..

Query execute successfully without order by clause,below are the explain plan for both with and without order by clause. any clue from this ?

With Order by

 Plan hash value: 3091986794


------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31 | 1364 | 65 (0)| 00:00:01 |
| 1 | SORT GROUP BY | | 31 | 1364 | 65 (0)| 00:00:01 |
|* 2 | HASH JOIN OUTER | | 31 | 1364 | 65 (0)| 00:00:01 |
|* 3 | VIEW | | 31 | 682 | 62 (0)| 00:00:01 |
| 4 | UNPIVOT | | | | | |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 6 | VIEW | | 1 | 22 | 3 (0)| 00:00:01 |
| 7 | HASH GROUP BY | | 1 | 11 | 3 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL| COL_HIS_FOLLOWUP | 1 | 11 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("unpivot_view_073"."QUANTITY"=TO_CHAR(INTERNAL_FUNCTION("CH"."DTACTI
ON"(+)),'DD-MM-RRRR'))
3 - filter("unpivot_view_073"."QUANTITY" IS NOT NULL AND
TRUNC(TO_DATE(TO_CHAR(TO_DATE("unpivot_view_073"."QUANTITY",'DD-MM-RRRR'),'DD/MM/Y
YYY'),'DD/MM/YYYY'))=TO_DATE(' 2020-02-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "unpivot_view_073"."QUANTITY"<=TO_CHAR("GET_BUSINESSDATE"('001'),'DD-MM-RRRR')
)
8 - filter(("CHF"."SZACTIONCODE"='IC' OR "CHF"."SZACTIONCODE"='OC') AND
"DTACTION"<="GET_BUSINESSDATE"('001') AND
"DTACTION">=TRUNC("GET_BUSINESSDATE"('001'),'fmmm'))


Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01839: date not valid for month specified
Author: dattatraya walgude, India
Date: May 20, 2020, 06:42, 8 days ago
Message: Without Order by

 Plan hash value: 1320637841


--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31 | 589 | 65 (0)| 00:00:01 |
| 1 | HASH GROUP BY | | 31 | 589 | 65 (0)| 00:00:01 |
| 2 | VIEW | | 31 | 589 | 65 (0)| 00:00:01 |
| 3 | SORT ORDER BY | | 31 | 1364 | 65 (0)| 00:00:01 |
|* 4 | HASH JOIN OUTER | | 31 | 1364 | 65 (0)| 00:00:01 |
|* 5 | VIEW | | 31 | 682 | 62 (0)| 00:00:01 |
| 6 | UNPIVOT | | | | | |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 8 | VIEW | | 1 | 22 | 3 (0)| 00:00:01 |
| 9 | HASH GROUP BY | | 1 | 11 | 3 (0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL| COL_HIS_FOLLOWUP | 1 | 11 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("unpivot_view_073"."QUANTITY"=TO_CHAR(INTERNAL_FUNCTION("CH"."DTACTION
"(+)),'DD-MM-RRRR'))
5 - filter("unpivot_view_073"."QUANTITY" IS NOT NULL AND
"unpivot_view_073"."QUANTITY"<=TO_CHAR("GET_BUSINESSDATE"('001'),'DD-MM-RRRR') AND
TRUNC(TO_DATE(TO_CHAR(TO_DATE("unpivot_view_073"."QUANTITY",'DD-MM-RRRR'),'DD/MM/YYY
Y'),'DD/MM/YYYY'))=TO_DATE(' 2020-02-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
10 - filter(("CHF"."SZACTIONCODE"='IC' OR "CHF"."SZACTIONCODE"='OC') AND
"DTACTION"<="GET_BUSINESSDATE"('001') AND
"DTACTION">=TRUNC("GET_BUSINESSDATE"('001'),'fmmm'))

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01839: date not valid for month specified
Author: Michel Cadot, France
Date: May 20, 2020, 07:20, 8 days ago
Message:
You have an ORDER BY in the view which should be removed.

With or without the WHERE clause?
Didn't you say the problem is there not in ORDER BY?

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01839: date not valid for month specified
Author: dattatraya walgude, India
Date: May 20, 2020, 07:39, 8 days ago
Message: Sir,

If we comment out where clause then order by works, and if we comment out order by then where clause work.

Plan when comment out where clause.

 Plan hash value: 3091986794


------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31 | 1364 | 65 (0)| 00:00:01 |
| 1 | SORT GROUP BY | | 31 | 1364 | 65 (0)| 00:00:01 |
|* 2 | HASH JOIN OUTER | | 31 | 1364 | 65 (0)| 00:00:01 |
|* 3 | VIEW | | 31 | 682 | 62 (0)| 00:00:01 |
| 4 | UNPIVOT | | | | | |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 6 | VIEW | | 1 | 22 | 3 (0)| 00:00:01 |
| 7 | HASH GROUP BY | | 1 | 11 | 3 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL| COL_HIS_FOLLOWUP | 1 | 11 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("unpivot_view_073"."QUANTITY"=TO_CHAR(INTERNAL_FUNCTION("CH"."DTACTI
ON"(+)),'DD-MM-RRRR'))
3 - filter("unpivot_view_073"."QUANTITY" IS NOT NULL AND
"unpivot_view_073"."QUANTITY"<=TO_CHAR("GET_BUSINESSDATE"('001'),'DD-MM-RRRR'))
8 - filter(("CHF"."SZACTIONCODE"='IC' OR "CHF"."SZACTIONCODE"='OC') AND
"DTACTION"<="GET_BUSINESSDATE"('001') AND
"DTACTION">=TRUNC("GET_BUSINESSDATE"('001'),'fmmm'))


Note:
If we removed order by clause from view then with where clause query not work, but work with order by.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01839: date not valid for month specified
Author: dattatraya walgude, India
Date: May 20, 2020, 07:40, 8 days ago
Message:
After removing order by from view

 Plan hash value: 3091986794


------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31 | 1364 | 65 (0)| 00:00:01 |
| 1 | SORT GROUP BY | | 31 | 1364 | 65 (0)| 00:00:01 |
|* 2 | HASH JOIN OUTER | | 31 | 1364 | 65 (0)| 00:00:01 |
|* 3 | VIEW | | 31 | 682 | 62 (0)| 00:00:01 |
| 4 | UNPIVOT | | | | | |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 6 | VIEW | | 1 | 22 | 3 (0)| 00:00:01 |
| 7 | HASH GROUP BY | | 1 | 11 | 3 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL| COL_HIS_FOLLOWUP | 1 | 11 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("unpivot_view_073"."QUANTITY"=TO_CHAR(INTERNAL_FUNCTION("CH"."DTACTI
ON"(+)),'DD-MM-RRRR'))
3 - filter("unpivot_view_073"."QUANTITY" IS NOT NULL AND
TRUNC(TO_DATE(TO_CHAR(TO_DATE("unpivot_view_073"."QUANTITY",'DD-MM-RRRR'),'DD/MM/Y
YYY'),'DD/MM/YYYY'))=TO_DATE(' 2020-02-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "unpivot_view_073"."QUANTITY"<=TO_CHAR("GET_BUSINESSDATE"('001'),'DD-MM-RRRR')
)
8 - filter(("CHF"."SZACTIONCODE"='IC' OR "CHF"."SZACTIONCODE"='OC') AND
"DTACTION"<="GET_BUSINESSDATE"('001') AND
"DTACTION">=TRUNC("GET_BUSINESSDATE"('001'),'fmmm'))

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01839: date not valid for month specified
Author: dattatraya walgude, India
Date: May 20, 2020, 07:43, 8 days ago
Message: Tested with Release 19.0.0.0.0 , but no luck, same issue.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01839: date not valid for month specified
Author: Michel Cadot, France
Date: May 20, 2020, 08:02, 8 days ago
Message:
You have to first change the view as I mentioned then test.
There we don't know to what is related these plans.


Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01839: date not valid for month specified
Author: dattatraya walgude, India
Date: May 20, 2020, 09:16, 8 days ago
Message: Yes Sir , view already changed

Plan also shared.( After removing order by from view)
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01839: date not valid for month specified
Author: dattatraya walgude, India
Date: May 20, 2020, 13:30, 8 days ago
Message: Michel Sir

Issue resolved.

We have changed view just add hint "/*+NO_MERGE */" and query executed successfully.

 CREATE OR REPLACE VIEW V_XXX_CALLS AS

SELECT /*+NO_MERGE */ "CNT","DATES"
FROM
(SELECT CNT,to_date(DATES,'DD-MM-RRRR') DATES
FROM
(SELECT NVL(CNT,0) CNT,QUANTITY DATES FROM (
SELECT COUNT(*) CNT, CHF.DTACTION
FROM COL_HIS_FOLLOWUP CHF
WHERE CHF.SZACTIONCODE IN ('OC','IC')
AND(DTACTION BETWEEN TRUNC(GET_BUSINESSDATE('001'), 'MM') AND
GET_BUSINESSDATE('001'))
GROUP BY CHF.DTACTION) CH
FULL OUTER JOIN
(SELECT SZDATES,QUANTITY FROM (
SELECT
('01'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "01",
('02'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "02",
('03'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "03",
('04'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "04",
('05'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "05",
('06'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "06",
('07'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "07",
('08'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "08",
('09'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "09",
('10'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "10",
('11'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "11",
('12'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "12",
('13'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "13",
('14'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "14",
('15'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "15",
('16'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "16",
('17'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "17",
('18'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "18",
('19'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "19",
('20'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "20",
('21'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "21",
('22'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "22",
('23'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "23",
('24'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "24",
('25'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "25",
('26'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "26",
('27'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "27",
('28'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "28",
('29'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "29",
('30'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "30",
('31'||'-'||TO_CHAR(GET_BUSINESSDATE('001'),'MM-RRRR')) "31"
FROM DUAL) A
UNPIVOT(QUANTITY FOR SZDATES IN ("01","02","03","04","05","06","07","08","09","10",
"11","12","13","14","15","16","17","18","19","20",
"21","22","23","24","25","26","27","28","29","30","31"))B) DD
ON DD.QUANTITY=TO_CHAR(CH.DTACTION,'DD-MM-RRRR'))FF
WHERE FF.DATES<=TO_CHAR(GET_BUSINESSDATE('001'),'DD-MM-RRRR')
ORDER BY FF.DATES);


Cause:
When we combine a GROUP BY and an ORDER BY on the same column list, Oracle will not use the HASH GROUP BY option.

Solution:
To get a better result, you can perform the GROUP BY in an in-line view and perform the ORDER BY in the outer query. Use the NO_MERGE hint to prevent the two operations from being combined.


reference from: http://guyharrison.squarespace.com/blog/2009/8/5/optimizing-group-and-order-by.html
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01839: date not valid for month specified
Author: Michel Cadot, France
Date: May 20, 2020, 14:51, 8 days ago
Message:
Thanks for the feedback.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here