Nadya:

I sat in a boring meeting this morning and came up with this idea. I typed the results during my break, so please forgive me for the spaghetti style... The function GetSqlTables will return a comma-delimited list of tables included in a well-formed SQL statement. The table ames includes the database section if it was included in the SQL statement.

Please send me an updated if you clean this up.

Daniel


lcTest1 = 'SELECT Statconfigs.* FROM Statconfigs'
lcTest2 = 'SELECT Statconfigs.* FROM Statconfigs WHERE Statconfigs.configname = "somename"'
lcTest3 = 'SELECT Statconfigs.*, Statfields.*, Statfuncs.funcid, Statfuncs.funcname, ' + ;
' Statfuncs.funcgroup, Statfuncs.funcdef ' + ;
' FROM stats!statconfigs ' + ;
' INNER JOIN stats!statfields ' + ;
' LEFT OUTER JOIN stats!statfuncs ' + ;
' ON Statfields.f_functionid = Statfuncs.funcid ' + ;
' ON Statconfigs.configid = Statfields.configid ' + ;
' WHERE UPPER(Statconfigs.configname) = UPPER(?lcConfigName) ' + ;
' ORDER BY Statconfigs.configname, Statfields.fieldorder'
lcTest4 = ' Select cCode, cTown, tmpMonth.cYear, " " as cQtr, tmpMonth.cMonth, ' + ;
' Sum(iPrice) as YtdVol, Count(*) as YtdNum, ' + ;
' tmpMonth.cYear+tmpMonth.cMonth as tmpOrder ' + ;
' From tmpMonth, InputTable ' + ;
' Where InputTable.cYear = tmpMonth.cYear and ' + ;
' InputTable.cMonth between "01" and tmpMonth.cMonth ' + ;
' Group By cCode, cTown, tmpOrder '

lcTest5 = 'select ccode, town, cYr, "YR" as Period, UseGrp, ' + ;
' cYr+"YR" as tmpOrder, ' + ;
' count(price) as YtdNumSale, sum(price) as YtdVolSale ' + ;
' from InputTable, tblYears ' + ;
' where cYear=cYr ' + ;
' group by 1, 2, 5, 6 ' + ;
' union ' + ;
' select ccode, town, cYr, cQtr as Period, UseGrp, ' + ;
' cYr+cQtr as tmpOrder, ' + ;
' count(price) as YtdNumSale, sum(price) as YtdVolSale ' + ;
' from InputTable, tblQtrs ' + ;
' where cYear=cYr and qtr between "Q1" and cQtr ' + ;
' group by 1, 2, 5, 6 ' + ;
' union ' + ;
' select ccode, town, cYr, cMn as Period, UseGrp, ' + ;
' cYr+cMn as tmpOrder, ' + ;
' count(price) as YtdNumSale, sum(price) as YtdVolSale ' + ;
' from InputTable, tblMonths ' + ;
' where cYear=cYr and cmonth between "01" and cMn ' + ;
' group by 1, 2, 6, 5 ' + ;
' order by 1, 2, 3, 4, 5 ' + ;
' into cursor curYtdResult'

lcTest6 = ' select crYtd.ccode, crYtd.town, cYr, crYtd.Period, crYtd.UseGrp, ' + ;
' NumSales, VolSales, YtdNumSale, YtdVolSale ' + ;
' from curResult cr right join curYtdResult crYtd ' + ;
' on cr.ccode+cr.town+cr.cYear+cr.Period+cr.UseGrp = ' + ;
' crYtd.ccode+crYtd.town+crYtd.cYr+crYtd.Period+crYtd.UseGrp ' + ;
' group by 1,2,3,4,5 ' + ;
' into cursor curFinalResult'

Clear
? GetSqlTables(lcTest1)
? GetSqlTables(lcTest2)
? GetSqlTables(lcTest3)
? GetSqlTables(lcTest4)
? GetSqlTables(lcTest5)
? GetSqlTables(lcTest6)

