• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

Adding rows


extending out dates
 

Hello

I have a sql data set where some of the data needs extending based on what is in output.
Sample data as follows.........

Cpy                         Period         Repeats       Date                    Value
A001-20110130       Monthly          3              01/02/2011         100
A001-20110330       Null                0              01/04/2010           45
A004-20110130       Quarterley      2              10/02/2011         234
A005-20110130       Null                0              01/06/2011         100
etc
so, based on above data I need to add rows based on "Period & Repeats" columns having values.
Row1 will now have 3 more rows added and the only thing that will change is the date column.
Row 3 will have 2 more rows added and again only the date value will change. The idea is to extend out the dates based on columns "Period & Repeats." Now, my new data set based on the above rules should read as follows....

Cpy                         Period         Repeats       Date                    Value
A001-20110130       Monthly          3              01/02/2011         100
A001-20110130       Monthly                          01/03/2011         100
A001-20110130       Monthly                          01/04/2011         100
A001-20110130       Monthly                          01/05/2011         100
A001-20110330       Null                0              01/04/2010           45
A004-20110130       Quarterley      2              10/02/2011         234
A004-20110130       Quarterley                      10/05/2011         234
A004-20110130       Quarterley                      10/08/2011         234
A005-20110130       Null                0              01/06/2011         100

Hope this makes sense and is it possible?

Regards
0
philsivyer
Asked:
philsivyer
  • 6
  • 5
  • 4
  • +2
3 Solutions
 
Evan CutlerCommented:
what is your programming model? ASP?  PHP?
or are you asking to do this in ORACLE developer?
0
 
gajmpCommented:
if you want to do in pl/sql then check this code
declare
   v_temp number;
begin
    for i in (select Cpy, Period, Repeats, Date, Value from t)
    Loop
                if i.repeats is not null and i.period is not null then
                       v_temp := case i.period when 'Monthly' then 1 when 'Quarterly' then 3 when 'Yearly' then 12 else 0 end;
                       for j in 1..i.repeats Loop                            
                             insert into t values (i.cpy, i.period, null, add_months(i.date, v_temp*j), i.value);
                       end loop;
                end if;
     end loop;
end;
0
 
philsivyerAuthor Commented:
Oracle Please
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Evan CutlerCommented:
I have your script...is your repeats always Daily, Monthly, Weekly, and Quarterly?
0
 
philsivyerAuthor Commented:
Yes - if field is null in "period" column then leave as is.
0
 
slightwv (䄆 Netminder) Commented:
See if you can get where you need using pipelined functions.

There might be easier ways but it is the first thing I thought of.
drop table tab1 purge;

create table tab1(col1 char(1), col2 number);

insert into tab1 values('a',2);
insert into tab1 values('b',3);
commit;

CREATE OR REPLACE TYPE numtab_type IS TABLE OF NUMBER;
/

CREATE or replace FUNCTION rowgen(pin_nor IN NUMBER)
RETURN numtab_type DETERMINISTIC PIPELINED
AS
BEGIN
  FOR i IN 1..pin_nor
  LOOP
    PIPE ROW(i);
  END LOOP;
  
  RETURN;
END rowgen;
/

show errors


select col1, col2 from tab1, table(rowgen(col2))
/

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
oops... for got to add code for the '0' repeats and the link where I got most of the code:

http://www.orafaq.com/wiki/Oracle_Row_Generator_Techniques#PL.2FSQL_table_or_pipelined_function

Try the below code.
drop table tab1 purge;

create table tab1(col1 char(1), col2 number);

insert into tab1 values('a',2);
insert into tab1 values('b',3);
insert into tab1 values('c',0);
commit;

CREATE OR REPLACE TYPE numtab_type IS TABLE OF NUMBER;
/

CREATE or replace FUNCTION rowgen(pin_nor IN NUMBER)
RETURN numtab_type DETERMINISTIC PIPELINED
AS
BEGIN
if pin_nor > 0 then
  FOR i IN 1..pin_nor
  LOOP
    PIPE ROW(i);
  END LOOP;
else
	pipe row(pin_nor);
