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

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

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

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

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

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.

Roy

> ps
>
> NULL <> emptystring still doesn't make sense.

LOL

John

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

Tim

> and I'm all for whatever is easiest.
>

A man after my own heart <bg>

John

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