Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Newbie: Unique records

Newbie: Unique records

From: Mike Chidsey <Chidseym_at_worldnet.att.net>
Date: 1998/06/25
Message-ID: <6mtkvk$ski@bgtnsc03.worldnet.att.net>#1/1

I am trying to create a procedure to handle promotions within an Oracle database. There are 6 different promotion categories, all with different criteria. One common theme, though, is that a member can only qualify for a promotion one time, with some minor exceptions.

The problem is the user can run the promotions several times, in order to see if any new members qualify.

Here is an example:

Birthday promotion
Criteria: Member has a b-day the month after the promo run date

Tables: Members, Promotions, Promo_Instance, Promo_Member

The Promotions table holds all the info about the promo: Promo_ID, Name, start date, expiration date, offer value

The Promo_Instance table holds all the info about an individual "run" of a promotion: Promo_Inst_Id, Promo_Id, Run_Date

The Promo_Member table holds all the details about the run: Promo_Inst_ID, Member_ID, Redeemed

When a promotion is created, an entry is added to Promotions. When the promotion is run, a record is added to Promo_Instance and the qualifying members are added to Promo_Member.

For the B-day promotion, I want to test to see if any other promotions were run for the same time period (At this point, I already have ALL the qualifying members):

--Get a list of all promo runs where the type is 1 (Birthday) CURSOR prev_promo IS

    SELECT promo_instance.promo_inst_id, promo_instance.run_date,

promo_instance.promo_id, promotions.promo_type FROM promotions INNER JOIN
promo_instance ON promotions.promo_id = promo_instance.promo_id WHERE
promo_type = 1

OPEN prev_promo
--Loop through all the previous promotions to see if any of the run dates are equal
-- to the current run date (only test for month & year) LOOP
    IF TO_CHAR(run_date,'mm-yy')=TO_CHAR(SYSDATE,'mm-yy') THEN

--this is where I don't know what to do
--I want to add the members that qualify that aren't already in the
list(s)

--Since there can be several runs, I thought it would be better to
start with a

--master list and simply delete those members that have already rec.
a

--promo.

I thought about adding ALL the members to another table, PROMO_INBOX, and then delete the members already qualified from that table and then append the remaining records (if any) to the table. Is there any way to do this w/o looping through ALL the records for that promo_instance?

Any response would be greatly appreciated....

--
Mike Chidsey
Chidseym_at_worldnet.att.net

You can have everything in life you
want if you will just help enough other
people get what they want.
           -Zig Ziglar
Received on Thu Jun 25 1998 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US