> >
I'm having trouble visualizing the tables you are describing. I suspect
others may have the same difficulty. Some small example tables to illustrate
what you mean would be useful.
Also, what version of DB2 and what OS are you using? The answer to many
questions depends on that information.
Rhino
Sorry: UDB 8.1.5 on Suse 8.1
For example:
CREATE TABLE marketing.campaign_1 (cid int);
CREATE TABLE marketing.campaign_2 (cid int);
CREATE TABLE marketing.campaign_3 (cid int);
....
CREATE TABLE marketing.campaign_def (tabname varchar(255), tabschema
varchar(255), construct varchar(1000), marketingcode varchar(5));
INSERT INTO campaign_def VALUES
('campaign_1', 'marketing', 'Campaign to customers who bought FWD in
last 6 months', 'CODE1'),
('campaign_2', 'marketing', 'Everyone with a gold card who purchased
three times last year and never since 1 Feb 04', 'CODE2'),
('campaign_3', 'marketing', 'Customers that hire clowns for birthday
parties and drink cheap wine', 'CODE1');
Populate campaign_1, _2, _3, etc with as many rows as you like. Then
we'd like to choose to get all the customers out of a particular
campaign, where the campaign is set by conditioning on the values of
campaign_def.construct.
Having used some CRM list management tools a few years ago, it's clear
you can get a GUI to manage the campaigns and do this kind of thing
(and then use EXCEPT, UNION and INTERSECT to dedupe, exclude and so
on). Now that we're doing something homebrew and I don't have the
budget to buy some GUI in, I'm wondering if this is an efficient way
to build the schema. - Following Ken's reply, I can see that with
campaign_def in place, we might be able to generate the SQL using an
application, but I'm not sure if my initial hope to define the FROM
clause via a sub query is possible using nothing but DB2.
Thanks
JCSJF