>I am trying to make some of our code compliant with VFP 9.0. The primary update that I am applying is to add a MIN() to every field in the SELECT that is not in the GROUP BY. Our SQL statements are built so I have the text strings, but I am not very proficient at text manipulation.
>
>cSelect='obligor,obligation,unit,name,addr1'
>cGroupby = 'obligor, obligation'
>
>So I would want to change cSelect to
>cSelect='obligor,obligation,MIN(unit),MIN(name),MIN(addr1)'
>
>How is an efficient way to add the MIN() to any cSelect field that is not in the cGroupby?
>
>Thanks
>
>Brenda

*** Make both to be in upper or lower case
cSelect  = UPPER('obligor,obligation,unit,name,addr1')
cGroupby = UPPER('obligor, obligation')

*** Remove all fields that are in GROUP BY
lnLines = ALINES(laGrpBy,cGroupby,1,[,])
FOR lnFor = 1 TO lnLines
    cSelect = STRTRAN(cSelect,laGrpBy[lnFor],[])
NEXT

*** Make all the rest to be in MIN()
lnLines = ALINES(laSelect,cSelect,1+4,[,])
cSelect = []
FOR lnFor = 1 TO lnLines
    cSelect = cSelect + [,] + [MIN(]+laSelect[lnFor]+[) AS ]+laSelect[lnFor] 
NEXT
cSelect = cGroupby + cSelect
MESSAGEBOX(cSelect)