Forum Discussion
Helper IIN Operator where the string is a Field
The expanded version is likely to work better.
The IN operator only works on a list, not a string. It's possible to convert a string into a list (with a mix of SUBSTITUTE, GENERATESERIES, and PATHITEM), but that's almost certainly less efficient than CONTAINSSTRING.
Depending on what you're ultimately trying to do, it's not clear if you even need the 'Device Type to Applications' table. If it's just a helper table, then you can almost certainly do better without it.
Helper INo its not related.
I have 3 tables like this
Devices
Applications
Device Type to Application
What I want to do is for each device type in Devices find out what application we should be looking for in Applications via Device to Application.
So imagine the tables have the following (simplified):
Devices:
Device ID, Device Name, Device Type
Applications:
Device ID, Application Name, Application Version
Device Type to Application: (this is a table that i made myself to try and find the apps we are after based on the device type):
Device Type, Applications
I currently have Devices tied to Applications via device ID which should give the apps per device and Device Type to Applications tied to Devices via the device type.
So for each device in Devices their Device Type will look into Device Type to Applications to find what applications to look for and from there search in Applications (after filtering on the device id link).
The reason why there is multiple comma delim applications is because one device type can have different names for the application we are looking for depending on the year it was made.
I tried to do it this way as well for Devices to applications:
Device Type A, ProdA
Device Type A, ProdZ
Device Type A, ProdXY
Device Type B, Prod2
Device Type B, Prod4
Device Type B, Prod53
But I coulnt get the relationships working correctly that way hence why i bundled up the apps into a comma delimited list (hoping IN would work)
OH and containsstring might work but there are 200,000 devices with a few hundred apps each so not sure what the performance would be.
Thanks again for your help.
Super UserThe expanded version is likely to work better.
The IN operator only works on a list, not a string. It's possible to convert a string into a list (with a mix of SUBSTITUTE, GENERATESERIES, and PATHITEM), but that's almost certainly less efficient than CONTAINSSTRING.
Depending on what you're ultimately trying to do, it's not clear if you even need the 'Device Type to Applications' table. If it's just a helper table, then you can almost certainly do better without it.