SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Sunday, September 10, 2006

Change Query Table

SQL Apprentice Question
have a series of tables in the database that hold orders for of the
months
i.e.
Tbl: June06
OrderNo, Amount
0001 100
102 150

Tbl: July06


What I want to do is, when a user passes in the month say July06, I
should query from table call June06.
Instead of putting the entire query in a string, is there a way to
switch the table names?


My query is long and I have tables for couple years.


Thanks



Celko Answers

>> I have a series of tables in the database that hold orders for of the months <<


"series of tables"??? No such animal in RDBMS. A table models a set
of entities or relationship of the same -- the whole damn set, not
parts of it. This is just basic math and elementary school set theory,
not advanced stuff.

This total screw up has a name -- Attribute Splitting. You take the
values of an attribute and make them into columns or tables in the
schema. Do you also have separate table for employees based on gender
or religion? Same stupid error!



>> What I want to do is, when a user passes in the month say July06, I should query from table call June06. <<


That is one of the MANNNNNY reasons this is screwed up, non-relational
design. You are mimicing a 1950's magentic tape system! LITERALLY!!
The tape labels had "yyddd" so you could keep track of them.

Put everything in one table, create a reporting period table:


CREATE TABLE ReportPeriods
(period_name CHAR(10) NOT NULL PRIMARY KEY,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
etc.);


Use a BETWEEN predicate JOIN to classify your data.


Also, name the periods in alphabetic order for sorting. That means
"2006-06" and not "Jun06"; this is a basic programming trick and not
just SQL.

No comments: