How to use a AS field name in WINDOW ORDER BY

20 views
Skip to first unread message

udono

unread,
Mar 1, 2016, 3:43:38 PM3/1/16
to python-sql
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

unread,
Mar 1, 2016, 6:40:02 PM3/1/16
to python-sql
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

unread,
Mar 2, 2016, 1:44:17 AM3/2/16
to python-sql
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

unread,
Mar 2, 2016, 2:11:27 AM3/2/16
to python-sql

Ok, I found it:

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

Thanks a lot.

Regards Udo

Cédric Krier

unread,
Mar 2, 2016, 3:50:04 AM3/2/16
to python-sql
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

unread,
Mar 2, 2016, 3:59:11 AM3/2/16
to python-sql
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.
Reply all
Reply to author
Forward
0 new messages