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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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!
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)