Cutting down on repetitive WHERE clauses?

This is a simple question, I hope, but an urgent one. Our application passes an SQL string to an app called Crosstab to basically transpose the result set. However, rarely the SQL string passed to Crosstab is "too large to be processed", and here's why:

WHERE study_id = 537 OR study_id = 546 OR study_id = 508 OR study_id = 534 OR study_id = 539 OR study_id = 715 OR study_id = 657 OR study_id = 717 OR study_id = 476 OR study_id = 477 OR study_id = 479 OR study_id = 480 OR study_id = 492 OR study_id = 501 OR study_id = 504 OR study_id = 506 OR study_id = 507 OR study_id = 533 OR study_id = 535 OR study_id = 538 OR study_id = 540 OR study_id = 541 OR study_id = 544 OR study_id = 545 OR study_id = 547 OR study_id = 548 OR study_id = 550 OR study_id = 569 OR study_id = 611 OR study_id = 339 OR study_id = 346 OR study_id = 347 OR study_id = 343 OR study_id = 349 OR study_id = 494 OR study_id = 566 OR study_id = 503 OR study_id = 372 OR study_id = 395 OR study_id = 379 OR study_id = 382 OR study_id = 594 OR study_id = 656 OR study_id = 661 OR study_id = 400 OR study_id = 663 OR study_id = 665 OR study_id = 669 OR study_id = 671 OR study_id = 673 OR study_id = 677 OR study_id = 678 OR study_id = 700 OR study_id = 493 OR study_id = 505 OR study_id = 399 OR study_id = 387 OR study_id = 536 OR study_id = 542 OR study_id = 543 OR study_id = 567 OR study_id = 570 OR study_id = 572 OR study_id = 573 OR study_id = 574 OR study_id = 575 OR study_id = 576 OR study_id = 578 OR study_id = 585 OR study_id = 588 OR study_id = 590 OR study_id = 593 OR study_id = 596 OR study_id = 610 OR study_id = 659 OR study_id = 660 OR study_id = 481 OR study_id = 319 OR study_id = 320 OR study_id = 327 OR study_id = 389 OR study_id = 393 OR study_id = 357 OR study_id = 549 OR study_id = 360 OR study_id = 378 OR study_id = 366 OR study_id = 662 OR study_id = 369 OR study_id = 371 OR study_id = 375

Imagine that but even longer. There is no "range" per se of study ids in the query, users select the studies from a select form on a website and the WHERE clause above is built based on that.

So to cut down on the sheer size of this query, is there any way I can just delimit these OR study_ids to be seperated by commas, like
537, 546, 508, 534, 539, 715, 657, 717, 476, 477, 479, 480, 492, 501, 504, 506, 507, 533, 535, 538, 540, 541, 544, 545, 547, 548, 550, 569, 611, 339, 346, 347, 343, 349, 494, 566, 503, 372, 395, 379, 382, 594, 656, 661, 400, 663, 665, 669, 671, 673, 677, 678, 700, 493, 505, 399, 387, 536, 542, 543, 567, 570, 572, 573, 574, 575, 576, 578, 585, 588, 590, 593, 596, 610, 659, 660, 481, 319, 320, 327, 389, 393, 357, 549, 360, 378, 366, 662, 369, 371, 375
?

If my question makes no sense, please let me know and I'll elaborate.
Thanks!
Tabris42Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
you can use IN

WHERE Study_id in (537, 546, 508, 534, 539, 715, 657, 717, 476, 477, 479, 480, 492, 501, 504, 506, 507, 533, 535, 538, 540, 541, 544, 545, 547, 548, 550, 569, 611, 339, 346, 347, 343, 349, 494, 566, 503, 372, 395, 379, 382, 594, 656, 661, 400, 663, 665, 669, 671, 673, 677, 678, 700, 493, 505, 399, 387, 536, 542, 543, 567, 570, 572, 573, 574, 575, 576, 578, 585, 588, 590, 593, 596, 610, 659, 660, 481, 319, 320, 327, 389, 393, 357, 549, 360, 378, 366, 662, 369, 371, 375 )

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chapmandewCommented:
You can do this:


select * from tablename where study_id in(537, 546, 508, 534, 539, 715, 657, 717, 476, 477, 479, 480, 492, 501, 504, 506, 507, 533, 535, 538, 540, 541, 544, 545, 547, 548, 550, 569, 611, 339, 346, 347, 343, 349, 494, 566, 503, 372, 395, 379, 382, 594, 656, 661, 400, 663, 665, 669, 671, 673, 677, 678, 700, 493, 505, 399, 387, 536, 542, 543, 567, 570, 572, 573, 574, 575, 576, 578, 585, 588, 590, 593, 596, 610, 659, 660, 481, 319, 320, 327, 389, 393, 357, 549, 360, 378, 366, 662, 369, 371, 375)
0
Patrick MatthewsCommented:
Hello Tabris42,

I would try using a table that lists each study_id, and whether or not to include it in your report.  That would
simplify your query greatly, as you would then just link to that table.

Of course, if you already have a Studies table, then it may be as simple as identifying an existing column
or adding a new one to fill that purpose.

Regards,

Patrick
0
Tabris42Author Commented:
WOW... seems so obvious and simple. Probably could've cracked open a text book for that one, but I had no idea what I was looking for. Thanks very much, you've saved the day!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.