Ahh, I see now. Like points on a map of how to get there from here. I can't do it in 1 SQL, but the following worked for me:
lparameter lcStart
local lcString
lcString = lcStart
select a.fld1+b.fld2 as 'MapResult' from test a, test b ;
where a.fld1=b.fld1 and a.fld1 = lcStart ;
into cursor crsResult
locate
do while !eof('crsResult')
lcString = lcString + right(crsResult.MapResult, 1)
lcStart = right(crsResult.MapResult, 1)
select a.fld1+b.fld2 as 'MapResult' from test a, test b ;
where a.fld1=b.fld1 and a.fld1 = lcStart ;
into cursor crsResult
locate
enddo
clear
?
? lcString
return lcString
Just pass the starting point,
DO MyProg with '7' I created a table with the 2 fields as character for simplicity, then inserted all your example values. Use numerics and convert to strings as needed.
>Hi Guys,
>
>>I do not understand what kind of criteria you are using to match up columns and values. I do not see a pattern for the return values. long weekend? :^)
>
>I suppose I could explain that better. Say you want the end result of 7
>
>7 goes to 6
>6 goes to 4
>4 goes to 1
>and 1 goes to 0
>
>You'd get 76410, or backwards 01467
>
>f1 f2
>----------
>1 0
>2 1
>3 2
>4 1
>5 2
>6 4
>7 6
>Into somehting like this:
>
01
>012
>0123
>0125
>014
>0146
>01467
>
>I was using this to create an order of a map of objects (which happened to be stored in a table). Say 1 is the first item, and below that are 2 and 4. 3 and 5 are subs of 2 and 7 is under 6 which is under 4. The numbers stand for the creation number. So to order my result by the line of replies, I will get the right order, but I couldn't find a way to build that string correctly with SQL, and thought someone might know.
>
>Anyway, I was told that I should probably not do it this way, and assign a sequence number when the record is created. Storing the numbers could create huge huge strings, imagine you need to plan for about 1000 shooting low. Then 01467 would need to be 00000001000400060007, and thats only a few items deep.
>
>Does this make any sense?
Mark McCasland
Midlothian, TX USA