EXCEL VBA WHERE

Connectivity
New Member

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, 

1 REPLY
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