Very big table

[Alex]

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

[DA Morgan]

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

[Erland Sommarskog]

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