473,320 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
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
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
> >
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
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
"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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
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?
2
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....
4
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
8
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,...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
1
isladogs
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...
0
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...
1
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)...
0
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...
1
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....
0
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

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.