I've got lots of different tables, each pertaining to a different
marketing campaign.
I have another table that stores the name & schema of each of these
tables, together with other information (eg what the marketing
campaign code is, a long description of what's in each table, and so
on).
Organisationally, this makes things a bit easier; my DBA doesn't have
to sit there thinking of unique variations on
'Marketing_campaign_to_recent_customers_that_did_t ravel_last_easter'
to name the tables: she just sits there and puts a row in the other
table, so we can then see at a glance how many campaigns have been
issued of type A, B, C, etc...
What I'd like us to be able to do is identify the table that we want
to select from, by looking up its tabname & tabschema from the
organisational table. Is that possible? If you know which table it
is that you're looking at, this is usually quite trivial, but I'm not
sure if the reverse is even possible or not.
(I'm thinking something along the lines of
SELECT customer_id
FROM [ figure out what table you want to select from and substitute it
here ]
WHERE [arbitrary condition applied]
Any thoughts on the possibility or merit/demerit of this approach
would be gratefully appreciated
JCSJF 5 2427
"James Foreman" <ja***********@abcmail.co.uk> wrote in message
news:78**************************@posting.google.c om... I've got lots of different tables, each pertaining to a different marketing campaign.
I have another table that stores the name & schema of each of these tables, together with other information (eg what the marketing campaign code is, a long description of what's in each table, and so on).
Organisationally, this makes things a bit easier; my DBA doesn't have to sit there thinking of unique variations on 'Marketing_campaign_to_recent_customers_that_did_t ravel_last_easter' to name the tables: she just sits there and puts a row in the other table, so we can then see at a glance how many campaigns have been issued of type A, B, C, etc...
What I'd like us to be able to do is identify the table that we want to select from, by looking up its tabname & tabschema from the organisational table. Is that possible? If you know which table it is that you're looking at, this is usually quite trivial, but I'm not sure if the reverse is even possible or not.
(I'm thinking something along the lines of
SELECT customer_id FROM [ figure out what table you want to select from and substitute it here ] WHERE [arbitrary condition applied]
Any thoughts on the possibility or merit/demerit of this approach would be gratefully appreciated
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
Sure, you can use dynamic SQL to build the query based on some results
of another query or input to a script or program. Take a look at the
Application Development Guides for dynamic SQL.
HTH
Ken
"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message news:<CX********************@news20.bellglobal.com >... "James Foreman" <ja***********@abcmail.co.uk> wrote in message news:78**************************@posting.google.c om... I've got lots of different tables, each pertaining to a different marketing campaign.
I have another table that stores the name & schema of each of these tables, together with other information (eg what the marketing campaign code is, a long description of what's in each table, and so on).
Organisationally, this makes things a bit easier; my DBA doesn't have to sit there thinking of unique variations on 'Marketing_campaign_to_recent_customers_that_did_t ravel_last_easter' to name the tables: she just sits there and puts a row in the other table, so we can then see at a glance how many campaigns have been issued of type A, B, C, etc...
What I'd like us to be able to do is identify the table that we want to select from, by looking up its tabname & tabschema from the organisational table. Is that possible? If you know which table it is that you're looking at, this is usually quite trivial, but I'm not sure if the reverse is even possible or not.
(I'm thinking something along the lines of
SELECT customer_id FROM [ figure out what table you want to select from and substitute it here ] WHERE [arbitrary condition applied]
Any thoughts on the possibility or merit/demerit of this approach would be gratefully appreciated 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
> > 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
Sure you can:
SELECT ... FROM TABLE (subselect) ...
you can also use the WITH clause:
WITH subselect AS table-defn SELECT...
which is especially useful for recursive queries.
Details are in the SQL Reference, which you should now read.
"James Foreman" <ja***********@abcmail.co.uk> wrote in message
news:78**************************@posting.google.c om... I've got lots of different tables, each pertaining to a different marketing campaign.
I have another table that stores the name & schema of each of these tables, together with other information (eg what the marketing campaign code is, a long description of what's in each table, and so on).
Organisationally, this makes things a bit easier; my DBA doesn't have to sit there thinking of unique variations on 'Marketing_campaign_to_recent_customers_that_did_t ravel_last_easter' to name the tables: she just sits there and puts a row in the other table, so we can then see at a glance how many campaigns have been issued of type A, B, C, etc...
What I'd like us to be able to do is identify the table that we want to select from, by looking up its tabname & tabschema from the organisational table. Is that possible? If you know which table it is that you're looking at, this is usually quite trivial, but I'm not sure if the reverse is even possible or not.
(I'm thinking something along the lines of
SELECT customer_id FROM [ figure out what table you want to select from and substitute it here ] WHERE [arbitrary condition applied]
Any thoughts on the possibility or merit/demerit of this approach would be gratefully appreciated
JCSJF
"James Foreman" <ja***********@abcmail.co.uk> wrote in message news:78**************************@posting.google.c om... I've got lots of different tables, each pertaining to a different marketing campaign.
I have another table that stores the name & schema of each of these tables, together with other information (eg what the marketing campaign code is, a long description of what's in each table, and so on).
<snip> Any thoughts on the possibility or merit/demerit of this approach would be gratefully appreciated
You might want to look at union-all views - depending on how many
tables (campaigns) you've got, they might offer a much simpler
solution. I think the currently-recommended limit is around 300
tables.
Anyhow, assuming that each table gets a table_id as a fk from the
campaign list table, then that table_id could become your table
constraint. With that in place you would have a single view sitting
on top of all the tables, and could join it directly with the campaign
list table to restrict which table you really want to select from.
This would allow the following advantages:
- simple retrival queries: no dynamic table changes, etc
- ability to retrieve/combine data from multiple tables in a single
easy query: for example to find all campaigns that single
household/company/email/etc were included within
- ability to write to a single view, rather than have to determine
which table to write to when creating the data in the campaign tables.
good luck,
buck This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Brandon M |
last post by:
I'm trying to get an Update Query to overwrite any records that already
exist. By default it appears to skip any records in which the key already
exists. Is there any way to change this?
|
by: Justin Koivisto |
last post by:
Firstly, I want to thank all that have helped me out with getting grips
on Access each time I've had questions. This has got to be one of the
most helpful groups that I've posted to over the years....
|
by: uspensky |
last post by:
I have a table (cars) with 3 fields:
VIN, Class, sell_price
101, sports, 10000
102, sports, 11000
103, luxury, 9000
104, sports, 11000
105, sports, 11000
106, luxury, 5000
107, sports, 11000
|
by: p |
last post by:
I'm trying to put a query into VBA code and its giving me difficulties.
I would like to put the following query:
SELECT tbl_Workload.Priority, tbl_Workload.Number AS Num,
tbl_Workload.Name,...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
| |