I need to filter a view on a variable list of values, but I wonder what's the best approach. Here's the SELECT-SQL we used before:

lcList = "ABC,DEF,GHI"
SELECT * FROM MyTable WHERE MyField $ m.lcList

I'm trying to build a view based on this, with lcList specified at run-time, but the view designer won't let me use the $ operator.

I tried the In operator, but it give me a syntax error when trying to save the view. If I copy-paste the SQL generated by the view in the command window, it work without problems. Here's the resulting SQL of the view:

lcList = '"ABC,DEF,GHI"'
SELECT * FROM MyTable WHERE MyField IN (&lcList)

I found a way to make it work, but it require that I create a cursor, which is more work and less usable:

CREATE CURSOR curFilter (MyField C (10))
INSERT INTO curFilter (MyField) VALUES ("ABC")
INSERT INTO curFilter (MyField) VALUES ("DEF")
INSERT INTO curFilter (MyField) VALUES ("GHI")
SELECT * FROM MyTable WHERE MyField IN (SELECT MyField FROM curFilter)

Is there a way of making my original view work (with the character variable) or better yet, use the $ operator in a view?