472,809 Members | 2,592 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,809 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 2413

"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: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

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.