|
|
|
Forum Newbie
Group: Forum Members
Last Login: 6/9/2008 11:56:22 PM
Posts: 2,
Visits: 8
|
|
Hi guys,
I have the below to run queries from comments in excel.
This works for all of the queries I run except for those with volatile tables.
I tried adding "(InStr(UCase(sCom), "CREATE VOLATILE TABLE") > 0) Or (InStr(UCase(sCom), "INSERT INTO") > 0) Or (InStr(UCase(sCom), ";CREATE VOLATILE TABLE") > 0) Or (InStr(UCase(sCom), ";INSERT INTO") > 0)" to get it to work, but it didn't.
I added and tested "(InStr(UCase(sCom), "EXEC") > 0)" successfully for macros.
I then tried creating a macro but that came up with an error as well, which I've already read up about saying that Teradata can't create macros with volatile tables included, which I can't understand why.
I did try writing the query so that it has no volatile tables, but I can't define a limited enough source of data for it not to run out of spool space.
Is there any excel add in out there that I can use to get data from our data warehouse with queries that contain volatile tables? My VB knowledge is very limited at best and I can't modify this.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 6/9/2008 11:56:22 PM
Posts: 2,
Visits: 8
|
|
Option Explicit
Option Base 1
Sub RunSQLComments()
Dim iName As Integer
Dim r As Range
Dim sCom As String, sCom2 As String
Dim i As Integer, sVar As String, varValue As Variant, bInVar As Boolean
Application.Cursor = xlWait
Application.StatusBar = "In Progress..."
'For iName = 1 To ActiveWorkbook.names.count
'Set r = ActiveWorkbook.names(iName).RefersToRange
Set r = Selection.Range("A1")
sCom = ""
On Error Resume Next
sCom = r.Comment.Text
On Error GoTo 0
If InStr(sCom, ":") > 0 Then
sCom = right(sCom, Len(sCom) - InStr(sCom, ":"))
End If
If (InStr(UCase(sCom), "SEL ") > 0) Or (InStr(UCase(sCom), "SELECT") > 0) Or (InStr(UCase(sCom), "CREATE VOLATILE TABLE") > 0) Or (InStr(UCase(sCom), "INSERT INTO") > 0) Or (InStr(UCase(sCom), ";CREATE VOLATILE TABLE") > 0) Or (InStr(UCase(sCom), ";INSERT INTO") > 0) Or (InStr(UCase(sCom), "EXEC") > 0) Then
'It's a SQL command
'Look for variables and substitute in...
sVar = ""
sCom2 = ""
bInVar = False
For i = 1 To Len(sCom)
If Not bInVar = True Then
If Mid(sCom, i, 1) = "[" Then
bInVar = True
sVar = ""
Else
sCom2 = sCom2 & Mid(sCom, i, 1)
End If
Else
If Mid(sCom, i, 1) <> "]" Then
sVar = sVar & Mid(sCom, i, 1)
Else
varValue = "NULL"
On Error Resume Next
varValue = Range(sVar)
On Error GoTo 0
sCom2 = sCom2 & CStr(varValue)
bInVar = False
End If
End If
Next
Call runSQL1(sCom2, r)
End If
'Next
'Format the 1st row with the sub 'Tidy headings'
Range(ActiveCell, ActiveCell.End(xlToRight)).Select
Call TidyQueryHeadings
Range("A1").Select
Application.Cursor = xlDefault
Application.StatusBar = False
End Sub
Sub runSQL1(sCommand As String, destCell As Range)
Dim userID As String
userID = "user"
Dim userPass As String
userPass = "password"
Const connDSN As String = "database"
Const connDB As String = ""
Dim bQueryThere As Boolean, sTemp As Variant
bQueryThere = True
On Error Resume Next
sTemp = destCell.QueryTable.Name
If Err > 0 Then bQueryThere = False
On Error GoTo 0
On Error Resume Next
If Not bQueryThere Then
With ActiveSheet.QueryTables.Add( _
Connection:="ODBC;DSN=" & connDSN & ";UID=" & userID & ";PWD=" & userPass & ";DATABASE=" & connDB & ";", _
Destination:=destCell)
.Sql = longStringToArray(sCommand)
.FieldNames = True
.RefreshStyle = xlOverwriteCells
.RowNumbers = False
.FillAdjacentFormulas = True
.RefreshOnFileOpen = False
.HasAutoFormat = False
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = True
End With
Else
With destCell.QueryTable
.Connection = "ODBC;DSN=" & connDSN & ";UID=" & userID & ";PWD=" & userPass & ";DATABASE=" & connDB & ";"
.Sql = longStringToArray(sCommand)
.Refresh False
End With
End If
If Err > 0 Then
MsgBox "Data Access Error: " & vbCrLf & Err.Description, vbOKOnly
End If
On Error GoTo 0
End Sub
Function longStringToArray(sLong As String) As Variant
'Chop a string into an array of bits so can be passed in as SQL argument
Dim temp() As String
Dim i As Integer, iLine As Integer, iCol As Integer
i = 0
iLine = 0
iCol = 256
Do While i < Len(sLong)
i = i + 1
iCol = iCol + 1
If iCol > 255 Then
iLine = iLine + 1
ReDim Preserve temp(iLine)
iCol = 1
End If
temp(iLine) = temp(iLine) & Mid(sLong, i, 1)
Loop
longStringToArray = temp
End Function
|
|
|
|