How to use a AS field name in WINDOW ORDER BY

[udono]

Hi,

having the following case:

from sql import Table, Window
from sql.aggregate import Sum
from sql.conditionals import Coalesce


table = Table('table')
columns = [
    Coalesce(
        table.name1,
    table.name2,
    ).as_('name'),
    Sum(
        table.debit - table.credit,
    window=Window(
            [],
            order_by=(
# The next line is not ok:
                table.name.asc,
            )
    )
    ).as_('sum'),
    ]
select = table.select(*columns)
tuple(select)

It results in:

('SELECT COALESCE("a"."name1", "a"."name2") AS "name", SUM(("a"."debit" - "a"."credit")) OVER "b" AS "sum" FROM "table" AS "a" WINDOW "b" AS ( ORDER BY "a"."name" ASC)', ())

The COALESCE column is correctly aliased as "name".

But how can I use the "name" column in the WINDOW ORDER BY expression, without using a Table() instance (table.name) which results in the wrong table prefix "a"?

The result should look like:



('SELECT COALESCE("a"."name1", "a"."name2") AS "name", SUM(("a"."debit" - "a"."credit")) OVER "b" AS "sum" FROM "table" AS "a" WINDOW "b" AS ( ORDER BY
"name" ASC)', ())


TIA and regards
Udo


[Cédric Krier]

On 2016-03-01 11:19, 'udono' via python-sql wrote:
> But how can I use the "name" column in the WINDOW ORDER BY expression,
> without using a Table() instance (table.name) which results in the wrong
> table prefix "a"?

You can not, you must duplicate the expression.


--
Cédric Krier - B2CK SPRL
Email/Jabber: cedric...@b2ck.com
Tel: +32 472 54 46 59
Website: http://www.b2ck.com/

[udono]

Am Mittwoch, 2. März 2016 00:40:02 UTC+1 schrieb Cédric Krier:

On 2016-03-01 11:19, 'udono' via python-sql wrote:
> But how can I use the "name" column in the WINDOW ORDER BY expression,
> without using a Table() instance (table.name) which results in the wrong
> table prefix "a"?

You can not, you must duplicate the expression.

What does it mean, "duplicate the expression"?

[udono]

Ok, I found it:

>             order_by=(
>               table.name.as_('name').asc,
>            )

Thanks a lot.

Regards Udo

[Cédric Krier]

This is wrong even if by change it works.
You must use:

order_by=(Coalesce(table.name1, table.name2).asc)

In python-sql, we don't have a way to re-use aliases but I think
database engine will any way optimize such duplication by computing only
once.
Maybe we could introduce a new object Alias which will be a little bit
like Literal.

[udono]

Am Mittwoch, 2. März 2016 09:50:04 UTC+1 schrieb Cédric Krier:

On 2016-03-01 23:11, 'udono' via python-sql wrote:
> Am Mittwoch, 2. März 2016 07:44:17 UTC+1 schrieb udono:
> > Am Mittwoch, 2. März 2016 00:40:02 UTC+1 schrieb Cédric Krier:
> >> On 2016-03-01 11:19, 'udono' via python-sql wrote:
> >> > But how can I use the "name" column in the WINDOW ORDER BY expression,
> >> > without using a Table() instance (table.name) which results in the
> >> wrong
> >> > table prefix "a"?
> >> You can not, you must duplicate the expression.
> > What does it mean, "duplicate the expression"?
> Ok, I found it:
> >             order_by=(
> >               table.name.as_('name').asc,
> >            )

This is wrong even if by change it works.
You must use:

    order_by=(Coalesce(table.name1, table.name2).asc)

Yes, this works correct. Thank you.