in/like statement??

[Sureshy]

Hi Friends.

can't work this out, maybe someone can help.

I have the following query:

SELECT i.item_sc, i.item_n, b.bldng_room_sc
FROM item i, bldng_room b
where i.bldng_room_id = b.bldng_room_id
AND i.item_sc LIKE ('DIG00%','HUB00%','JET00%')

obviously the like statement is not going to work as it should be an IN statememt however the values have a wildcard in them.

can someone please help and tell me how to do this.

thanks

[jmodic]

SELECT i.item_sc, i.item_n, b.bldng_room_sc
FROM item i, bldng_room b
where i.bldng_room_id = b.bldng_room_id
AND
(i.item_sc LIKE 'DIG00%' OR
i.item_sc LIKE 'HUB00%' OR
i.item_sc LIKE 'JET00%')

[Sureshy]

Thanks.

I tried that without the brackets and got thousands of results, all makes sense now.

[JMac]

or ...

SELECT i.item_sc, i.item_n, b.bldng_room_sc
FROM item i, bldng_room b
where i.bldng_room_id = b.bldng_room_id
AND SUBSTR(i.item_sc,1,5) IN ('DIG00','HUB00','JET00')


(and lets assume item_sc isn't indexed!)