469,076 Members | 1,437 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,076 developers. It's quick & easy.

Very big table

Hi Expert,
In my DB i have a big table like this:

Primary key | param1 | para2 | param3 | param4 | ... | param100|
------------------+-----------+---------+-----------+------------+----
+--------------+
| | | |
| | |

There is any way to build a parametric "Where" instead of manually
specification for example:

WHERE param1 = Y OR param2 = Y or param3 = Y ... param100 = Y

mybe for example:

WHERE paramx = Y

thanks

Apr 16 '07 #1
2 1498
Alex wrote:
Hi Expert,
In my DB i have a big table like this:

Primary key | param1 | para2 | param3 | param4 | ... | param100|
------------------+-----------+---------+-----------+------------+----
+--------------+
| | | |
| | |
Then I would suggest you stop writing DML and fix the problem:

Primary Key | Parameter ID | Parameter Value

Your report writers, no doubt, will want to draw blood for such
an abominable lack of design.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Apr 16 '07 #2
Alex (al*****@gmail.com) writes:
Hi Expert,
In my DB i have a big table like this:

Primary key | param1 | para2 | param3 | param4 | ... | param100|
------------------+-----------+---------+-----------+------------+----
+--------------+
| | | |
| | |

There is any way to build a parametric "Where" instead of manually
specification for example:

WHERE param1 = Y OR param2 = Y or param3 = Y ... param100 = Y

mybe for example:

WHERE paramx = Y

It is not unlikely that the best design for the table would have been:
CREATE TABLE details (primarykey ....,
paramno tinyint NOT NULL,
value ....,
CONSTRAINT pk_deatils(primarykey, paramno))

In this case the query would be trivial to write.

With the current design, you could generate the code, but in the end
you would need that long chain of ORs one way or another.

Relational databases are simply not meant to be used that way.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 16 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by robin | last post: by
1 post views Thread by David Lawson | last post: by
11 posts views Thread by DJJ | last post: by
8 posts views Thread by York | last post: by
2 posts views Thread by shsandeep | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.