|
pravin14u
Posting Yak Master
India
246 Posts |
 Posted - 11/20/2006 : 09:35:30
|
We have two subqueries returning two recordsets with the same set of columns.
When we try to Union both using UNION, we are unable to specify the alias names for the sub queries.
I have given the query below. The Alias names are 'Current_Week' and 'Previous_Week', It shows syntax error near Current_Week.
SELECT Previous_Week.account_id, Previous_Week.sabre_log_id as ps, Previous_Week.outlet_count as po, Current_Week.outlet_count as ci,
Current_Week.sabre_log_id as cs
FROM
SELECT * FROM
(
SELECT account_id, outlet_count, sabre_log_id
FROM tbl_sabre_load_log
WHERE sabre_log_id in
(
SELECT max(sabre_log_id) as "sec_log" FROM
tbl_sabre_load_log WHERE sabre_log_id NOT IN
(SELECT max(sabre_log_id)
FROM tbl_sabre_load_log
GROUP BY account_id
)
GROUP BY account_id
)
)
UNION ALL
(
SELECT account_id, outlet_count, sabre_log_id
FROM tbl_sabre_load_log
WHERE sabre_log_id in
(SELECT max(sabre_log_id)
FROM tbl_sabre_load_log
GROUP BY account_id
)
)
WHERE
Previous_Week.account_id = Current_Week.account_id
Can u please suggest a solution?
Thanks |
|
Alias Names
