EXCEL VBA WHERE

Connectivity

EXCEL VBA WHERE

Hello, 

 

Trying setup report which would pull data from Teradata based on query which is already written but somehow i need pass two parameters to query (ie year , week) stored in excel. Did search forum and i can't find anything close to what I'm trying find out. 

 

Would be there any chance that someone can share some examples how ODBC connection setup is looking and how pass parameter to filer the query? 

 

Thanks, 

2 REPLIES
Junior Supporter

Re: EXCEL VBA WHERE

Pawel,

 

Here example using a single parameter, executing in Excel.

 

##Excel VB MACRO##
Sub RPT_Memory()
Rem To Retrieve Data from Teradata Database
Sheets("L3 Memory").Rows("3:1000").Select
Selection.ClearContents
Sheets("L3 Memory").Cells(1, 5).Select
ODBCnm = Sheets("Config").Cells(2, 3).Value
DateParm = Sheets("Memory").Cells(1, 6).Value
RunMacro = "exec PDCRINFO.RPT_Memory (" & DateParm & ");"
With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=" & ODBCnm & ";;;; " _
, Destination:=Sheets("Memory").Range("A3"))
.CommandText = RunMacro
.Name = "RptMem"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

##SQL MACRO##
REPLACE MACRO PDCRINFO.RPT_Memory (RunDate DATE) AS (
LOCKING ROW FOR ACCESS      --EXEC PDCRINFO.RPT_Memory (CURRENT_DATE);
SEL TheDate "Date"
,COALESCE(P.DescShort ,'*Error*') AS "Time"
,Max(MaxSwapSec) "Max Swap Sec" ,AVG(AvgSwapSec) "Avg Swap Sec"
,MIN(MinMemFree) "Min Mem Free" ,AVG(AvgMemFree) "Avg Mem Free"
FROM PDCRDATA.Hist_Memory A
LEFT OUTER JOIN PDCRDATA.Tool_periods P
ON A.TheHour BETWEEN P.StartHour AND P.EndHour-1
WHERE TheDate BETWEEN :RunDate-31 AND :RunDate
GROUP BY 1,2,P.PeriodID
ORDER BY TheDate DESC, P.PeriodID DESC;
);

 

Teradata Frank, Certified Master

Re: EXCEL VBA WHERE

Hello, 

 

I did look on your code but for some reason it's not working when addng to my VBA code which is slightly defferent. 

Field to which ConcatSQL is reffering holds "John Smith" without quotation marks as value.

 

 

Dim ConcatSQL As String
ConcatSQL = Sheets("LogIn").Cells(8, 3).Value

QueryA = "select *  from database.table WHERE Name = " & ConcatSQL & "   

cmdSQLData.CommandText = QueryA
cmdSQLData.CommandType = adCmdText
cmdSQLData.CommandTimeout = 0
MsgBox (QueryA)

Set rs = cmdSQLData.Execute()
Row = 1
rs.MoveFirst ' it's falling here
Do While (rs.EOF = False And rs.BOF = False)
p = rs.GetRows(1)

 

 

When running this i can see that SQL string is exactly as in SQL Assistant however I'm getting bellow error message. 

 

Capture.PNG

 

What i'm actually doing wrong as i did spend last hour without any joy.