Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 11 to 20 of 20 total |
Error #700 |
Thu, Mar 25 2010 10:25 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | John
>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...... You've spotted the reason. Why I don't know but OE does use the title as part of its threading logic. One of the many reasons I rolled my own. Roy Lambert |
Thu, Mar 25 2010 2:59 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< 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? >> No, the issue is that you're referencing a column in the expression that isn't inside of an aggregate and isn't in the GROUP BY *as it exists in the SELECT column expression*. The bottom line is that you cannot have any components of an expression that aren't inside of an aggregate function, and you must have all separate expressions that aren't in an aggregate expression be part of the GROUP BY. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Mar 25 2010 3:07 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | John,
<< Isn't it just the opposite (ie the _Forename+' '+_Surname was OK) ? >> Actually, I misread the query and mistook the + for a comma, but the issue is still the same. << 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 >> I'll have to check this out and find out what the issue is, but I suspect that it's just a case of EDB not issuing an error message when it should be. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Mar 26 2010 4:25 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>No, the issue is that you're referencing a column in the expression that >isn't inside of an aggregate and isn't in the GROUP BY *as it exists in the >SELECT column expression*. > >The bottom line is that you cannot have any components of an expression that >aren't inside of an aggregate function, and you must have all separate >expressions that aren't in an aggregate expression be part of the GROUP BY. After reading many times, and having written a different reply I finally reached comprehension of what you've written before posting. It seems a bit of a weird restriction but at least I now (I think) understand what it is. Roy Lambert |
Fri, Mar 26 2010 7:22 AM | Permanent Link |
John Hay | Roy
> It seems a bit of a weird restriction but at least I now (I think) understand what it is. Knowing how keen you are on standards <vbg> the restriction was part of 1992 standard. The 2003 standard changed this to "If T is a grouped table, then let G be the set of grouping columns of T. In each <value expression> contained in <select list>, each column reference that references a column of T shall reference some column C that is functionally dependent on G or shall be contained in an aggregated argument of a <set function specification> whose aggregation query is QS. " Your original query therefore is "potentially" valid per the standard. While it is interesting from a logical/intellectual standpoint, adhering to the '92 standard and using dervived tables for concantenation etc is perfectly workable. John |
Fri, Mar 26 2010 8:47 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | John
>Knowing how keen you are on standards <vbg> the restriction was part of 1992 >standard. The 2003 standard changed this to > >"If T is a grouped table, then let G be the set of grouping columns of T. In >each <value expression> contained in <select list>, each column reference >that references a column of T shall reference some column C that is >functionally dependent on G or shall be contained in an aggregated argument >of a <set function specification> whose aggregation query is QS. " I especially hate them when they make Sir Humphrey seem as though he's speaking plain English! Tim has my pity for having to read and comprehend stuff like that. Roy Lambert ps NULL <> emptystring still doesn't make sense. |
Fri, Mar 26 2010 10:09 AM | Permanent Link |
John Hay | Roy
> ps > > NULL <> emptystring still doesn't make sense. LOL John |
Fri, Mar 26 2010 2:21 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | John,
<< Knowing how keen you are on standards <vbg> the restriction was part of 1992 standard. The 2003 standard changed this to >> I'll check it out - it may be easier to just allow it than to disallow it, and I'm all for whatever is easiest. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Mar 26 2010 2:30 PM | Permanent Link |
John Hay | Tim
> and I'm all for whatever is easiest. > A man after my own heart <bg> John |
Wed, Apr 14 2010 7:55 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | John,
<< 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 >> I finally got a chance to look at this, and the issue isn't what I thought it was at all. EDB doesn't actually care whether the column references are in the GROUP BY at all, and I suspect that this was to be compatible with DBISAM. It *does*, however, care whether you try to combine aggregated expressions with non-aggregated expressions. This is why it complains about the second query, but not the first - the second query has this expression: count(*)+columna I'm still digging at this, though, because I can't see why EDB is complaining. It appears as though it is perfectly capable of evaluating such expressions properly. -- Tim Young Elevate Software www.elevatesoft.com |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |