Replicating rows in PL/SQL

[mufnet2]

Dear Gurus,
am running a competition and this is a scenario.
when a customer buys a card worth $1 he gets one entry and when he buys a card worth $2 he gets 3 entries and $5 gets 8 entries and so forth.
I have a two tables
customer table card_table
------------- -------------
customer_ID customer_ID, card_value, purch_date

how can I write a PL/sql program that will replicate entries that have a card more than $1.

John

 

[SantaMufasa]

Muffy,

I have a few questions. First, why are you specifying PL/SQL as the implementation environment for your solution, when regular SQL is a viable alternative (unless this is a classroom assignment)?

...when a customer buys a card worth $1 he gets one entry and when he buys a card worth $2 he gets 3 entries and $5 gets 8 entries and so forth.

Is the card value-to-entries ratio supposed to be obvious? Please humour me and explicitly specify the card value-to-entries algorithm you want to use.

how can I write a PL/sql program that will replicate entries that have a card more than $1.

What "entries" do you want replicated? It is not obvious from your posting.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[mufnet2]

I want to replicate the entries where a customer buys for $2 and up.

 

[Dagon]

Procedure p_rep_entries (p_person in integer,
                         p_card_value in number,
                         ...) is
begin
   if p_card_value = 2 then
      for i in 1..3 loop
         insert into entry_table
         (person_no, entry_no,...)
         values
         (p_person, i, ...);
      end loop;
   elsif p_card_value = 5 then
      for i in 1..8 loop
           ....


   end if;
end;

 

[mufnet2]

Thanks Musafa but what wrong with this below
Procedure p_rep_entries
spool tt.txt
(msisdn in integer,
p_card_value in number,
) is
begin
select
subscribernumber,
voucherserialnumber,
RECHARGEaMOUNTMARKET
from
air_cdr a, in_cdr partition (incdr_2006_1120)
where
substr(a.origintimestamp,1,8) = '20061019' and rownum < 3;
if rechargeamountmarket = 2 then
spool off;

 

[Dagon]

I take it you don't actually know much about PL/SQL.

a) you can't do a spool in a PL/SQL routine
b) when doing a select, you have to select into a variable
e.g.

select
subscribernumber,
voucherserialnumber,
RECHARGEaMOUNTMARKET
into
v_subnumber,
v_voucher,
v_recharge
from
air_cdr a, in_cdr partition (incdr_2006_1120)
where
substr(a.origintimestamp,1,8) = '20061019' and rownum < 3;

c) This won't work unless you are only retrieving one row (which you clearly aren't since you have rownum < 3). You need to use a cursor.

 

[KenCunningham]

d) No wonder one never sees SantaMufasa and Dagon in the same room at the same time!

I don't mind people who aren't what they seem. I just wish they'd make their mind up.

 

[SantaMufasa]

Mmmmm

http://wwwwwwaaaaaaaaahhhhhhhaaaaahhhhhaaa!

You figured me/us out! Don't tell anyone else, please!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[mufnet2]

Thanks Musaba for your help I will try it out. I did pl/sql prograing 7 years ago so I have forgotten certain principles.
Thanks once more

 

[Dagon]

There are three of us now - Dagon, Mufasa and Musaba !

 

[SantaMufasa]

Perhaps we should just consolidate into "MusabaDangFasa".

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]