end if;
  
  RETURN;
END rowgen;
/

show errors


select col1, col2 from tab1, table(rowgen(col2))
/

Open in new window

0
 
Evan CutlerCommented:
Ok,
Here you go...
now, I did this in notepad, out of memory...so please forgive if I forgot a semi-colon somewhere.

I think a global temp table would work here, reading from your original table and inserting the added rows only (original rows not needed here), then taking the whole table and adding it to your original table.

I wrote this as a procedure.
Hope it will help.
Create OR Replace Procedure addrows

is

Create Global Temporary Table addedrows  --copy of your table
(

Cpy varchar(20),
Period varchar(20),
Repeats Number,
Date Date,
value Number

)

cursor inputrows is select * from <your table>;

Begin

	For Cpy_rec in inputrows
	LOOP	
		if (Repeats IS NOT NULL || If Repeats > 0) AND Period IS NOT NULL
		CASE Period
			When 'Daily' THEN
				FOR InsertCTR IN 1..Repeats
				LOOP
     					Insert Into addedrows (Cpy, Period, Repeats, Date, Value) Values (Cpy, Period, NULL, Date + InsertCTR, Value)

				END LOOP;
			When 'Weekly' THEN
				FOR InsertCTR IN 1..Repeats
				LOOP
     					Insert Into addedrows (Cpy, Period, Repeats, Date, Value) Values (Cpy, Period, NULL, Date + (InsertCTR*7), Value)

				END LOOP;
			When 'Monthly' THEN

				FOR InsertCTR IN 1..Repeats
				LOOP
     					Insert Into addedrows (Cpy, Period, Repeats, Date, Value) Values (Cpy, Period, NULL, Add_Months(Date, InsertCTR), Value)

				END LOOP;
			When 'Quarterly' THEN

				FOR InsertCTR IN 1..Repeats
				LOOP
     					Insert Into addedrows (Cpy, Period, Repeats, Date, Value) Values (Cpy, Period, NULL, Add_Months(Date, (InsertCTR*3)), Value)

				END LOOP;

			When 'Yearly' THEN
				FOR InsertCTR IN 1..Repeats
				LOOP
     					Insert Into addedrows (Cpy, Period, Repeats, Date, Value) Values (Cpy, Period, NULL, Add_Months(Date, (InsertCTR*12)), Value)

				END LOOP;

		END
	END LOOP;


	Insert into <YOUR TABLE> (Cpy, Period, Repeats, Date, Value) Values (SELECT Cpy, Period, NULL, Date, Value FROM addedrows);
	Delete from addedrows;
	DROP Table addedrows;
	commit;


end;

Open in new window

0
 
awking00Commented:
Can you please list all of the values that can be contained by the period field?
0
 
philsivyerAuthor Commented:
Just Monthly and Quarterly
0
 
Evan CutlerCommented:
Ok, that's why I gave you a case statement to use.  You can add and take away anything you want.  Also, you can add an else statement if desired.

Cheers
0
 
awking00Commented:
See attached.
comment.txt
0
 
philsivyerAuthor Commented:
acree

Trying out Global Temp Table and getting the following error........

