CODEWITHSUNDEEP
sqloracle-database
Nate Heldman
Nate Heldman

Reputation: 39

gotta be a better way of doing this SQL WHERE/IN s

i have to test my Select statement returns against a Where/In clause that is completely unwieldy. I'm sure it could be worse with tens of thousands of numbers to check against, but this looks bad to me, despite the fact that it works.

Where p.description Like '%UbD%'
  Or pa.text Like '%UbD%'
And p.state_code_id IN ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','65')

how can i have the statement check something like '1-50','65' instead? is it possible? i'm using oracle.

Upvotes: 2

Views: 108

Answers (5)

Hart CO
Hart CO

Reputation: 34774

Assuming positive state codes and no 0, you could use:

AND (p.state_code_id <= 50
     OR p.state_code_id = 65)

Upvotes: 0

ZorleQ
ZorleQ

Reputation: 1427

If your codes are always the same, do what the other guys said. If they are different, then create a temporary table. Insert all your desired values into that new (temporary table) and do a join on both.

SELECT p.* 
FROM p, temp 
WHERE p.description Like '%xyz%' AND p.state_code == temp.code

Upvotes: 0

Dustin Laine
Dustin Laine

Reputation: 38503

Not changing the query, but makes it more maintainable. Put the data into a table and use a subquery...

Where p.description Like '%UbD%'
  Or pa.text Like '%UbD%'
And p.state_code_id IN (SELECT ID FROM States)

Upvotes: 1

jmadsen
jmadsen

Reputation: 3675

 AND ((p.state_code BETWEEN 1 AND 50) OR p.state_code = 65)

Upvotes: 1

karthikr
karthikr

Reputation: 99630

Try this:

Where p.description Like '%UbD%'
  Or pa.text Like '%UbD%'
  And ((p.state_code_id BETWEEN 1 AND 50) OR p.state_code_id = 65)

Upvotes: 3

Related Questions