Home » SQL & PL/SQL » SQL & PL/SQL » How to Split a date (Merged)
How to Split a date (Merged) [message #353525] Tue, 14 October 2008 02:05 Go to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Hello,
I have got a problem and tried with known all the function to split a date into the following format:

1. Say i have a date format like 8/31/2008.
2. In variable X i would like to keep the value of month so in this case X will contain 8 and in variable Y i would like to keep the value of the year,Y=2008.

Please help me with providing a sample code for this. Thank you for your kind consideration.
Re: How to Split a date [message #353527 is a reply to message #353525] Tue, 14 October 2008 02:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What did you try so far?
Show us and tell us where you are stuck. It will teach you so much more trying yourself then us telling you what to do.
Hint: Check the docs for to_char.
Re: How to Split a date [message #353534 is a reply to message #353525] Tue, 14 October 2008 02:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at TO_CHAR and follow the links.

Regards
Michel
Re: How to Split a date [message #353566 is a reply to message #353534] Tue, 14 October 2008 03:33 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Thank you, for such a quick reply, how ever i have successfully seperated the month and year from a date string and the reason i was doing it for a trigger. The trigger is as follows:


CREATE OR REPLACE TRIGGER Do_Dcmbl.Achievement_Tr AFTER
INSERT
ON Master_Sales_Tbl
FOR EACH ROW

DECLARE
DISTRI_CODE VARCHAR2(4);
MONTH_ID NUMBER(2);
YEAR NUMBER(4);

BEGIN
SELECT DISTRIBUTOR_CODE INTO DISTRI_CODE FROM MASTER_SALES_TBL;
select to_number( to_char( to_date(Do_Date,'dd-mm-yy'),'yyyy' ) ) INTO YEAR from master_sales_tbl;
select to_number( to_char( to_date(Do_Date,'dd-mm-yy'),'MM' ) ) INTO MONTH_ID from master_sales_tbl;
END;

BEGIN
IF INSERTING THEN
UPDATE Do_Dcmbl.Dealer_Achievement_Tbl
SET Achieved_Qty_Monthly = nvl(Achieved_Qty_Monthly,0) + :NEW.product_quantity
SET Achieved_Amt_Monthly = nvl(Achieved_Amt_Monthly,0) + :NEW.Sales_amount
WHERE Dealer_Achievement_Tbl.product_Code = :NEW.product_Code AND
Dealer_Achievement_Tbl.DISTRIBUTOR_CODE=:NEW.DISTRI_CODE AND
Dealer_Achievement_Tbl.MONTH_ID=:NEW.MONTH_ID AND
Dealer_Achievement_Tbl.TARGET_YEAR=:NEW.YEAR;



END IF;


END;


But it shows a error:Warning: Trigger created with compilation errors.
What to do now? Any Ideas?
Re: How to Split a date [message #353569 is a reply to message #353566] Tue, 14 October 2008 03:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
type "show errors"
Re: How to Split a date [message #353572 is a reply to message #353569] Tue, 14 October 2008 03:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Is do_date not already a date? If so, then why do you do a to_date on it?

SELECT DISTRIBUTOR_CODE 
INTO   DISTRI_CODE 
FROM   MASTER_SALES_TBL;

Don't you need a where clause here?
Can't you get the value from the :NEW record? It will prevent a mutating table problem.
Same for the other two selects.
Don't select from the triggering table in a row trigger.
Re: How to Split a date [message #353573 is a reply to message #353566] Tue, 14 October 2008 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: How to Split a date [message #353593 is a reply to message #353525] Tue, 14 October 2008 05:10 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Thanks for the advice, i will work on that and let you know my progress. Thank you once again.
Month and Year Problem [message #353778 is a reply to message #353525] Wed, 15 October 2008 01:50 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Hello, thanks for the lat time help now i am having a new problem and need your kind concern about it. i have created a trigger which will cut the month (In number) and Year from the date string like as follows:

Example: 12/12/2008 to Month_Id=12 and Year=2008 and here is my trigger code to do that:

create or replace trigger XYZ1
after insert ON master_SALES_TBL FOR EACH ROW

DECLARE
YEA NUMBER(4);
mon_id NUMBER(2);

begin
select to_number( to_char( to_date(Do_Date,'dd-mm-yy'),'yyyy')) INTO YEA from master_sales_tbl ;
select to_number( to_char( to_date(Do_Date,'dd-mm-yy'),'mm')) INTO mon_id from master_sales_tbl;

update MASTER_SALES_TBL
SET master_sales_tbl.YEAR =YEA, MASTER_SALES_TBL.MONTH_ID=MON_ID
where master_sales_tbl.Do_No=(Select Max(Do_no) from master_sales_tbl);
END;


So, after inserting into Master_sales_tbl from the form i am getting a error ORA-04091 error, but can't find it. So i need your help.
Re: Month and Year Problem [message #353780 is a reply to message #353778] Wed, 15 October 2008 01:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This isn't a new problem at all, so I merged the two threads.

Reread my last reply, it explains it all.
Re: Month and Year Problem [message #353782 is a reply to message #353778] Wed, 15 October 2008 01:56 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Quote:

Error:
ORA-04091: table name is mutating, trigger/function may not see it
Cause:
A statement executed a trigger or custom PL/SQL function. That trigger/function tried to modify or query a table that is currently being modified by the statement that fired the trigger/function.
Action:
The options to resolve this Oracle error are:
Re-write the trigger/function so that it does not try to modify/query the table in question.


Error is because you are modifying the same table on which trigger is written.
Use temporary table/ pass values in the after statement trigger and use update statemnent in the after statement trigger.
update MASTER_SALES_TBL
SET master_sales_tbl.YEAR =YEA, MASTER_SALES_TBL.MONTH_ID=MON_ID
where master_sales_tbl.Do_No=(Select Max(Do_no) from master_sales_tbl);


[Updated on: Thu, 16 October 2008 03:20] by Moderator

Report message to a moderator

Re: How to Split a date (Merged) [message #353804 is a reply to message #353525] Wed, 15 October 2008 03:03 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Thank you all, according to your suggestion i have changed the table name and yes do_date is a date string having the format 12/12/2008. now i have already a table name master_sales_tbl where every field is already posted expect Month_id and Year, which i would like to get using this trigger. Any more hints or help please
Re: How to Split a date (Merged) [message #353823 is a reply to message #353525] Wed, 15 October 2008 04:46 Go to previous messageGo to next message
shettypravs
Messages: 9
Registered: August 2008
Junior Member
I believe mutating error issue can also be resolved using an autonomous transaction
Re: How to Split a date (Merged) [message #353826 is a reply to message #353823] Wed, 15 October 2008 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
shettypravs wrote on Wed, 15 October 2008 11:46
I believe mutating error issue can also be resolved using an autonomous transaction

I believe you are digging your grave if you do this.
Do you think that Oracle returns an error just its pleasure?
Do you REALLY understand what this error means?

Regards
Michel

Re: How to Split a date (Merged) [message #353829 is a reply to message #353525] Wed, 15 October 2008 04:57 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
As far as i know Autonomous transaction will works if SELECT query on the table on which trigger is written.

And if DML operation will be performed then Autonomous transaction wont help.

Thanks,
Re: How to Split a date (Merged) [message #353833 is a reply to message #353525] Wed, 15 October 2008 05:05 Go to previous messageGo to next message
shettypravs
Messages: 9
Registered: August 2008
Junior Member
FYI...we did use autonomous txns when we encountered a mutating table eror. This data conversion task was to update one column with the converted value of the other field in the db. It worked fine and our one time task got completed successfully.
Re: How to Split a date (Merged) [message #353840 is a reply to message #353525] Wed, 15 October 2008 05:21 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

As i have overcome the mutating problem with your help now please consider the problem for the last time and that is after creating the following trigger i have got the following message:

SQL> SHOW ERRORS
Errors for TRIGGER DO_DCMBL.ACHIEVEMENT_TR:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/5 PL/SQL: SQL Statement ignored
4/64 PLS-00327: "MASTER_SALES_TBL" is not in SQL scope here
SQL>

and the Trigger is :

SQL> CREATE OR REPLACE TRIGGER Do_Dcmbl.Achievement_Tr AFTER
2 INSERT
3 ON Master_Sales_Tbl
4 FOR EACH ROW

5 BEGIN
6 IF INSERTING THEN
7 UPDATE Do_Dcmbl.Dealer_Achievement_Tbl
8 SET Achieved_Qty_Monthly = nvl(Achieved_Qty_Monthly,0) + master_sales_tbl.product_quantity, Achieved_Amt_Monthly = nvl(Achieved_Amt_Monthly,0) + master_sales_tbl.Sales_amount

9 WHERE Dealer_Achievement_Tbl.product_Code = master_sales_tbl.product_Code AND
10 Dealer_Achievement_Tbl.DISTRIBUTOR_CODE=MASTER_SALES_TBL.DISTRI_CODE AND
11 Dealer_Achievement_Tbl.MONTH_ID=MASTER_SALES_TBL.MON_ID AND
12 Dealer_Achievement_Tbl.TARGET_YEAR=MASTER_SALES_TBL.YEAR;
13
14 END IF;
15
16
17 END;
18 /
I know what does it mean by this type of errors but as i am working on master_sales_tbl table yet why the errors is showing not in the SQL Scope? Need Help from you.
Re: How to Split a date (Merged) [message #353853 is a reply to message #353840] Wed, 15 October 2008 06:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 14 October 2008 11:03
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Use SQL*Plus and copy and paste your session.

Regards
Michel


Re: How to Split a date (Merged) [message #354027 is a reply to message #353525] Thu, 16 October 2008 02:57 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Ok as you said i have change the Formatting. Now please someone help me out about it:

CREATE OR REPLACE TRIGGER Do_Dcmbl.Achievement_Tr AFTER 
    INSERT 
    ON Master_Sales_Tbl 
    FOR EACH ROW 
BEGIN
select 
  IF INSERTING THEN
    UPDATE Do_Dcmbl.Dealer_Achievement_Tbl
     SET Achieved_Qty_Monthly =  nvl(Achieved_Qty_Monthly,0) + master_sales_tbl.product_quantity, 
Achieved_Amt_Monthly =  nvl(Achieved_Amt_Monthly,0) + master_sales_tbl.Sales_amount
      WHERE  Dealer_Achievement_Tbl.product_Code = master_sales_tbl.product_Code AND
	Dealer_Achievement_Tbl.DISTRIBUTOR_CODE=MASTER_SALES_TBL.DISTRI_CODE AND
	Dealer_Achievement_Tbl.MONTH_ID=MASTER_SALES_TBL.MON_ID AND
	Dealer_Achievement_Tbl.TARGET_YEAR=MASTER_SALES_TBL.YEAR;

  END IF;


END;


SQL> SHOW ERRORS
Errors for TRIGGER DO_DCMBL.ACHIEVEMENT_TR:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/5 PL/SQL: SQL Statement ignored
4/64 PLS-00327: "MASTER_SALES_TBL" is not in SQL scope here
SO now what to do?

[Updated on: Thu, 16 October 2008 03:21] by Moderator

Report message to a moderator

Re: How to Split a date (Merged) [message #354029 is a reply to message #354027] Thu, 16 October 2008 02:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can't just refer to master_sales_tbl.product_quantity in the trigger - in an Insert trigger it would need to be :new.product_quantity.
Re: How to Split a date (Merged) [message #354035 is a reply to message #353525] Thu, 16 October 2008 03:15 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Thanks for the reply and after change the trigger i have got the following errors message. Now i am wondering where i have made the mistake and as time is running out i am truly need your help to solved it out.

Quote:
SQL> SHOW ERRORS
Errors for TRIGGER DO_DCMBL.ACHIEVEMENT_TR:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3 PLS-00103: Encountered the symbol "IF" when expecting one of the
following:
( * - + all mod null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
table avg count current distinct max min prior sql stddev sum
unique variance execute the forall time timestamp interval
date <a string literal with character set specification>
<a number> <a single-quoted SQL string>

15/0 PLS-00103: Encountered the symbol "end-of-file" when expecting
one of the following:
begin function package pragma procedure form
Re: How to Split a date (Merged) [message #354037 is a reply to message #354035] Thu, 16 October 2008 03:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, you haven't shown us the code that generates these error, so I'll take a guess based on yuor previous posted code: It's that SELECT that youve got on a line by itself after the initial BEGIN in the trigger body.

Re: How to Split a date (Merged) [message #354039 is a reply to message #353525] Thu, 16 October 2008 03:22 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Here is the code that i have modified,Please have a look and reply to me. Thank you very much:
CREATE OR REPLACE TRIGGER Do_Dcmbl.Achievement_Tr AFTER 
    INSERT 
    ON Master_Sales_Tbl 
    FOR EACH ROW 
BEGIN
select 
  IF INSERTING THEN
    UPDATE Do_Dcmbl.Dealer_Achievement_Tbl
     SET Achieved_Qty_Monthly =  
nvl(Achieved_Qty_Monthly,0) + master_sales_tbl.product_quantity,
 Achieved_Amt_Monthly = 
 nvl(Achieved_Amt_Monthly,0) + master_sales_tbl.Sales_amount
      WHERE  Dealer_Achievement_Tbl.product_Code =:New.product_Code AND
	Dealer_Achievement_Tbl.DISTRIBUTOR_CODE=:New.DISTRIBUTOR_CODE AND
	Dealer_Achievement_Tbl.MONTH_ID=:New.MONTH_ID AND
	Dealer_Achievement_Tbl.TARGET_YEAR=:New.YEAR;

  END IF;


END;

[Updated on: Thu, 16 October 2008 03:48] by Moderator

Report message to a moderator

Re: How to Split a date (Merged) [message #354042 is a reply to message #353525] Thu, 16 October 2008 03:39 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well JRowbottom has already told you want you've done wrong, but I'm curious.
What exactly do you think that select keyword is going to do?

[Updated on: Thu, 16 October 2008 03:41]

Report message to a moderator

Re: How to Split a date (Merged) [message #354043 is a reply to message #353525] Thu, 16 October 2008 03:44 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Sorry for the stupid mistake i have made, but yet after removing the SELECT keyword from the code i am having another problem, where is shows that:
Quote:
PLS-00327: "MASTER_SALES_TBL" is not in SQL scope here


Now i am one the last stage of solving my problem, so please help.
Re: How to Split a date (Merged) [message #354046 is a reply to message #354039] Thu, 16 October 2008 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Keep your lines in 80 characters.

Regards
Michel
Re: How to Split a date (Merged) [message #354047 is a reply to message #354043] Thu, 16 October 2008 03:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, if I were you, I'dtake a long look at my code, and ask myself if I'd replaced all of the references to MASTER_SALES_TBL.<column_name> with :new.<column_name>.

I might particularly concentrate on the SET line in the Update statement
Re: How to Split a date (Merged) [message #354048 is a reply to message #353525] Thu, 16 October 2008 04:01 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Don't know how but thanks for the advice you people gave me spending valuable time. I am really appreciate all the efforts made for me. Here is the complete code for the trigger:
CREATE OR REPLACE TRIGGER Do_Dcmbl.Achievement_Tr AFTER 
    INSERT 
    ON Master_Sales_Tbl 
    FOR EACH ROW 
BEGIN
  IF INSERTING THEN
    UPDATE Do_Dcmbl.Dealer_Achievement_Tbl
     SET Achieved_Qty_Monthly =  
nvl(Achieved_Qty_Monthly,0) + :New.product_quantity, 
Achieved_Amt_Monthly =  
nvl(Achieved_Amt_Monthly,0) + :New.Sales_amount
      WHERE  Dealer_Achievement_Tbl.product_Code =:New.product_Code AND
	Dealer_Achievement_Tbl.DISTRIBUTOR_ID=:New.DISTRIBUTOR_CODE AND
	Dealer_Achievement_Tbl.MONTH_ID=:New.MONTH_ID AND
	Dealer_Achievement_Tbl.TARGET_YEAR=:New.YEAR;

  END IF;

END;

Its done without any error message and may may be that is how people should learn.

[Updated on: Thu, 16 October 2008 05:15] by Moderator

Report message to a moderator

Re: How to Split a date (Merged) [message #354060 is a reply to message #354048] Thu, 16 October 2008 05:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You don't need the IF INSERTING check - your trigger only fires on Insert.

You only need the IF INSERTING/UPDATING/DELETING checks if your trigger is fired by multiple types of DML
Re: How to Split a date (Merged) [message #354147 is a reply to message #354048] Thu, 16 October 2008 10:32 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
mahatab wrote on Thu, 16 October 2008 11:01
Don't know how but thanks for the advice you people gave me spending valuable time. I am really appreciate all the efforts made for me.
Its done without any error message and may may be that is how people should learn.


Now if you would read the replies you get a bit more carefully, you would have had your answer a lot earlier. (reread this reply)

[Updated on: Thu, 16 October 2008 10:34]

Report message to a moderator

Previous Topic: Record to column display ...
Next Topic: Ref Cursors - Query
Goto Forum:
  


Current Time: Thu Dec 08 20:10:17 CST 2016

Total time taken to generate the page: 0.10994 seconds