Found 'Temporary', Expecting:; :=DEFAULT NOT NULL -or-% -or- (,@

Regards
0
 
slightwv (䄆 Netminder) Commented:
You cannot create a global temporary table like that inside a procedure.  They are physical objects and should be created once outside the procedure then used globally.

Only the session that inserts into them can see the data and it is automatically removed when the session ends (or a commit depending on how you create them).
0
 
Evan CutlerCommented:

ok....
looking at this, we can simplify ORACLE's processing requirements and try this again...

Take this out and run it.Create Global Temporary Table addedrows  --copy of your table
(

Cpy varchar(20),
Period varchar(20),
Repeats Number,
Date Date,
value Number

) ON COMMIT DELETE ROWS;

What this does, is create an actual table in your table library (if your going to continuously use it anyways, why not just keep it there....less proccessing time to create and delete.  The "ON COMMIT" line will empty the table when the execute COMMIT is actioned at the end of the stored procedure.

remove those lines from the procedure and try again.

0
 
philsivyerAuthor Commented:
Hello
OK - done this now getting error on this line ..

 if (Repeats IS NOT NULL || If Repeats > 0) AND red_liquidity_point IS NOT NULL

It does not like the ||  the error message is....
.............Found '||', Expecting:)  or, -or-  OR  -or-   AND

Thanks

0
 
awking00Commented:
If you change your cursor as I suggested, you can eliminate the IF ... statement entirely.
0
 
philsivyerAuthor Commented:
OK - Please forgive me as I am not a PL SQL person - so, my question is now what should the following look like to work.

Create OR Replace Procedure addrows

is

Create Global Temporary Table addedrows  --copy of your table
(

Cpy varchar(20),
Period varchar(20),
Repeats Number,
Date Date,
value Number

)

cursor inputrows is select * from <your table>;

Begin

      For Cpy_rec in inputrows
      LOOP      
            if (Repeats IS NOT NULL || If Repeats > 0) AND Period IS NOT NULL
            CASE Period
                  When 'Daily' THEN
                        FOR InsertCTR IN 1..Repeats
                        LOOP
                                   Insert Into addedrows (Cpy, Period, Repeats, Date, Value) Values (Cpy, Period, NULL, Date + InsertCTR, Value)

                        END LOOP;
                  When 'Weekly' THEN
                        FOR InsertCTR IN 1..Repeats
                        LOOP
                                   Insert Into addedrows (Cpy, Period, Repeats, Date, Value) Values (Cpy, Period, NULL, Date + (InsertCTR*7), Value)

                        END LOOP;
                  When 'Monthly' THEN

                        FOR InsertCTR IN 1..Repeats
                        LOOP
                                   Insert Into addedrows (Cpy, Period, Repeats, Date, Value) Values (Cpy, Period, NULL, Add_Months(Date, InsertCTR), Value)

                        END LOOP;
                  When 'Quarterly' THEN

                        FOR InsertCTR IN 1..Repeats
                        LOOP
                                   Insert Into addedrows (Cpy, Period, Repeats, Date, Value) Values (Cpy, Period, NULL, Add_Months(Date, (InsertCTR*3)), Value)

                        END LOOP;

                  When 'Yearly' THEN
                        FOR InsertCTR IN 1..Repeats
                        LOOP
                                   Insert Into addedrows (Cpy, Period, Repeats, Date, Value) Values (Cpy, Period, NULL, Add_Months(Date, (InsertCTR*12)), Value)

                        END LOOP;

            END
      END LOOP;


      Insert into <YOUR TABLE> (Cpy, Period, Repeats, Date, Value) Values (SELECT Cpy, Period, NULL, Date, Value FROM addedrows);
      Delete from addedrows;
      DROP Table addedrows;
      commit;


end;

0
 
slightwv (䄆 Netminder) Commented:
>> if (Repeats IS NOT NULL || If Repeats > 0)

|| is not Oracle and no inner if.  Use OR:   if (Repeats IS NOT NULL or Repeats > 0)

>>you can eliminate the IF

Agreed.  Let the SQL do all the work it can for you.

>>so, my question is now what should the following look like to work.

No that will not work.

Again, a temporary table is a physical Oracle object.  It cannot be created like:

...
is

Create Global Temporary
...


Log into sqlplus and create it OUTSIDE the procedure.  Create it once and never again.  You just keep using it inside your code.

>>Insert into <YOUR TABLE> (Cpy, Period, Repeats, Date, Value) Values

No insert into VALUES and SELECT...  One or the other





In looking at the entire procedure, you can probably eliminate the entire structure.

something like (typed in, untested and you need to replace your column names):

Insert into <YOUR TABLE> (Cpy, Period, Repeats, Date, Value)
(select col1, col2,
CASE Period
          When 'Daily' THEN Date + InsertCTR
          When 'Weekly' THEN Date + (InsertCTR*7)
          When 'Monthly' THEN Add_Months(Date, InsertCTR)
END
from <your table> where
   Repeats IS NOT NULL and Repeats > 0 AND Period IS NOT NULL
)
;
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 6
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now