I have the following UNION statement, which works fine.

 SELECT CustCompany AS CustName, Total
 FROM Orders
 UNION
 SELECT CompanyName AS CustName, Total
 FROM Invoices
 GROUP BY CustName

But the resulting data set is not sorted by CustName.  All the like
CustNames are together, but not alphabetically.  So I should be able to
add an ORDER BY clause at the end of it all.

 ORDER BY CustName

This, however, does NOT work.  I get error 401, CustName does not
exist.  So I tried changing the ORDER BY clause to use the first
statement's "CustName" field, which worked.

 ORDER BY CustCompany

However using CompanyName, the second statement's "CustName" field, did
not.

Is this by design?  I thought I could use CustName here?  I did look
through the SQL reference for this, but ended up just stumbling on this
solution.

--
David Cornelius
CorneliusConcepts.com

David,

>   SELECT CustCompany AS CustName, Total
>   FROM Orders
>   UNION
>   SELECT CompanyName AS CustName, Total
>   FROM Invoices
>   GROUP BY CustName

The GROUP BY in this statement affects only the second query (FROM
Invoices), not the result of the union.

On the contrary, the ORDER BY clause applies to the final result of the
UNION operation, so ORDER BY "CustCompany" will order the entire result
set by the first column of the entire resulting table.

The reason why you can use "CustCompany" and not "CompanyName" is
because the column names in the resulting table of a UNION are the
columns names of the first table in the UNION. So it seems everything is
working as expected.

There is however a detail I can't answer (because I don't know the
answer , that is why can't "CustName" be used instead of
"CustCompany" in the ORDER BY.

Also, if what you want is not only to sort but also to group the entire
result table, then you can do that as follows:

SELECT CustName, Total FROM(
  SELECT CustCompany AS CustName, Total
  FROM Orders
  UNION
  SELECT CompanyName AS CustName, Total
  FROM Invoices
) as TempTbl
GROUP BY CustName

--
Fernando Dias
[Team Elevate]

Fernando Dias wrote:

> David,
>
> >  SELECT CustCompany AS CustName, Total
> >  FROM Orders
> >  UNION
> >  SELECT CompanyName AS CustName, Total
> >  FROM Invoices
> >  GROUP BY CustName
>
> The GROUP BY in this statement affects only the second query (FROM Invoices), not the result of the union.
>
> On the contrary, the ORDER BY clause applies to the final result of the UNION operation, so ORDER BY "CustCompany" will order the entire result set by the first column of the entire resulting table.
>
> The reason why you can use "CustCompany" and not "CompanyName" is because the column names in the resulting table of a UNION are the columns names of the first table in the UNION. So it seems everything is working as expected.
>
> There is however a detail I can't answer (because I don't know the answer , that is why can't "CustName" be used instead of "CustCompany" in the ORDER BY.
>
> Also, if what you want is not only to sort but also to group the entire result table, then you can do that as follows:
>
> SELECT CustName, Total FROM(
>   SELECT CustCompany AS CustName, Total
>   FROM Orders
>   UNION
>   SELECT CompanyName AS CustName, Total
>   FROM Invoices
> ) as TempTbl
> GROUP BY CustName

I don't have EDB yet, but I would try ORDER BY 1
ie BY <column number>

--

Malcolm,

> I don't have EDB yet, but I would try ORDER BY 1
> ie BY <column number>

That's not going to work (at least as you are expecting .
In EDB, ORDER BY expressions are no longer interpreted as columns order
numbers but as expressions whose value will be the value used in
sorting. For example, if you have a table T, with an integer column x:

T(x)
----
1
2
3
----

The statement

SELECT x FROM T ORDER BY 10-x

will sort the table according to the expression 10-x evaluated for each
row, that are the values 9,8,7 so the result will be:

T(x)
----
3
2
1


--
Fernando Dias
[Team Elevate]

Fernando Dias wrote:

> That's not going to work (at least as you are expecting .

Uh, oh!  Then that's another reason to dread the impending upgrade.  

Malcolm

Malcolm,

> Uh, oh!  Then that's another reason to dread the impending upgrade.  

At the moment, I'm converting an old app from BDE (Paradox) to EDB, and
after all it's not so scary as it seemed when I started. It's a lot of
work, yes, because the application is big and the details vanished from
my mind a long time ago, but at the end it will worth the effort, I think.

--
Fernando Dias
[Team Elevate]

> The GROUP BY in this statement affects only the second query (FROM
> Invoices), not the result of the union.
>
> On the contrary, the ORDER BY clause applies to the final result of
> the UNION operation, so ORDER BY "CustCompany" will order the entire
> result set by the first column of the entire resulting table.
>
> The reason why you can use "CustCompany" and not "CompanyName" is
> because the column names in the resulting table of a UNION are the
> columns names of the first table in the UNION. So it seems everything
> is working as expected.

Thanks very much--that makes a lot of sense.


> There is however a detail I can't answer (because I don't know the
> answer , that is why can't "CustName" be used instead of
> "CustCompany" in the ORDER BY.

That's one that really surprised me--seems contrary to the
documentation.


> Also, if what you want is not only to sort but also to group the
> entire result table, then you can do that as follows:
>
> SELECT CustName, Total FROM(
>   SELECT CustCompany AS CustName, Total
>   FROM Orders
>   UNION
>   SELECT CompanyName AS CustName, Total
>   FROM Invoices
> ) as TempTbl
> GROUP BY CustName

Neat trick!  I thought about using temp tables, but thought it would be
more complex than that.  Thanks for providing this alternative.


--
David Cornelius
CorneliusConcepts.com

David,

<< This, however, does NOT work.  I get error 401, CustName does not exist.
So I tried changing the ORDER BY clause to use the first statement's
"CustName" field, which worked. >>

I'll have to check into this and get back to you.  There's obviously
something wrong here, but I'm not sure what it is.

--
Tim Young
Elevate Software
www.elevatesoft.com

> I'll have to check into this and get back to you.  There's obviously
> something wrong here, but I'm not sure what it is.

Thanks.

--
David Cornelius
CorneliusConcepts.com

Tim,

<< I'll have to check into this and get back to you.  There's obviously
something wrong here, but I'm not sure what it is. >>

The ORDER BY clause should work directly on the result of processing the
query expression. The "CustName" column is belonging to the cursor/result
set (albeit derived from the "Orders"."CustCompany" and
"Invoices"."CompanyName" source columns in the query expression), and is
therefore a valid and highly logical sort specification. I also think that
SQL:2003/2008 alllows source column references in the sort specification.

Best regards,
Ole Willy Tuv