Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
pravin14u
Posting Yak Master
246 Posts |
Posted - 2006-11-20 : 09:34:14
|
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 csFROMSELECT * 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 ) ) WHEREPrevious_Week.account_id = Current_Week.account_idCan u please suggest a solution?Thanks |
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-11-20 : 09:49:15
|
[code]SELECT account_id, sabre_log_id as ps, outlet_count as poFROM ( 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 ) ) Previous_WeekUNION ALLSELECT account_id, sabre_log_id as ps, outlet_count as poFrom ( 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 ) ) Current_Week[/code]Harsh AthalyeIndia."Nothing is Impossible" |
 |
|
pravin14u
Posting Yak Master
246 Posts |
Posted - 2006-11-20 : 23:05:13
|
Thanks a lot for ur reply.But the query would return the result sets one below the other.We want these asPrevious_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 csin adjacent columns..It would be great if you can suggest a way to do this. |
 |
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-11-21 : 01:08:35
|
How difficult is that? Instead of UNION try join: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 csFROM ( 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 ) ) Previous_WeekJoin ( 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 ) ) Current_Weekon Previous_Week.account_id = Current_Week.account_id Harsh AthalyeIndia."Nothing is Impossible" |
 |
|
|
|
|
|
|