L. Moronvalle L. Moronvalle - 3 months ago 23
SQL Question

Aggregate SQL query in VB.net

I have the following query:

INSERT INTO tbl_GroupInvoices
SELECT tbl_Invoices.CustomerName AS CustomerName,
tbl_Invoices.CountryCode AS CountryCode,
tbl_Invoices.[Group] AS [Group],
SUM((tbl_Invoices.PageReferenceVolume / tbl_Invoices.DaysInMonth) * tbl_Invoices.ActiveDaysInMonth) AS PageReferenceVolume,
SUM(tbl_Invoices.BlackPages + tbl_Invoices.ColorPages) AS ActualPageVolume,
ActualPageVolume / PageReferenceVolume AS UsageRate
FROM tbl_Invoices
GROUP BY tbl_Invoices.CustomerName, tbl_Invoices.CountryCode, tbl_Invoices.[Group]


This is working perfectly fine when I execute it directly in Ms Access 2013.
but when embedded in my VB.net code I have an error message saying:

Your query does not include the specified expression
'ActualPageVolume/PageReferenceVolume' as part of an aggregate function.

Here is my vb code

cmd.Connection = mdlLocalAccDB.accessConn
cmd.CommandType = CommandType.Text

Try
sQuery = String.Empty
sQuery = sQuery & "INSERT INTO tbl_GroupInvoices "
sQuery = sQuery & "SELECT tbl_Invoices.CustomerName AS CustomerName, "
sQuery = sQuery & "tbl_Invoices.CountryCode AS CountryCode, "
sQuery = sQuery & "tbl_Invoices.[Group] AS [Group], "
sQuery = sQuery & "SUM((tbl_Invoices.PageReferenceVolume / tbl_Invoices.DaysInMonth) * tbl_Invoices.ActiveDaysInMonth) AS PageReferenceVolume, "
sQuery = sQuery & "SUM(tbl_Invoices.BlackPages + tbl_Invoices.ColorPages) AS ActualPageVolume, "
sQuery = sQuery & "ActualPageVolume / PageReferenceVolume AS UsageRate "
sQuery = sQuery & "FROM tbl_Invoices "
sQuery = sQuery & "GROUP BY tbl_Invoices.CustomerName, tbl_Invoices.CountryCode, tbl_Invoices.[Group] "
cmd.CommandText = sQuery
QueryReturn = cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox("PrepareInvoicing: Invoicing step 6" & vbCrLf & ErrorToString())
Exit Sub
End Try


Any ideas ? (except it would be better to use parameters instead of string concatenations.

Answer

Normally, in SQL, you can't refer to column aliases in other parts of the same select clause where they are defined. I'm surprised that Access supports that when running queries directly.

This would be the more usual way of running that query (I just replaced ActualPageVolume / PageReferenceVolume with their corresponding expressions):

INSERT INTO tbl_GroupInvoices 
    SELECT tbl_Invoices.CustomerName AS CustomerName, 
           tbl_Invoices.CountryCode AS CountryCode, 
           tbl_Invoices.[Group] AS [Group], 
           SUM((tbl_Invoices.PageReferenceVolume / tbl_Invoices.DaysInMonth) * tbl_Invoices.ActiveDaysInMonth) AS PageReferenceVolume, 
           SUM(tbl_Invoices.BlackPages + tbl_Invoices.ColorPages) AS ActualPageVolume, 
           SUM(tbl_Invoices.BlackPages + tbl_Invoices.ColorPages) / SUM((tbl_Invoices.PageReferenceVolume / tbl_Invoices.DaysInMonth) * tbl_Invoices.ActiveDaysInMonth) AS UsageRate 
    FROM tbl_Invoices 
    GROUP BY tbl_Invoices.CustomerName, tbl_Invoices.CountryCode, tbl_Invoices.[Group]

Hopefully that works better.

And concerning your little note at the end:

except it would be better to use parameters instead of string concatenations

Your current query doesn't have anything that requires parameter binding. The way you have it now is perfectly ok.