Riho-Rene,
It's not just a sorting problem, it's a general SQL syntax problem.

At 01:22 PM 12/03/2004 +0200, you wrote:

>I can't get the query to sort correctly for field 'accountno'
>
>My Query is:
>SELECT (select account from v_account a where a.id=c.accid and c.docdate
>between a.sdate and a.edate) as accountno
>,for_pay.id,BANK , ACCID , STATUS
> , IBANK
> , c.CURRENCY
>FROM for_pay c
>join account on account.id=accid and uid=:UID and install=:INST
>
>Ordering is:
>Account=1;1 DESC
>ID=for_pay.ID;for_pay.ID DESC
>
>
>If I run this query from IBAdmin
> ....
> join account on account.id=accid and uid=:UID and install=:INST
> order by 1
>then the sorting works OK. If I run it in IBO 4.2Ib, then the sorting is
>done by field 'for_pay.id'.
>If I try to change the 1 with 2, like
> Account=2;2 DESC
>then I get error :"Invalid ORDER BY clause"

Because it *is* an invalid ORDER BY clause (even if the SQL were valid,
which it's not).

By getting the account column from a subquery, you have converted it to a
computed column. ORDER BY on a computed column has to be by its ordinal
number (in SQL), as you demonstrated for yourself in IBAdmin. It can't be
an OrderingItem, because an OrderingItem has to be a database field.

>Looks like IBO reorders something or does something behind the scenes.
>Any comments, solutions?

What it does behind the scenes is it changes the ORDER BY clause.

But, getting back to your query:

Fix up your SQL so that you output the column instead of an aliased
subquery expression. Also fix up your SQL so that 1) the table-aliasing is
all correct and 2) this join isn't getting muddled up with search
conditions and the unwanted subquery (which is also illegal, because it is
not a guaranteed singleton).

SELECT
a.account,
c.id,
c.bank,
c.accid,
c.status,
c.ibank,
c.currency
from v_account a
join for_pay c
on a.id = c.accid
where
c.docdate between a.sdate and a.edate
and c.uid = :UID
and c.install = :INST

Now a.account can be an OrderingItem. If you are going to use both ASC and
DESC orderings on it as an OrderingItem, you need to have both ASC and DESC
indexes on it.

Helen