I tried to improve usagerecipe for windowed range query (http://
www.sqlalchemy.org/trac/wiki/UsageRecipes/WindowedRangeQuery) to
include previous query and multiple column ordering support.
I need to generate the following query:
SELECT
L2.[LineasAlbaranCliente_Cod_1],
L2.[LineasAlbaranCliente_Eje_2],
L2.[LineasAlbaranCliente_Ser_3],
L2.[LineasAlbaranCliente_Num_4],
L2.[LineasAlbaranCliente_Orden],
L2.[LineasAlbaranCliente_Lin_5],
L1.*
FROM (
SELECT
[LineasAlbaranCliente].[CodigoEmpresa] AS
[LineasAlbaranCliente_Cod_1],
[LineasAlbaranCliente].[EjercicioAlbaran] AS
[LineasAlbaranCliente_Eje_2],
[LineasAlbaranCliente].[LineasPosicion] AS
[LineasAlbaranCliente_Lin_5],
[LineasAlbaranCliente].[NumeroAlbaran] AS
[LineasAlbaranCliente_Num_4],
[LineasAlbaranCliente].[Orden] AS [LineasAlbaranCliente_Orden],
[LineasAlbaranCliente].[SerieAlbaran] AS
[LineasAlbaranCliente_Ser_3],
row_number() OVER (
ORDER BY
[LineasAlbaranCliente].[CodigoEmpresa],
[LineasAlbaranCliente].[EjercicioAlbaran],
[LineasAlbaranCliente].[LineasPosicion],
[LineasAlbaranCliente].[NumeroAlbaran],
[LineasAlbaranCliente].[Orden],
[LineasAlbaranCliente].[SerieAlbaran]
) AS rownum
FROM [LineasAlbaranCliente]
WHERE [LineasAlbaranCliente].[CodigoEmpresa] = 2) AS L2
inner join [LineasAlbaranCliente] L1 on
L2.LineasAlbaranCliente_Cod_1=L1.CodigoEmpresa
AND
L2.LineasAlbaranCliente_Eje_2=L1.EjercicioAlbaran
AND
L2.LineasAlbaranCliente_Lin_5=L1.LineasPosicion
AND
L2.LineasAlbaranCliente_Num_4=L1.NumeroAlbaran
AND
L2.LineasAlbaranCliente_Orden=L1.Orden
AND
L2.LineasAlbaranCliente_Ser_3=L1.SerieAlbaran
WHERE L2.rownum / 500.0 > 0.0 AND L2.rownum/500.0<=1.0
I have a query object with inner L2 query but I dont know how to do
the join...
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
