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


Saturday, June 23, 2007

I have problem with union

SQL Apprentice Question
use Sql Server 2000. I've structure like it:

Create table T1(
RID int primary key,
A varchar(8),
...
...
)


Create table T2(
RID int primary key,
A varchar(8),
...
...
)


To make easy to view, so I make a view like it


Create view VUni as
Select RID, A From T1
union


Select RID, A From T2


Now, when I execute


Select * From VUNI Where RID between 15 and 25


So the process needs a lot of times alias Very Slow. How to make it best
performance?


Oh yeach, it's other question, I want to select data from table which its
name is name dimanically (variable)


IF @YEAR=2000 Select * FROM T2000
ELSE
IF @YEAR=2001 Select * FROM T2001
ELSE
IF @YEAR=2002 Select * FROM T2002
ELSE
...
...


To make it compact, So how to the code?


Next question, How to avoid error? I execute like it


Select a/b from T2


seldom the statement is error, because value of filed "a" is 0. I want if
a/b is error calculation, so it give value 0 automatically. Can I do it?
Without I ve to make a function? Or any function (built in) to handle it?


Celko Answers
>> I want to select data from table whose name is dynamic (variable) <<


IF @year=2000 SELECT * FROM T2000
ELSE
IF @year=2001 SELECT * FROM T2001
ELSE
IF @year=2002 SELECT * FROM T2002
ELSE
...
<<

This design flaw is so bad it has a name, like a disease: "Attribute
Splitting". Instead of separate tables, you need one table with a
"_year" column in it. You then use VIEWs or queries.


You probably also split an attribute in the first part of this
posting, and are trying to fix it with a UNION [ALL] construct.



Original Source

No comments: