coalesce function causing invalid column error

[richrich05]

Hi i'm running into a weird issue when i add a coalesce function to my select statement. I'm getting a error that saids "Column 'NPSC_Reporting..TBL_SupplierInvoices.SI_InputDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. " anyone out there know how to fix this issue?




StrSql = StrSql & " SELECT coalesce(CONVERT(varchar, SI_inputdate, 101),'Total') as DateRange, SUM(CASE WHEN si_enterby = 'DTS' THEN 1 ELSE 0 END) AS elec,"
StrSql = StrSql & " SUM(CASE WHEN NOT si_enterby = 'DTS' THEN 1 ELSE 0 END) AS manual, SUM(CASE WHEN SI_Reconstatus = 'matched' THEN 1 ELSE 0 END) "
StrSql = StrSql & " AS matched, SUM(CASE WHEN NOT SI_Reconstatus = 'Matched' THEN 1 ELSE 0 END) AS unmatched, SUM(CAST(REPLACE(REPLACE(SI_CostEach, ')', "
StrSql = StrSql & " ''), '(', '') AS Money)) AS Dailybal"
StrSql = StrSql & " FROM NPSC_Reporting..TBL_SupplierInvoices"
StrSql = StrSql & " WHERE CONVERT(varchar, SI_inputdate, 101) >= cast('" & (DateStart) & "' as datetime)"
StrSql = StrSql & " AND CONVERT(varchar, SI_inputdate, 101) <= cast('" & (DateEnd) & "' as datetime)"
StrSql = StrSql & " GROUP BY CONVERT(varchar, SI_inputdate, 101) with rollup"
StrSql = StrSql & " ORDER BY CONVERT(varchar, SI_inputdate, 101)

 

[SQLSister]

Add it to the group by clause

"NOTHING is more important in a database than integrity." ESquared

 

[SQLBill]

To add to what SQLSister said...

You GROUP "CONVERT(varchar, SI_inputdate, 101)" but that's not what you SELECT.

Try changing it to:

GROUP BY coalesce(CONVERT(varchar, SI_inputdate, 101),'Total') WITH ROLLUP

-SQLBill


The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[richrich05]

grrr i hate it when i forget simple things like adding coalesce else where in my statement.