How to select ID from table based on a value in a List?

Question

Question

Michael Lundberg · Dec 20, 2023

Hello!

I wonder how to select a conditional value from a Table column that has lists of values?
Example: I have a table in the database that looks like below:

ID	Itemname	IDList
1	TestItem	1 7 10 17

For example, I want to know which Itemname it is for number 10 that is in the IDList. What should this SQL syntax look like? I have tried %INLIST and other things but only get blank value. How should the SQL-syntax for that query look like?

Greatful for all tips.

Regards,
Michael

Product version: IRIS 2023.1

$ZV: IRIS for Windows (x86-64) 2023.1.1 (Build 380U) Fri Jul 7 2023 23:43:12 EDT [HealthConnect:5.1.0-1.m1]

Discussion (4)2

Log in or sign up to continue

Enrico Parisi · Dec 20, 2023

Check FOR SOME %ELEMENT (SQL) documentation.

1 0

score 0 · Answer 1 · 2023-12-20T08:26:57-05:00

If your IDList is a string, you'd have to use WHERE 10 %INLIST $LISTFROMSTRING(IDList,' ') so that the string is converted to an actual list for %INLIST to work.

score 0 · Answer 2 · 2023-12-20T08:30:43-05:00

Michael Lundberg · Dec 20, 2023

Hi David!

It's integers-values in the list. It's actually IDs from other tables

/Michael

0 0

score 1 · Answer 3 · 2023-12-21T04:17:27-05:00

Hello Enrico

Thanks! It worked.

SELECT Item Name
from <Table>
WHERE FOR SOME %ELEMENT(IDList) (%VALUE=10)
cooked the steak! :0)

Regards,
Michael