SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 adding a date to a control record
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gschwab
Starting Member

7 Posts

Posted - 06/14/2013 :  12:50:24  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 06/14/2013 :  12:54:45  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000