Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01847: day of month must be between 1 and last day of month error (oracle 10.1.2)
ORA-01847: day of month must be between 1 and last day of month error [message #420576] Mon, 31 August 2009 10:37 Go to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Hi All
When i run the following code i'm getting the error:ORA-01847: day of month must be between 1 and last day of month error

SELECT t2.owner_cat, 
t1.owner_id, 
stock_id, 
Sum(received_amount) received_amount, 
Sum(curr_amount) curr_amount, 
Sum(used_amount) used_amount, 
To_char(Add_months(date_work,6),'YYYY') date_work 
FROM (SELECT owner_id_to owner_id, 
stock_id_to stock_id, 
Sum(full_amount) received_amount, 
0 curr_amount, 
0 used_amount, 
To_char(0) date_work 
FROM dw_wms_mat_transfer 
WHERE master_code_id IN ('8664','8665') 
GROUP BY owner_id_to, 
stock_id_to 
UNION ALL 
SELECT owner_id, 
stock_id, 
0 received_amount, 
Sum(curr_amount) curr_amount, 
0 used_amount, 
To_char(0) date_work 
FROM dw_wms_mat_inv 
WHERE master_code_id IN ('8664','8665') 
GROUP BY owner_id, 
stock_id 
UNION ALL 
SELECT t2.owner_id, 
t1.stock_id, 
0 received_amt, 
0 curr_amt, 
Sum(amount) used_amt, 
To_char(Add_months(date_work,6),'YYYY') date_work 
FROM dw_wms_wo_mat_dc t1, 
(SELECT owner_id, 
stock_id 
FROM dw_wms_mat_inv 
WHERE master_code_id IN ('8664','8665') 
GROUP BY owner_id, 
stock_id) t2 
WHERE t1.stock_id = t2.stock_id 
GROUP BY t2.owner_id, 
t1.stock_id, 
To_char(Add_months(date_work,6),'YYYY')) t1, 
dw_wms_setup_owner t2 
WHERE t1.owner_id = t2.owner_id 
GROUP BY t2.owner_cat, 
t1.owner_id, 
stock_id, 
To_char(Add_months(date_work,6),'YYYY') 

But when i just run the inner query:
(
SELECT owner_id_to owner_id, 
stock_id_to stock_id, 
Sum(full_amount) received_amount, 
0 curr_amount, 
0 used_amount, 
To_char(0) date_work 
FROM dw_wms_mat_transfer 
WHERE master_code_id IN ('8664','8665') 
GROUP BY owner_id_to, 
stock_id_to 
UNION ALL 
SELECT owner_id, 
stock_id, 
0 received_amount, 
Sum(curr_amount) curr_amount, 
0 used_amount, 
To_char(0) date_work 
FROM dw_wms_mat_inv 
WHERE master_code_id IN ('8664','8665') 
GROUP BY owner_id, 
stock_id 
UNION ALL 
SELECT t2.owner_id, 
t1.stock_id, 
0 received_amt, 
0 curr_amt, 
Sum(amount) used_amt, 
To_char(Add_months(date_work,6),'YYYY') date_work 
FROM dw_wms_wo_mat_dc t1, 
(SELECT owner_id, 
stock_id 
FROM dw_wms_mat_inv 
WHERE master_code_id IN ('8664','8665') 
GROUP BY owner_id, 
stock_id) t2 
WHERE t1.stock_id = t2.stock_id 
GROUP BY t2.owner_id, 
t1.stock_id, 
To_char(Add_months(date_work,6),'YYYY')) 

I'm not getting that error, any help please?
Re: ORA-01847: day of month must be between 1 and last day of month error [message #420577 is a reply to message #420576] Mon, 31 August 2009 10:41 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

We have no means to replicate problem or develop solution.
No assistance is possible without you providing Test Case.
Re: ORA-01847: day of month must be between 1 and last day of month error [message #420579 is a reply to message #420576] Mon, 31 August 2009 10:49 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
What do you want to achieve from "To_char(0) date_work" and "To_char(Add_months(date_work,6),'YYYY') date_work".

By
Vamsi
Re: ORA-01847: day of month must be between 1 and last day of month error [message #420580 is a reply to message #420576] Mon, 31 August 2009 10:55 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
SELECT   t2.owner_cat, 
         t1.owner_id, 
         stock_id, 
         Sum(received_amount)                    received_amount, 
         Sum(curr_amount)                        curr_amount, 
         Sum(used_amount)                        used_amount, 
         To_char(Add_months(date_work,6),'YYYY') date_work 
FROM     (SELECT   owner_id_to      owner_id, 
                   stock_id_to      stock_id, 
                   Sum(full_amount) received_amount, 
                   0                curr_amount, 
                   0                used_amount, 
                   To_char(0)       date_work 
          FROM     dw_wms_mat_transfer 
          WHERE    master_code_id IN ('8664','8665') 
          GROUP BY owner_id_to, 
                   stock_id_to 
          UNION ALL 
          SELECT   owner_id, 
                   stock_id, 
                   0                received_amount, 
                   Sum(curr_amount) curr_amount, 
                   0                used_amount, 
                   To_char(0)       date_work 
          FROM     dw_wms_mat_inv 
          WHERE    master_code_id IN ('8664','8665') 
          GROUP BY owner_id, 
                   stock_id 
          UNION ALL 
          SELECT   t2.owner_id, 
                   t1.stock_id, 
                   0                                       received_amt, 
                   0                                       curr_amt, 
                   Sum(amount)                             used_amt, 
                   To_char(Add_months(date_work,6),'YYYY') date_work 
          FROM     dw_wms_wo_mat_dc t1, 
                   (SELECT   owner_id, 
                             stock_id 
                    FROM     dw_wms_mat_inv 
                    WHERE    master_code_id IN ('8664','8665') 
                    GROUP BY owner_id, 
                             stock_id) t2 
          WHERE    t1.stock_id = t2.stock_id 
          GROUP BY t2.owner_id, 
                   t1.stock_id, 
                   To_char(Add_months(date_work,6),'YYYY')) t1, 
         dw_wms_setup_owner t2 
WHERE    t1.owner_id = t2.owner_id 
GROUP BY t2.owner_cat, 
         t1.owner_id, 
         stock_id, 
         To_char(Add_months(date_work,6),'YYYY')


PLEASE follow Posting Guidelines!
Re: ORA-01847: day of month must be between 1 and last day of month error [message #420591 is a reply to message #420580] Mon, 31 August 2009 12:24 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
BlackSwan wrote on Mon, 31 August 2009 11:55

PLEASE follow Posting Guidelines!


Are you kidding? Do you really expect this person be able to post formatted code or a line number or any helpful information? Surely you jest.
Re: ORA-01847: day of month must be between 1 and last day of month error [message #420678 is a reply to message #420591] Tue, 01 September 2009 08:05 Go to previous message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Sorry all
I got the answer, i removed to_char(add_months(t1.DATE_WORK,6),'YYYY') date_work
from the main select statement and just used date_work.

Thanks All
Previous Topic: problem with DBMS_DATAPUMP
Next Topic: SQL Query
Goto Forum:
  


Current Time: Fri Dec 09 06:14:42 CST 2016

Total time taken to generate the page: 0.05317 seconds