Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gschwab
Starting Member
7 Posts |
 Posted - 2013-06-14 : 12:50:24
|
| I'm trying to create a positive pay file to send to a bank. I've been able to create the detail record based on the file layout, but I'm hung up on the control record/trailer record. One of the fields is the Check Date and the length needs to be 6 characters in MMDDYY. This is what I came up with:REPLACE (CONVERT(VARCHAR(10), p.CheckDate, 1), '/', '')But I'm getting this when I try to execute the query:Column 'dbo.Payment.CheckDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.Anybody know what I'm missing here?Thanks! |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-14 : 12:54:45
|
quote:
Originally posted by gschwab
I'm trying to create a positive pay file to send to a bank. I've been able to create the detail record based on the file layout, but I'm hung up on the control record/trailer record. One of the fields is the Check Date and the length needs to be 6 characters in MMDDYY. This is what I came up with:REPLACE (CONVERT(VARCHAR(10), p.CheckDate, 1), '/', '')But I'm getting this when I try to execute the query:Column 'dbo.Payment.CheckDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.Anybody know what I'm missing here?Thanks!
There are aggregations (SUM, AVG etc.) and GROUP BY clause in your query. The rule is that if you have aggregations, any column that is in the select list outside of the aggregation expressions must be included in the GROUP BY clause. So add the same expression that you posted REPLACE (CONVERT(VARCHAR(10), p.CheckDate, 1), '/', '') as the last item in the GROUP BY clause.Now, that may change the results - you may get more number of rows. Compare the two result sets and you will see why that happens. |
 |
|
|
|
|
|
|
|
adding a date to a control record