Return

Function GetSqlTables
LParameters tcSQL

#Define True .T.
#Define False .F.

Local lnRetVal
Local lnCounter
Local llDiscard
Local lnEnd
Local lcExact
Local lnKeyWordCount
Local laKeywords[14]
Local lnLen
Local lnPos
Local lcSQL
Local lnStart
Local lnWhich

lcRetVal = ''

laKeywords[ 1] = ' SELECT '
laKeywords[ 2] = ' INTO '
laKeywords[ 3] = ' TO FILE '
laKeywords[ 4] = ' TO PRINTER '
laKeywords[ 5] = ' TO SCREEN '
laKeywords[ 6] = ' PREFERENCE '
laKeywords[ 7] = ' NOCONSOLE '
laKeywords[ 8] = ' PLAIN '
laKeywords[ 9] = ' NOWAIT '
laKeywords[10] = ' WHERE '
laKeywords[11] = ' GROUP BY '
laKeywords[12] = ' HAVING '
laKeywords[13] = ' UNION '
laKeywords[14] = ' ORDER BY '

lcExact = Set('Exact')
Set Exact On

lcSQL = Upper(tcSQL) + ' '
lcSQL = StrTran(lcSQL, '(', ' ')
lcSQL = StrTran(lcSQL, ')', ' ')
lcSQL = StrTran(lcSQL, ',', ' , ')

lnKeyWordCount = ALen(laKeywords)

Do While True
lnStart = At(' FROM ', lcSQL)
If lnStart > 0 Then
lcSQL = Ltrim(SubStr(lcSQL, lnStart + 5))
lnLen = Len(lcSQL)
lnEnd = lnLen + 1

For lnCounter = 1 To lnKeywordCount
lnPos = At(laKeywords[lnCounter], lcSQL)
If lnPos > 0 And lnPos < lnEnd Then
lnWhich = lnCounter
lnEnd = lnPos
EndIf
EndFor

If lnEnd < lnLen Then
lcFromClause = Left(lcSQL, lnEnd - 1)
Else
lcFromClause = lcSQL
EndIf

llDiscard = False
Do While True
lcWord = GetNextWord(@lcFromClause)

Do Case
Case Empty(lcWord)
*-- Finished
Exit

Case lcWord = 'FORCE'
*-- Ignore keyword

Case lcWord = ','
*-- Next word will be a table name
llDiscard = False

Case lcWord = 'ON'
*-- Next few words represent an expression.
llDiscard = True

Case lcWord = 'AS'
*-- Ignore keyword

Case Inlist(lcWord, 'LEFT', 'RIGHT', 'FULL', 'INNER', 'OUTER')
*-- Everything up to the JOIN keyword is
*-- to be discarded
llDiscard = True

Case lcWord = 'JOIN'
llDiscard = False

Otherwise
If Not llDiscard Then
*-- This word is a tablename; must set discard next word
*-- not a keyword because it will be a local alias
If At(',' + lcWord, lcRetVal) = 0 Then
lcRetVal = lcRetVal + ',' + lcWord
EndIf

llDiscard = False
EndIf
EndCase
EndDo
Else
Exit
EndIf
EndDo

If Not Empty(lcRetVal) Then
lcRetVal = Substr(lcRetVal, 2)
EndIf


Set Exact &lcExact


Return ( lcRetVal )


Function GetNextWord(tcString)

Local lcRetVal
Local lnCounter
Local lnLen

lcRetVal = ''
tcString = LTrim(tcString) + ' '
lnLen = Len(tcString)

For lnCounter = 1 To lnLen
lcChar = SubStr(tcString, lnCounter, 1)
If lcChar == ' ' Then
tcString = Substr(tcString, lnCounter)
Exit
Else
lcRetVal = lcRetVal + lcChar
EndIf
EndFor


Return ( lcRetVal )