Can I use a sub query to define the contents of the FROM clause of a query?

James Foreman

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

Nov 12 '05 #1

Rhino

"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

Nov 12 '05 #2

Ken

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

Nov 12 '05 #3

James Foreman

> >

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

Nov 12 '05 #4

Mark Yudkin

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

Nov 12 '05 #5

Buck Nuggets

"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

Nov 12 '05 #6