Problem with UNION???!!! - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with UNION???!!!

Author

Topic

justin_jk
Starting Member

4 Posts

Posted - 2005-09-01 : 03:06:59

How can i join two Query using UNION, with presence of order in the first query.If there any other way also,plz let me know.

For Example :

(Select product_code, product_name,price,PM.product_id,PM.Categoryid, min_order_volume,product_picture_small,lead_time from Product_master PM, B2C_priceList PL where PM.product_id=PL.product_id and not(apply_to)='B' and not(PL.Price) is null and PM.cust_id='1md00001' AND (PM.categoryid) in (37,7,42) order by case when categoryid = 37 then 1 when categoryid = 7 then 2 when categoryid = 42 then 3 end)

UNION ALL

(Select product_code, product_name price, PM.product_id,PM.Categoryid,min_order_volume,product_picture_small,lead_time from Product_master PM,B2C_priceList PL where PM.product_id=PL.product_id and not(apply_to)='B' and not(PL.Price) is null and PM.cust_id='1md00001' AND (not(PM.categoryid) in (37,7,42) OR categoryid IS NULL))

Thanks in Advance
Justin

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2005-09-01 : 03:45:19

[code]
Select 1 x,product_code, product_name,price,PM.product_id,PM.Categoryid, min_order_volume,product_picture_small,lead_time from Product_master PM, B2C_priceList PL where PM.product_id=PL.product_id and not(apply_to)='B' and not(PL.Price) is null and PM.cust_id='1md00001' AND (PM.categoryid) in (37,7,42)

UNION ALL

Select 2 x,product_code, product_name price, PM.product_id,PM.Categoryid,min_order_volume,product_picture_small,lead_time from Product_master PM,B2C_priceList PL where PM.product_id=PL.product_id and not(apply_to)='B' and not(PL.Price) is null and PM.cust_id='1md00001' AND (not(PM.categoryid) in (37,7,42) OR categoryid IS NULL)
order by x,case when categoryid = 37 then 1 when categoryid = 7 then 2 when categoryid = 42 then 3 end
[/code]

Kristen
Test

22859 Posts

Posted - 2005-09-01 : 03:58:37

Isn't the difference between the two just that the first has
PM.categoryid in (37,7,42)
and the second SELECT has all other PM.categoryid values?

If so scrap the UNION and just extend the CASE in the ORDER BY


case when categoryid = 37 then 1 
     when categoryid = 7 then 2 
     when categoryid = 42 then 3 
     ELSE 9999
end

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-01 : 04:10:58

Kris, is the effect of using 9999 equivalent to column itself?
I mean
Else 9999 => Else categoryid?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-01 : 04:28:24

Its going to ORDER BY this calculated value - 1, 2 or 3 for the specified values. I just assigned an "ELSE" to put everything else last - and "9999" to signify that it was last [I choose a big number to signify that, and allow for additions in teh future etc.]

Kristen

justin_jk
Starting Member

4 Posts

Posted - 2005-09-01 : 04:43:28

hi LarsG,
When i tried this,am getting an error as shown below.but if i, remove the case clause its working.But its not my required result.so how to solve this problem.

Error:
ORDER BY items must appear in the select list if the statement contains a UNION operator

Select 1 x,product_code, product_name,price,PM.product_id,PM.Categoryid, min_order_volume,product_picture_small,lead_time from Product_master PM, B2C_priceList PL where PM.product_id=PL.product_id and not(apply_to)='B' and not(PL.Price) is null and PM.cust_id='1md00001' AND (PM.categoryid) in (37,7,42)

UNION ALL

Select 2 x,product_code, product_name price, PM.product_id,PM.Categoryid,min_order_volume,product_picture_small,lead_time from Product_master PM,B2C_priceList PL where PM.product_id=PL.product_id and not(apply_to)='B' and not(PL.Price) is null and PM.cust_id='1md00001' AND (not(PM.categoryid) in (37,7,42) OR categoryid IS NULL)
order by x,case when categoryid = 37 then 1 when categoryid = 7 then 2 when categoryid = 42 then 3 end

Error:
ORDER BY items must appear in the select list if the statement contains a UNION operator

justin_jk
Starting Member

4 Posts

Posted - 2005-09-01 : 04:52:50

Hi Kristen,

Thanks a lot, this is working Great.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-01 : 04:54:24

Did you apply Kristen's method?

Select * from (
Select product_code, product_name,price,PM.product_id,PM.Categoryid, min_order_volume,product_picture_small,lead_time from Product_master PM, B2C_priceList PL where PM.product_id=PL.product_id and not(apply_to)='B' and not(PL.Price) is null and PM.cust_id='1md00001' AND (PM.categoryid) in (37,7,42)

UNION ALL

(Select product_code, product_name price, PM.product_id,PM.Categoryid,min_order_volume,product_picture_small,lead_time from Product_master PM,B2C_priceList PL where PM.product_id=PL.product_id and not(apply_to)='B' and not(PL.Price) is null and PM.cust_id='1md00001' AND (not(PM.categoryid) in (37,7,42) OR categoryid IS NULL))
) T
Order by case when categoryid = 37 then 1
when categoryid = 7 then 2
when categoryid = 42 then 3
ELSE 9999
end

Madhivanan

Failing to plan is Planning to fail

justin_jk
Starting Member

4 Posts

Posted - 2005-09-01 : 08:46:02

Madhivanan,

The idea give by kirsten is working right now.The query is given below.if there any problem plz let me know.

Query
=======
Select product_code, product_name,price,PM.product_id,PM.Categoryid, min_order_volume,product_picture_small,lead_time from Product_master PM, B2C_priceList PL where PM.product_id=PL.product_id and not(apply_to)='B' and not(PL.Price) is null and PM.cust_id='1MD00001' order by case when categoryid = 7 then 1 when categoryid = 97 then 2 when categoryid = 37 then 3 when categoryid = 45 then 4 else 9999 end

Justin

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-01 : 08:59:06

Thats correct
When I posted my reply, I did not see your below reply

quote:
Hi Kristen,

Thanks a lot, this is working Great.

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-01 : 09:47:05

"The query is given below"

hehehe .. even including the 9999 - I should ahve used my date of birth and then my legacy would be widespread!!

I disassembled some code back in the '70's which was written by a bright guy called Allen Ashworth. Whole sections of the code failed to disassemble because they had been XOR'd with, you've guessed it!, "AA" - one way to achieve prosterity!

Kristen

shijobaby
Starting Member

44 Posts

Posted - 2009-08-19 : 05:40:46

Hi

The reasons and ways to avoid this error have discussed in this

site with good examples. By making small changes in the query

http://sqlerror104.blogspot.com/2009/08/order-by-items-must-appear-in-select_19.html

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-19 : 06:52:23

quote:
Originally posted by shijobaby

Hi

The reasons and ways to avoid this error have discussed in this

site with good examples. By making small changes in the query

http://sqlerror104.blogspot.com/2009/08/order-by-items-must-appear-in-select_19.html

How is your suggestion related to the topic?
Also why do you post the same answer in almost all the threads you post?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources