Using IIF() after IN with macro substitution variable inside query where clause

Question

I am trying to accomplish a query as such:

select  *
    from table1
    where id1 in IIF(llchoose_flag = .T., (&lcid_list.), (id1))

where the character string lcid_list = "1,2,3".

So if llchoose_flag = .T., I want to select records where id1 is in the list defined by the variable lcid_list. And if llchoose_flag = .F., I want to select all records, i.e. where id1 in (id1). I have also tried using INLIST() but have not been successful with either method. The error I seem to keep getting is: Function name is missing ), but I don't believe that is true. I am starting to think that it might not be possible to use an IIF() statement inside the IN clause, but maybe I just need to fix my syntax.

I am trying to accomplish this query in either FoxPro or SQL. The above code syntax is my attempt in FoxPro, but it would be very similar in SQL, aside from the macro substitution notation I believe?

I am able to run a FoxPro query such as:

select  *
    from table1
    where id1 in (&lcid_list.)

So it seems to not like the IIF() after the IN. One way around this would be to store different values inside the lcid_list variable depending on llchoose_flag, but that would involve storing millions of id values in that character string when llchoose_flag = .F. (all id records in table1), which would not be as efficient as an IIF method.

Let me know if anyone knows of a way to accomplish this type of query. Thanks.

Answer 1

In VFP basically it is:

select  * from table1 where !m.llChoose or id1 in (&lcid_list.)

In SQL server it would be similar.

However, if you think about it, a statement in VFP cannot go over 8K and thus id count is much less than 4K at max. If it would be a call to SQL server, than that limit is much higher (SQL server side limit is much higher), for VFP side 16 Mb by documentation.

Since the call starts from within VFP, either it is a VFP data or SQL server, it is much better to eliminate if from where and to push that outside, ie:

if m.llChoose
   select  * from table1 where id1 in (&lcid_list.)
else
   select  * from table1
endif  

If it is for SQL server then you could either create a temp table and fill it with either one by one or using a bulk copy activex (search for sqlbulkcopy on foxite.com). Then you would simply do a join.

Another approach is to pass a parameter to SQL server and have it parsed there. SQL server itself is slow at string operations and it might time out with large strings. However you could use CLR functions for parsing. ie: on Foxite check:

A little handy utility for SQL server

Using that, you can pass your ID list as a string parameter and the length of it could be up to 2 Gb in length (in theory, if you use a memo field as the parameter source), or 16 Mb plain string memory variable. Your code then would look like:

lcSQL = 'select * from Table1 t'
if m.llChoose 
   lcSQL = m.lcSQL + ;
      ' inner join ufn_IntsToTable(?m.lcid_list) ids'+;
      ' on t.id1 = ids.v'
endif

lcID_list = "1,2,3"

SQLExec(m.handle, m.lcSQL, 'crsResult')

PS: You could also send an XML table and parse on SQL server side for joining (would be slower).