Re: Newbie: Unique records

From: David Greensmith <david_at_green1.demon.co.uk>
Date: 1998/07/01
Message-ID: <359ab0fd.15903457_at_news.demon.co.uk>#1/1


"Mike Chidsey" <Chidseym_at_worldnet.att.net> wrote:

>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?
>
> [Quoted]
>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
>
>

Have you considered coding your selection criteria into a view?

David
David Greensmith :-)
(david_at_green1.demon.co.uk) Received on Wed Jul 01 1998 - 00:00:00 CEST

Original text of this message