Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

coalesce function causing invalid column error

Status
Not open for further replies.

richrich05

Programmer
Aug 15, 2005
24
0
0
US
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)
 
Add it to the group by clause

"NOTHING is more important in a database than integrity." ESquared
 
To add to what SQLSister said...

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

Try changing it to:
Code:
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
 
grrr i hate it when i forget simple things like adding coalesce else where in my statement.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top