This query runs with no error, but ORDER BY is ignored

SELECT
 Remesa, C.Entidad, C.Oficina, Serie, Factura,
 0 AS ExpFac, CodigoLog, Asunto, FacturarA AS Cliente,
 FechaFactura AS Fecha, C.DC, C.Cuenta
FROM MAEXPEDI
INNER JOIN MACLIENT C ON (FACTURARA = C.NIF)
WHERE REMESA > 0 AND SERIE > 0 AND DOMICILIAR = TRUE
ORDER BY 1, 2, 3, 4, 5
--ORDER BY Remesa, Entidad, Oficina, Serie, Factura

Commented line is the correct one to make ORDER BY run.

Does EDB support ordinal numbers in ORDER BY as many RDBMS do? If not, I'll make this request, please.

Tiago,

<< Does EDB support ordinal numbers in ORDER BY as many RDBMS do? If not,
I'll make this request, please. >>

No, it doesn't because the SQL standards folks have indicated that they will
be phasing out support for such a construct.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<No, it doesn't because the SQL standards folks have indicated that they will
be phasing out support for such a construct.>

It's a handful feature when building SQL in code ...

In such case EDB would raise an error to inform that construct is not supported, I think.

Tiago,

<< In such case EDB would raise an error to inform that construct is not
supported, I think. >>

It is supported - any constant integer value is a valid expression.
Remember, in EDB the ORDER BY clause can use *any* valid expression,
including referencing columns that aren't in the SELECT list.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

> It is supported - any constant integer value is a valid expression.
> Remember, in EDB the ORDER BY clause can use any valid expression,
> including referencing columns that aren't in the SELECT list.

For what it's worth, I'm with Tim and the SQL standard on this one.

Tim,

<It is supported - any constant integer value is a valid expression.
Remember, in EDB the ORDER BY clause can use *any* valid expression,
including referencing columns that aren't in the SELECT list.>

Yes, right. But ORDER BY 1, 2, 3 has no sense, despite it's a "valid expression".
This makes a waste of time for every EDB new user looking for this issue: You create a query. It runs ok, you don't see at first (or second) test
ORDER BY is incorrect and you have a bug. Almost every EDB user will sufer this error maker. This construct is wide used in all major RDBMS
and, of course, DBISAM.
Documentation informs of it in DBISAM Migration chapter. IMHO in SELECT chapter would be a warning or similar to avoid this kind of problems.

I apreciate so much you can ORDER BY columns that aren't in the SELECT list, this a good feature

>> It is supported - any constant integer value is a valid expression.
>> Remember, in EDB the ORDER BY clause can use any valid expression,
>> including referencing columns that aren't in the SELECT list.
>
> For what it's worth, I'm with Tim and the SQL standard on this one.

+1

Ole Willy Tuv

Tiago Ameller wrote:

<snip>
> Yes, right. But ORDER BY 1, 2, 3 has no sense, despite it's a "valid
> expression".  This makes a waste of time for every EDB new user ...

<snip>

Kia Orana Tiago

Forgive a mere DBISAM v3.30 user giving his 2c worth ...  but this is a
great feature if I understand it correctly and it gives me an extra
impetus to move to EDB, eventually

If you can use an "valid expression" then you can do cool stuff like:-

ORDER BY CustomerCode,
        IF(TransType = "Invoice" THEN 1
        ELSE IF("TransType" = Receipt THEN 2
        ELSE 9)) /*EDB syntax is probably different!*/,
        TransDate

Kia Manuia

Jeff

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz
+
Joan and Jeff Cook
The Cooks Oasis
www.cookislandsoasis.com

Tiago,

<< Yes, right. But ORDER BY 1, 2, 3 has no sense, despite it's a "valid
expression".  >>

Well, so does True=False, yet customers use such a construct in WHERE
clauses.  IOW, there really is no way for us to *really* tell whether the
expressions are nonsense.  The only judgement that can be made is whether
they are valid or not.

<< This makes a waste of time for every EDB new user looking for this issue:
You create a query. It runs ok, you don't see at first (or second) test
ORDER BY is incorrect and you have a bug. Almost every EDB user will sufer
this error maker. This construct is wide used in all major RDBMS and, of
course, DBISAM.

Documentation informs of it in DBISAM Migration chapter. IMHO in SELECT
chapter would be a warning or similar to avoid this kind of problems. >>

Yes, but the migration section is specifically written for DBISAM users,
whereas the SELECT is not.  Duplicating the migration information everywhere
else in the manual really defeats the purpose of having the migration
section in the first place.

--
Tim Young
Elevate Software
www.elevatesoft.com