SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Alias Names
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

pravin14u
Posting Yak Master

India
246 Posts

Posted - 11/20/2006 :  09:34:14  Show Profile  Click to see pravin14u's MSN Messenger address  Send pravin14u a Yahoo! Message
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

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 11/20/2006 :  09:49:15  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
SELECT account_id, sabre_log_id as ps, 
	outlet_count as po
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
		)
	) Previous_Week

UNION ALL

SELECT account_id, sabre_log_id as ps, 
	outlet_count as po
From
	(
	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


Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

pravin14u
Posting Yak Master

India
246 Posts

Posted - 11/20/2006 :  23:05:13  Show Profile  Click to see pravin14u's MSN Messenger address  Send pravin14u a Yahoo! Message
Thanks a lot for ur reply.

But the query would return the result sets one below the other.
We want these as

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

in adjacent columns..

It would be great if you can suggest a way to do this.

Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 11/21/2006 :  01:08:35  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
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 cs
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
		)
	) Previous_Week

Join
	(
	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
on Previous_Week.account_id = Current_Week.account_id


Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000