Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Cutting down on repetitive WHERE clauses?

Posted on 2008-11-07
4
Medium Priority
?
230 Views
Last Modified: 2012-05-05
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!
0
Comment
Question by:Tabris42
4 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 22904090
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 22904091
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22904095
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
 

Author Comment

by:Tabris42
ID: 22904118
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question