Home » SQL & PL/SQL » SQL & PL/SQL » Porblem with Procedure...
Porblem with Procedure... [message #412992] Mon, 13 July 2009 21:04 Go to next message
preethi 12$
Messages: 9
Registered: January 2008
Junior Member

I have a table having following values:

Issue_cd Indicator New_Bus_eff_dt Rnw_eff_Dt
N Initial 2/27/2004 3/28/2004
N No Change 8/31/2004 9/30/2004

From this table I need to move this data after some formatting into another table.

Data in New table should have the foll values.
If Issue_cd='N' and second record has indicator as 'No Change',
First_eff_dt will have the value of New_bus_eff_dt.

Issue_cd first_eff_dt last_eff_Dt
N 2/27/2004 31-dec-9999

With the attached code I am able to achieve this.

2) Now my new requirement is

First table:
Issue_cd Indicator New_Bus_eff_dt Rnw_eff_Dt
R Initial 2/27/2004 3/28/2004
R Change 8/31/2004 9/30/2004
R No Change 10/30/2004 11/30/2005

Second table should be populated as follows:
If issue_cd='R', Rnw_eff_dt is used to populate first_eff_dt of Ist row. If Indicator is 'Change', Last_eff_dt of I record =Rnw_eff_dt of last record -1.

As in the third record, indicator is No change, Frst_Eff_dt is same as 3rd record Rnw_eff_dt and last_eff_dt is 31-dec-9999.

Issue_cd first_eff_dt last_eff_Dt
R 3/28/2004 11/29/2005
R 10/30/2004 12/31/9999

Can you please help me how to modify the attached code to achieve the second requirement?

  • Attachment: Forum.sql
    (Size: 4.43KB, Downloaded 98 times)
Re: Porblem with Procedure... [message #412993 is a reply to message #412992] Mon, 13 July 2009 21:07 Go to previous messageGo to next message
Messages: 25578
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Porblem with Procedure... [message #413005 is a reply to message #412992] Mon, 13 July 2009 23:53 Go to previous message
Michel Cadot
Messages: 65150
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Thu, 09 July 2009 09:54
From your previous topic:
Michel Cadot wrote on Wed, 08 July 2009 14:41
Michel Cadot wrote on Wed, 08 July 2009 09:47
Michel Cadot wrote on Tue, 07 July 2009 09:29
Are you sure you can't do it in a simple SQL statement?

Post a working Test case: create table and insert statements along with the result you want with these data.
Use SQL*Plus and copy and paste what you already tried.

Also always post your Oracle version (4 decimals).

Before posting any code, 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 code tags.
Use the "Preview Message" button to verify.


Previous Topic: Parametrize a date (merged 4)
Next Topic: For loop taking more than 24 hours to complete (merged 4)
Goto Forum:

Current Time: Mon Aug 21 22:22:35 CDT 2017

Total time taken to generate the page: 0.04999 seconds