Ken --

This part of the where clause simply places either the value 0 (because the record with 0 as it's ID has 0 as a value), or the value of a distinct value at each column. If we use the algorithm you suggest, the same value can appear in different columns, so we'll have rows in the result set with duplicate solutions to the problem. This approach generates a unique set of values, which I understand to be the goal.

I was really interested more in whether this could be done rather than its practical application. That it can be done means that SQL could be used for a goal-seeking engine in some restricted sense.

This kind of problem can be difficult to solve procedurally and test. A declarative algorithm, such as this, is actually rather boring to put together, and it's easier to determine, by visual inspection, whether it is correct.

I ran this with 20 rows and it took 5 secs on a 1 gig system. So, it could well take several hours with an set of 100 or so records. But, how long would it take to develop and test the procedural algorithm?

Another approach, which might significantly reduce execution time, would assign each value to its own table, with a 2nd row in that table with value 0. That would mean each table would have 2 rows, rather than n. It would eliminate the clause you pointed out. This would be a more scaleable approach, I believe.

Best,

Jay






>I don't understand your use of
>AND INLIST (x2.nid, 2, 0);
>>> AND INLIST (x3.nid, 3, 0) ;
>>> AND INLIST (x4.nid, 4, 0) ;
>>> AND INLIST (x5.nid, 5, 0) ;
>>> AND x0.nid = 0;
> to make sure that the indices are distinct. It appears that indeed they are distinct but you miss most of them. How about
>x1.nid + x2.nid + x3.nid + x4.nid + x5.nid = 15 && = 5*6/2.
>
>To handle the case for 6,7,8..., you can first generate the SQL statement and execute is as a macro, so clearly we can handle the general case. The question is, how efficient is the SQL optimisationr and just how long will this take to run. I have a feeling that you will be forced back to less elegant solutions working directly with arrays.
>
>
>>I formatted the data entry a bit better here.
>>
>>>>I'm working on a reconciliation project and i'm stuck. I have a list of numbers lets say 100 numbers and I have a target amount that some permutation of these numbers adds up to. I don't know how many of them will add up to this number. I need the list of numbers that adds up to my target number. I tried writing this but my brain overworked itself. Anybody ever write something like this or have any ideas? Thanks in advance!!!
>>>>
>>>>Example
>>>>Say my number is 362.21 and my number list is:
>>>>201.10
>>>>25.18
>>>>150.11
>>>>778.88
>>>>151.21
>>>>11.31
>>>>10.00
>>>>...
>>>>The answer is 201.10 150.11 and 10.00
>>>
>>>Marvin --
>>>
>>>I've wondered about a SQL solution to this. If 2 tables are joined without a join condition, a Cartesian join results -- all possible combinations of rows of the 2 tables. This is similar to your problem. The issue is how to tame it.
>>>
>>>I don't know if this will suit your needs. You mention 100 possible values. If that's realistic, this approach may work. However, orders of magnitude greater numbers of values would run up against VFP limits, though T-SQL might work.
>>>
>>>Conceptually, what we'll do is create a crosstab from your data and sum the values of the fields. We'll then do a selfjoin with the table into itself for the number of values that you have. The key is adding 1 more value -- a zero --into the data. The selfjoin will then allow us either to represent the actual value of the data, or 0. Finally, our where clause will be based on the desired sum.
>>>
>>>Consider the following table structure:
>>>TABLE t1
>>> nID N(10)
>>> nVal N(10)
>>>
>>>and values:
>>

>>>  nID      nVal
>>>   0       0           This is added to our data set.
>>>   1       3
>>>   2       5
>>>   3       4
>>>   4       6
>>>   5       7
>>

>>>
>>>Let's take a first cut at the SQL:
>>>
>>>

>>>SELECT x1.nval, x2.nval, x3.nval, x4.nval, x5.nval ;
>>>	FROM t1 x1, t1 x2, t1 x3, t1 x4, t1 x5 ;
>>>	into cursor temp
>>>

>>>
>>>The syntax t1 x1... uses the t1 table under other aliases. Each value is represented by the nVal field, but of different tables. This will combine all values with all values including themselves. A bit more than we need.
>>>
>>>The second cut :
>>>

>>>SELECT x1.nval, x2.nval, x3.nval, x4.nval, x5.nval ;
>>>	FROM t1 x1, t1 x2, t1 x3, t1 x4, t1 x5 ;
>>>	where x1.nval + x2.nval + x3.nval + x4.nval + x5.nval  = 7 ;
>>>	into cursor temp
>>>

>>>
>>>will give us all combinations equalling 7. However, there are many duplicated combinations which you don't need.
>>>
>>>The final cut restricts the value to its position in the list, and allows it the value of either itself or of 0. This will give the distinct combinations of the value you need.
>>>
>>>

>>>SELECT  x1.nval, x2.nval, x3.nval, x4.nval, x5.nval ;
>>>	FROM t1 x0, t1 x1, t1 x2, t1 x3, t1 x4, t1 x5 ;
>>>	where INLIST (x1.nid, 1, 0) ;
>>>          AND INLIST (x2.nid, 2, 0);
>>>          AND INLIST (x3.nid, 3, 0) ;
>>>          AND INLIST (x4.nid, 4, 0) ;
>>>          AND INLIST (x5.nid, 5, 0) ;
>>>          AND x0.nid = 0;
>>>	AND x1.nval + x2.nval + x3.nval + x4.nval + x5.nval  = 7 ;
>>>	into cursor temp
>>>

>>>
>>>This solution can be generalized quite easily, but you would run into SQL language limits with larger data sets.
>>>
>>>Hope you arrive at a solution for your specific need.
>>>
>>> Jay