Parsing a string as arithmetic

[Gruuuu]

Here's the skinny: I'm creating a database tool in an excel book that can automate some queries against an external Database (Teradata, not that it matters).
My basic setup is:

I have a list of query files in the excel book.

Each entry in the list is the name of a .sql file.

Each query, before being edited to work with my tool, runs just fine, and we've been using them for ages.

However, I've made a few changes. Wherever there is a part of the query which will or might change, I have replaced that section with a string that looks like "$THIS$".

I've written some VBA code that opens the .sql as a textstream, finds instances of text delimited by "$", searches a table I've set up for whatever text it finds, and then returns a comma delimited list of every item below the text that it finds.

Example:

$ONE$    $TWO$
1        1
         2

If the .sql file has $ONE$ in it, it returns "1".
If it has $TWO$, it returns "1, 2"

After it returns this text, it replaces the original string.

Then, it updates the CommandText of a querytable on another sheet with the altered query text, and refreshes.

After that, it copies and saves the results off to a text file.

All of this works as intended... My biggest problem, though, comes from formatting the .sql files so that the queries don't bomb. And my main culprit there is when there's some simple, 2nd grade arithmetic in the query, which we mostly use on dates.

Example:

WHERE table.datefield >= $WE_DATE$ - ($FISCAL_WEEKS$ * 4) -7

I'm sure I can alter my code to ignore them. But I'm greedy. I want to calculate the date fields using these formula.

Here's the code that searches the query:

Sub runqueries()

Dim strQryName As Variant
Dim qt As QueryTable
Dim qryFile As Workbook

Set qt = Sheets("QueryTable").QueryTables(1)

Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False


For Each strQryName In Sheets("Control").Range("queries")
    qt.CommandText = fillquery(strQryName)
    qt.Refresh
    Sheets("QueryTable").UsedRange.Copy
    Set qryFile = Workbooks.Add
    qryFile.Sheets(1).Range("A1").PasteSpecial xlPasteValues
    qryFile.SaveAs "X:\Path\" & Left(strQryName, Len(strQryName) - 4) & ".txt", xlTextWindows
    qryFile.Close
Next strQryName

Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True

Set qt = Nothing
Set qryFile = Nothing
End Sub

and the fillquery function:

Function fillquery(ByVal queryfile As String)

Dim fs, ts As Object
Dim strQry, strQryLine As String
Dim qrySplit As Variant

'Debug.Assert 0
Set fs = CreateObject("Scripting.FileSystemObject")
Set ts = fs.OpenTextFile("X:\Path2\" & queryfile, 1)

While ts.AtEndOFStream <> True
    strQryLine = ts.ReadLine
    If InStr(strQryLine, "$") Then
        ReDim qrySplit(0)
        qrySplit = Split(strQryLine, "$")
        For i = 0 To UBound(qrySplit)
            If Len(qrySplit(i)) < 50 Then
                If qrySplit(i) = UCase(qrySplit(i)) Then
                    If Len(qrySplit(i)) > 4 Then
                        qrySplit(i) = getList("$" & qrySplit(i) & "$")
                    End If
                End If
            End If
        Next i
        strQry = strQry & Join(qrySplit, " ") & " "
    Else
        strQry = strQry & strQryLine & " "
    End If
Wend

ts.Close
fillquery = strQry

Set ts = fs.CreateTextFile("X:\Path2\test.sql")
ts.write fillquery
ts.Close

Set fs = Nothing
Set ts = Nothing

End Function

So I'm looking for a way to capture formula strings, and parse them along with the date values.

 

[PHV]

What is getList ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gruuuu]

Ah. My Bad. Forgot that was in there.

Function getList(ByVal listname As String)

Dim list As Variant
Dim lists As Worksheet
Dim listHead As Range
Dim i As Integer
'Debug.Assert 0

Set lists = Sheets("Lists")

Set listHead = lists.Range("1:1").Find(listname)
If listHead Is Nothing Then
    getList = listname
    Exit Function
End If
getList = listHead.Offset(1, 0).Value
For i = 2 To Range(listHead.Offset(1, 0), listHead.End(xlDown)).Rows.count
    getList = getList & ", " & listHead.Offset(i, 0).Value
Next i

End Function

 

[PHV]

So, what is the replaced string of the following ?
WHERE table.datefield >= $WE_DATE$ - ($FISCAL_WEEKS$ * 4) -7

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gruuuu]

$WE_DATE$ is replaced with a date. Currently I have this as a TEXT formatted string in this format 'YYYY-MM-DD'. I can always leave it as a date field on the excel page and format it within the script. In fact I'm certain I will do exactly that to save myself conversion work. The final date that goes in the query must be formatted in this way.

also $FISCAL_WEEKS$ would be replaced by a small integer, between 1 and 53

 

[PHV]

So, format the value in the excel page as expected by the SQL engine, eg #yyyy-mm-dd#
and replace this:
getList = listHead.Offset(1, 0).Value
with this:
getList = listHead.Offset(1, 0).Text

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

WHERE table.datefield >= #2010/06/21# - ($FISCAL_WEEKS$ * 4) -7

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

Ah, was unaware of the .Text returning the info as formatted.
But that would actually countermand my need, I think?

Ultimately I'm trying to take the date value, and perform the calculations in the query to the date (inside VBA!), and replace the whole formula with the new date (to save the query server the processing load).

If I get the .Text value, I wouldn't be able to perform these calculations in VBA anymore, right?

Ok, I was just thinking... If I used another set of symbols to surround my formulas, "@" for example, I could search for those first, parse the dates, do the math, and then return it.
So my .sql line might look like this:

WHERE table.datefield >= @$WE_DATE$ - ($FISCAL_WEEKS$ * 4) -7@

and (assuming WE_DATE returns 2010/06/26 and FISCAL_WEEKS returns 12)
my results would be:

...
2010/06/26 - (12 * 4) -7
...
2010/06/26 - 41
...
[b]2010/05/16[/b]

But, ugh. This means that my script will need to be recursive, and know the order of operations. Man this is gonna be fun.

 

[PHV]

save the query server the processing load
Should be negligible, IMHO.

I'd stick with properly formatted cells...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gruuuu]

Should be negligable, IMHO

I thought so originally, as well. at least not worth the effort to put into it.

But after testing a few queries with and without the date changes, I noticed a significant decrease in processing time.

If the date field is on a table of item movement, the potential for processing is huge, considering the millions of item movement I would have.
Depending on how the optimizer works on the query, it could be doing the calculation for every row in the table. This is especially true when calculations are done TO the date field.

Additionally, I suppose it's a habit from my database days, to do as much processing client-side as is reasonable, and to optimize the balance between I/O and Processing.