Hi all,
I am facing the problem with aliasing when i try to do union of
SA queries.
Following three SA queries
--------------------------
query = self.session.query(Table1.row_num.label('row_num'),
Table.test_msg.label('test_msg'),
Table.date_created.label('date_created'),
Table2.name_file.label('name_file')).join(
(Table2, Table2.some_idn == Table.some_idn))
query1 = self.session.query(Table3.row_num.label('row_num'),
Table.test_msg.label('test_msg'),
Table.date_created.label('date_created'),
Table2.name_file.label('name_file')).join(
(Table2, Table2.some_idn == Table.some_idn))
query2 = self.session.query(Table4.row_num.label('row_num'),
Table.test_msg.label('test_msg'),
Table.date_created.label('date_created'),
Table2.name_file.label('name_file')).join(
(Table2, Table2.some_idn == Table.some_idn))
We got the following sql statements for above three queries.
------------------------------------------------------------------
SELECT dbo.table1.row_num AS row_num, dbo.table1.test_msg AS test_msg,
dbo.table1.date_created AS date_created, table2.name_file AS
name_file
FROM dbo.table1 JOIN table2 ON table2.some_idn = dbo.table1.some_idn
WHERE table2.some_idn = :some_idn_1 AND dbo.table1.date_created
BETWEEN :date_created_1 AND :date_created_2
SELECT dbo.table3.row_num AS row_num, dbo.table3.test_msg AS test_msg,
dbo.table3.date_created AS date_created, table2.name_file AS
name_file
FROM dbo.table3 JOIN table2 ON table2.some_idn = dbo.table3.some_idn
WHERE table2.some_idn = :some_idn_2 AND dbo.table3.date_created
BETWEEN :date_created_3 AND :date_created_4
SELECT dbo.table4.row_num AS row_num, dbo.table4.test_msg AS test_msg,
dbo.table4.date_created AS date_created, table2.name_file AS
name_file
FROM dbo.table4 JOIN table2 ON table2.some_idn = dbo.table4.some_idn
WHERE table2.some_idn = :some_idn_3 AND dbo.table4.date_created
BETWEEN :date_created_5 AND :date_created_6
when i do the union(used union_all) of all the above three queries
-------------------------------------------------------------------
query.union_all(query1, query2) is producing below select statement.As
i added label method to change labels of columns to refer in the code.
anonimous column names are giving reference errors while accesing
result set.
SELECT
anon_1.anon_2_row_num AS anon_1_anon_2_row_num,
anon_1.anon_2_test_msg AS anon_1_anon_2_test_msg,
anon_1.anon_2_date_created AS anon_1_anon_2_date_created,
anon_1.anon_2_name_file AS anon_1_anon_2_name_file
FROM
(SELECT anon_2.row_num AS anon_2_row_num, anon_2.test_msg AS
anon_2_test_msg, anon_2.date_created AS anon_2_date_created,
anon_2.name_file AS anon_2_name_file
FROM
(SELECT dbo.table1.row_num AS row_num, dbo.table1.test_msg AS
test_msg, dbo.table1.date_created AS date_created,
table2.name_file
AS name_file
FROM
dbo.table1 JOIN table2 ON table2.some_idn = dbo.table1.some_idn
WHERE
table2.some_idn = :some_idn_1 AND dbo.table1.date_created
BETWEEN :date_created_1 AND :date_created_2
UNION ALL
SELECT
dbo.table3.row_num AS row_num, dbo.table3.test_msg AS test_msg,
dbo.table3.date_created AS date_created,
table2.name_file AS name_file
FROM
dbo.table3 JOIN table2 ON table2.some_idn = dbo.table3.some_idn
WHERE
table2.some_idn = :some_idn_2 AND dbo.table3.date_created
BETWEEN :date_created_3 AND :date_created_4) AS anon_2
UNION ALL
SELECT
dbo.table4.row_num AS row_num, dbo.table4.test_msg AS test_msg,
dbo.table4.date_created
AS date_created, table2.name_file
AS name_file
FROM
dbo.table4 JOIN table2 ON table2.some_idn = dbo.table4.some_idn
WHERE
table2.some_idn = :some_idn_3 AND dbo.table4.date_created
BETWEEN :date_created_5 AND :date_created_6) AS anon_1
Here we don't want to have top level select column name with alias
since i am refering in the code as row_num.
Can any one suggest what changes to be made in the query.
--
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.
