Building Better SQL Queries
By Mo Budlong, January 01, 2003
In an awkwardly designed database, building SQL statements can be troublesome. The CSQL Class presented here builds a correctly formatted query from parameters entered one piece at a time, resulting in simple, easy-to-read SQL queries.
Building Better SQL Queries
Listing 6 Examples using CSQL
Function FormatDeptQuery(optional dept as long = 0)
Dim mainQ as new CSQL
' what tables do I need?
mainQ.Table = "Employee as e"
mainQ.Table = "Department as d"
mainQ.Table = "Employee as m" ' for managers
' How are they Related?
mainQ.Where = "e.dep_id = d.dept_id"
mainQ.Where = "d.manager_id = m.empl_id"
' What limits do I want on the data
' Use any passed department, otherwise there are no limits
If dept <> = 0 Then
mainQ.Where = "e.dept_id = " & CStr(dept)
End If
' What fields do I want and what column names
mainQ.Selct = "d.dept_id as Dept_ID"
mainQ.Selct = "d.dept_name as Department"
mainQ.Selct = "e.emp_id as Employee_Number"
mainQ.Selct = "e.emp_name as Employee"
mainQ.Selct = "m.emp_name as Manager"
' Sort by department if we are retrieving all departments
If dept = 0 Then
mainQ.Order = "d.dept_id"
End If
' and by Employee Name
mainQ.Order = "e.emp_name"
' Return the query string
FormatDeptQuery = mainQ.SelectQuery
End Function
'==================================================
' Format the query to insert department weekly totals
'==================================================
Function FormatDeptWeekly(Yr As Long, Wk As Long)
Dim mainQ As New CSQL
mainQ.InsertTable = "DeptTotals"
mainQ.InsertValues "year", "dd.year"
mainQ.InsertValues "week", "dd.week"
mainQ.InsertValues "dept_id", "dd.dept_id"
mainQ.InsertValues "revenue", "SUM(dd.revenue)"
mainQ.Table = "DeptDaily dd"
mainQ.Where = "dd.year = " & CStr(Yr)
mainQ.Where = "dd.week = " & CStr(Wk)
mainQ.Group = "dd.year"
mainQ.Group = "dd.week"
mainQ.Group = "dd.dept_id"
FormatDeptWeekly = mainQ.InsertQuery
End Function
