David,

Yes, it would be much better to tweak SQL before and not after.

E.g. you would have program like this PrepareSQLFrom

lcStr=''
PrepareSQLFrom(m.FirstTable, @lcStr)
PrepareSQLFrom(m.SecondTable, @lcStr)
................................

PrepareSQLFrom
lparameters tcTableName, tcStr
if vartype(m.tcTableName)<>"C" or not UseTble(m.tcTableName,'WorkTable')
   && error
   return .f.
endif
if vartype(m.tcStr)<>"C"
   tcStr = ""
endif
select WorkTable
lnFields = afields(laFields)
local lcStr, lnI, lcTable
lcStr = ""
lcTable = juststem(m.tcTableName)

for lnI = 1 to m.lnFields
    if "."+laFields[m.lnI,1]+" " $ m.tcStr+" " 
       && Field is already in the string, do nothing
    else
        lcStr = m.lcStr + " ;"+ chr(13)+" "+ ;
                m.lcTable+"."+laFields[m.lnI,1]
    endif
next
tcStr = m.tcStr + m.lcStr
return

This code is from the top of my head and not tested.


>I am having a bad day. hit reply before I finished my thought last time. I meant to say: I might try creating some methods as follows:
>
>** for each parameter, before 1st slash is table name to create select
>** string for, next is what fields to include from table, after 2nd slash ***is 'exclude list' ie list of fields to exclude from the statement.
>lcfromclause=osqlcmd.vsqlfrom('sosord/*/ccustno,csono', 'sostrs/*/')
>lcsqlcommand=lcfromclause+ 'rest of the sql string'
>&lcsqlcommand
>
>I have already written a little routine that grabs all the fields from a given table or view - and makes a formatted string - and puts it onto the clipboard. I could modify it for use in thie vsqlfrom() method.
>The code for it is as follows:
>
>Lparameters tctable
>*set step on
>If type('tctable')='C'
> If used(tctable)
> Select &tctable
> Else
> Select 0
> Use &tctable shared
> Endif
>Else
> tctable=upper(alias())
> if empty(tctable)
> select 0
> use ?
> endif
>
>Endif
>If not empty(tctable)
> cstring=''
> lnfcount=fcount(tctable)
> For i = 1 to lnfcount
> cfieldname=field(i)
> If i> cstring=cstring+"'"+tctable+"."+ cfieldname+", "+"'" +" + ;" + chr(13)+chr(10)
> Else
> cstring=cstring+"'"+tctable+"."+ cfieldname+ "'"
> Endif
> Endfor
> _Cliptext=cstring
>
> Messagebox('column string for ' + tctable + ' was placed on the clipboard')
> use in (tctable)
>
>Else
> Messagebox('No table was selected')
>
>Endif
>********* end code by DSS
>
>
>
>>This is not my code, but the code by my colleague:
>>

>>		IF USED(justname )
>>			SELECT &justname
>>		ELSE
>>			USE (ojc.curtable) in 0 EXCL  && Reopen table to do the alter table
>>			SELECT &justname
>>			*wait window 'table not there'  && nl  temporary handling.
>>		ENDIF
>>	lnumflds=afields(fldlistall,justname )  && Get the list of fields in array fldlist
>>	DIMENSION  lfldlist(lnumflds)
>>	idx=0
>>		
>>	FOR i=1 to lnumflds
>>		IF '_A'$fldlistall(i,1)
>>			idx=idx+1
>>			lfldlist(idx)=fldlistall(i,1)
>>		ENDIF
>>		
>>	ENDFOR
>>	lnumchgflds=idx
>>	IF lnumchgflds>0
>>		*Wait window nowait 'Number of unclear fields are '+ALLTRIM(STR(lnumchgflds))
>>		starttime = seconds()
>>		FOR i=1 to lnumchgflds
>>			newname=left(lfldlist(i),AT('_A',lfldlist(i))-1)
>>			ALTER TABLE (tblname) RENAME COLUMN (lfldlist(i)) TO (newname)
>>		
>>		ENDFOR
>>
>>		*wait window nowait "ALTRTABL: Total time = " + str(m.endtime - m.starttime, 10, 4)
>>		endtime = seconds()
>>		*wait window nowait "ALTRTABL: Total time = " + str(endtime - starttime, 10, 4)
>>	ENDIF

>>
>>This code works on tables only. I guess, you can adjust it to work with cursors.
>>
>>
>>>That sounds good Nadya! I was considering doing something like that. It might save me a lot of time. I'd like to see the code. I assume you are using some trick like 'use again', to get the cursor to be updatable?
>>>I will email you.
>>>
>>>Does it just handle _a or does it handle multiple occurrences - _b _c and also, I have a vague memory that vfp handles things differently if the column name is 10 chars???
>>>
>>>>For cases like this we have a special post-processing altering table structure code. It changes all columns with _a to just fieldname. Send me a letter to work, I'll send you the code on Monday.
>>>>
>>>>Could be a Wish List for Toledo.
>>>>
>>>>>In sql server/transact sql when a query such as:
>>>>>
>>>>>select * from sosord, sostrs where sosord.csono=sostrs.csono
>>>>>
>>>>>is executed, the key value appears once in the result set as csono. In vfp you end up with csono_a, and csono_b.
>>>>>
>>>>>If I want my cursor result to be 'clean' and not have the underscores, then I need to explicitly name all the columns, and include the key from only one of the tables, once. But this results in very long query statements. I wrote a little routine to retrieve all of the column names for a given table into a formatted select string, and put it on the clipboard. This helps speed things up, but I still would be able to use select *.
>>>>>
>>>>>Is there any syntax, or trick that I can use to shorten my select statements. E.g. I wish there was a keyword like
>>>>>
>>>>>select * from sosord, sostrs where sosord.csono=sostrs.csono NODUPLICATEKEYVALUES
>>>>>
>>>>>Is there a specific reason why VFP's sql is implemented so differently than TRANSACT-SQL in such a fundamental area?

If it's not broken, fix it until it is.


My Blog