![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 20 total |
![]() |
Sun, Mar 21 2010 2:50 PM | Permanent Link |
Roy Lambert NLH Associates ![]() | Tim
I'm getting this error ElevateDB Error #700 An error was found in the statement at line 2 and column 1 (Invalid expression "_Forename" found, non-aggregated column references not allowed) with this query. SELECT _Forename+' '+_Surname+' ('+CAST(COUNT(_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16))) AS VARCHAR(3))+')', CAST(_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16)) AS VARCHAR(80)), _fkCompanies FROM Career JOIN Contacts C ON _fkContacts = C._ID GROUP BY _Surname,_Forename,_fkCompanies,_fkSites HAVING COUNT(_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16))) > 1 I can have _Forename+' '+_Surname and COUNT(_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16))) as separate columns but can't add them together. To me it doesn't make sense. Can you explain please. Roy Lambert |
Mon, Mar 22 2010 12:24 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Roy,
<< I can have _Forename+' '+_Surname and COUNT(_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16))) as separate columns but can't add them together. >> I don't understand what you mean by this statment - please show me the SQL statements that you're using - the ones that work and the ones that don't. In general, the error means that you can't use non-aggregated columns in the SELECT list that aren't in the GROUP BY. -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Mar 22 2010 12:42 PM | Permanent Link |
Roy Lambert NLH Associates ![]() | Tim
>I don't understand what you mean by this statment - please show me the SQL >statements that you're using - the ones that work and the ones that don't. The one I posted doesn't work. This one SELECT _Forename+' '+_Surname, CAST(COUNT(_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16))) AS VARCHAR(3)), CAST(_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16)) AS VARCHAR(80)), _fkCompanies FROM Career JOIN Contacts C ON _fkContacts = C._ID GROUP BY _Surname,_Forename,_fkCompanies,_fkSites HAVING COUNT(_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16))) > 1 Does. >In general, the error means that you can't use non-aggregated columns in the >SELECT list that aren't in the GROUP BY. It would appear that this is not general <vbg> Roy Lambert |
Mon, Mar 22 2010 5:09 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | Roy,
You could use derived a derived table to get what you want. Something like this should work. SELECT _Surname + ' ' + _Forename + ' ' + CAST(_fkCompanies AS VARCHAR(3)) + CAST( _fkSites AS VARCHAR(3)) + CAST(X1 AS VARCHAR(3)) FROM ( SELECT _Surname, _Forename, _fkCompanies, _fkSites, CAST (COUNT(*) AS VARCHAR(3)) AS X1 FROM Career C1 GROUP BY _Surname, _Forename, _fkCompanies, _fkSites ) AS T1 You could also replace HAVING COUNT(_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16))) > 1 with HAVING COUNT(*) > 1 since you are grouping on the columns contained in the COUNT expression. Richard Harding |
Tue, Mar 23 2010 4:09 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Richard
>You could use derived a derived table to get what you want. Something like this should work. > >SELECT > _Surname + ' ' + _Forename + ' ' + > CAST(_fkCompanies AS VARCHAR(3)) + > CAST( _fkSites AS VARCHAR(3)) + > CAST(X1 AS VARCHAR(3)) > FROM ( > >SELECT > _Surname, _Forename, _fkCompanies, _fkSites, > CAST (COUNT(*) AS VARCHAR(3)) AS X1 > FROM Career C1 > GROUP BY _Surname, _Forename, _fkCompanies, _fkSites ) AS T1 With some tweaking (to allow for table structure) that could work, but the original question still stands. >You could also replace >HAVING COUNT(_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16))) > 1 > >with HAVING COUNT(*) > 1 since you are grouping on the columns contained in the COUNT expression. Something else I didn't know. Roy Lambert |
Tue, Mar 23 2010 12:39 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Roy,
<< The one I posted doesn't work. This one >> Yes, but you're not "combining" columns there - you have a separate _Forename+' '+_Surname expression and then a separate CAST(COUNT()) expression. The _Forename+' '+_Surname is not in the GROUP BY, so EDB is complaining. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Mar 23 2010 12:41 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Richard,
<< You could use derived a derived table to get what you want. >> As an aside, you'll be happy to know that 2.03 B10 finally fixes the global derived table naming issues - you can now use whatever name you want and the scope is local to the current SQL statement, not global for the entire session. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Mar 24 2010 3:39 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Tim
>Yes, but you're not "combining" columns there - you have a separate >_Forename+' '+_Surname expression and then a separate CAST(COUNT()) >expression. The _Forename+' '+_Surname is not in the GROUP BY, so EDB is >complaining. I'm still lost (probably being thick). "(Invalid expression "_Forename" found, non-aggregated column references not allowed" Is what I'm told. But GROUP BY _Surname,_Forename,_fkCompanies,_fkSites So _Forename is in the GROUP BY. The COUNT expression isn't in the GROUP BY so is that what's really being complained about that I'm trying to catenate two items that are in the GROUP BY with one that isn't? If so it still doesn't make sense to me. I can list the columns so why can't I combine the output into a single column? Roy Lambert |
Wed, Mar 24 2010 1:08 PM | Permanent Link |
John Hay | Tim
> Yes, but you're not "combining" columns there - you have a separate > _Forename+' '+_Surname expression and then a separate CAST(COUNT()) > expression. The _Forename+' '+_Surname is not in the GROUP BY, so EDB is > complaining. Isn't it just the opposite (ie the _Forename+' '+_Surname was OK) ? While I am not going to lose any sleep over it <bg> I can't see logically why any column should not be referenced in the SELECT list as long as it is included in the GROUP BY clause. ElevateDB allows the following SELECT columna+columnb,count(*)+sum(columna)+sum(columnb) FROM Table GROUP BY columna,columnb but not SELECT columna+columnb,count(*)+columna FROM Table GROUP BY columna,columnb John |
Wed, Mar 24 2010 1:11 PM | Permanent Link |
John Hay | Roy
As an aside to this, do you have any idea why OE would decide this thread should be part of a conversation started by you on 26/0/5/2008? I can't see any link bar the subject is the same. Strange...... John |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |