AnsweredAssumed Answered

Using IN and LIKE in Table Lookup WHERE Clause Syntax

Question asked by kbobeck on Mar 24, 2017
Latest reply on Mar 27, 2017 by Asle Frantzen

I was hoping to get some clarification on how IN and LIKE work when using Table Lookup in System Explorer. The Live Library has the following information:

 

I am trying to populate the "Entity Number" using a table lookup.

 

My table appears like so:

Originally, I was falsely getting a 'No Data' error when my lookup was

SELECT [Entity Number] FROM [Utility Distribution] WHERE [Entity] LIKE ('%Element%')

or

SELECT [Entity Number] FROM [Utility Distribution] WHERE [Entity] IN ('%Element%')

or

SELECT [Entity Number] FROM [Utility Distribution] WHERE [Building Code] = @[|Building Code] AND Entity IN('%Element%')

 

Using the Live Library info as a guide, I then tried

SELECT [Entity Number] FROM [Utility Distribution] WHERE [Entity] LIKE ['%Element%']

and got the following error

 

I've managed to use IN successfully before:

SELECT [Entity Number] FROM [Utility Distribution] WHERE [Building Code] = @[|Building Code] AND Entity IN('%Element%')

 

So I am unsure where to go from here. One would think that using IN would mean "If [0912] is IN [0912I], then populate the corresponding Entity Number", which would be 2 in this case. Since that is apparently not how this operates, would someone mind providing a more in-depth explanation of the purposes of IN and LIKE?

Outcomes