ORDER BY error message - 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

General SQL Server Forums

New to SQL Server Programming

ORDER BY error message

Author

Topic

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2010-03-05 : 08:50:41

Hi friends,

Any idea about this message:
ORDER BY items must appear in the select list if the statement contains a UNION operator.

It doesn't seem to like (case WHEN order_id IS NULL THEN 1 ELSE 0 END),

when I take that out and use order_id it seems to accept that (but not the order that I need) so I have no idea what the problem is, can you help?

SELECT cast(tb_properties.propertyid as integer) as propertyid, cast(tb_properties.prop_branchref as varchar(10)) as prop_branchref,
cast(tb_properties.prop_name as varchar(50)) as prop_name, cast(tb_properties.prop_description as varchar(2000)) as prop_description,
cast(tb_properties.prop_description2 as varchar(2000)) as prop_description2, cast(tb_properties.prop_owner as integer) as prop_owner,
cast(tb_properties.rand_sort as uniqueidentifier) as rand_sort, cast(tb_photos.photoid as integer) as photoid,
cast(tb_owner_order.order_id as integer) as order_id, cast(tb_country.country_name as varchar(50)) as country_name,
cast(tb_regions.region_name as varchar(50)) as region_name, cast(tb_counties.county_name as varchar(50)) as county_name,
cast(tb_town.town_name as varchar(50)) as town_name
from tb_properties left join
tb_photos on tb_properties.propertyid = tb_photos.propertyid left join
tb_town on tb_town.town_id = tb_properties.prop_town left join
tb_counties on tb_counties.county_id = tb_properties.prop_county left join
tb_regions on tb_regions.regionid = tb_counties.region_id left join
tb_country on tb_regions.region_country = tb_country.countryid left join
tb_owner_order ON tb_owner_order.order_property_id = tb_properties.propertyid and dateadd(m, 1, tb_owner_order.transactiondatetime) > current_timestamp and tb_owner_order.order_total < 5
WHERE tb_photos.main_photo = 1 and left(TB_Town.Town_Name,1) = 'O'
UNION
SELECT cast(tb_lrproperties.propertyid as integer) as propertyid, cast(tb_lrproperties.prop_branchref as varchar(10)) as prop_branchref,
cast(tb_lrproperties.prop_name as varchar(50)) as prop_name, cast(tb_lrproperties.prop_description as varchar(2000)) as prop_description,
cast(tb_lrproperties.prop_description2 as varchar(2000)) as prop_description2, cast(tb_lrproperties.prop_owner as integer) as prop_owner,
cast(tb_lrproperties.rand_sort as uniqueidentifier) as rand_sort, cast(tb_photos.photoid as integer) as photoid,
cast(tb_owner_order.order_id as integer) as order_id, cast(tb_country.country_name as varchar(50)) as country_name,
cast(tb_regions.region_name as varchar(50)) as region_name, cast(tb_counties.county_name as varchar(50)) as county_name,
cast(tb_town.town_name as varchar(50)) as town_name
from tb_lrproperties left join
tb_photos on tb_lrproperties.propertyid = tb_photos.propertyid left join
tb_town on tb_town.town_id = tb_lrproperties.prop_town left join
tb_counties on tb_counties.county_id = tb_lrproperties.prop_county left join
tb_regions on tb_regions.regionid = tb_counties.region_id left join
tb_country on tb_regions.region_country = tb_country.countryid left join
tb_owner_order ON tb_owner_order.order_property_id = tb_lrproperties.propertyid and dateadd(m, 1, tb_owner_order.transactiondatetime) > current_timestamp and tb_owner_order.order_total < 5
WHERE left(TB_Town.Town_Name,1) = 'O'

order by (case WHEN order_id IS NULL THEN 1 ELSE 0 END), rand_sort ASC

raky
Aged Yak Warrior

767 Posts

Posted - 2010-03-05 : 09:13:46

try this



SELECT T.*
FROM 
(
SELECT cast(tb_properties.propertyid as integer) as propertyid, cast(tb_properties.prop_branchref as varchar(10)) as prop_branchref,
cast(tb_properties.prop_name as varchar(50)) as prop_name, cast(tb_properties.prop_description as varchar(2000)) as prop_description,
cast(tb_properties.prop_description2 as varchar(2000)) as prop_description2, cast(tb_properties.prop_owner as integer) as prop_owner,
cast(tb_properties.rand_sort as uniqueidentifier) as rand_sort, cast(tb_photos.photoid as integer) as photoid,
cast(tb_owner_order.order_id as integer) as order_id, cast(tb_country.country_name as varchar(50)) as country_name,
cast(tb_regions.region_name as varchar(50)) as region_name, cast(tb_counties.county_name as varchar(50)) as county_name,
cast(tb_town.town_name as varchar(50)) as town_name
from tb_properties left join
tb_photos on tb_properties.propertyid = tb_photos.propertyid left join
tb_town on tb_town.town_id = tb_properties.prop_town left join
tb_counties on tb_counties.county_id = tb_properties.prop_county left join
tb_regions on tb_regions.regionid = tb_counties.region_id left join
tb_country on tb_regions.region_country = tb_country.countryid left join
tb_owner_order ON tb_owner_order.order_property_id = tb_properties.propertyid and dateadd(m, 1, tb_owner_order.transactiondatetime) > current_timestamp and tb_owner_order.order_total < 5
WHERE tb_photos.main_photo = 1 and left(TB_Town.Town_Name,1) = 'O'
UNION
SELECT cast(tb_lrproperties.propertyid as integer) as propertyid, cast(tb_lrproperties.prop_branchref as varchar(10)) as prop_branchref,
cast(tb_lrproperties.prop_name as varchar(50)) as prop_name, cast(tb_lrproperties.prop_description as varchar(2000)) as prop_description,
cast(tb_lrproperties.prop_description2 as varchar(2000)) as prop_description2, cast(tb_lrproperties.prop_owner as integer) as prop_owner,
cast(tb_lrproperties.rand_sort as uniqueidentifier) as rand_sort, cast(tb_photos.photoid as integer) as photoid,
cast(tb_owner_order.order_id as integer) as order_id, cast(tb_country.country_name as varchar(50)) as country_name,
cast(tb_regions.region_name as varchar(50)) as region_name, cast(tb_counties.county_name as varchar(50)) as county_name,
cast(tb_town.town_name as varchar(50)) as town_name
from tb_lrproperties left join
tb_photos on tb_lrproperties.propertyid = tb_photos.propertyid left join
tb_town on tb_town.town_id = tb_lrproperties.prop_town left join
tb_counties on tb_counties.county_id = tb_lrproperties.prop_county left join
tb_regions on tb_regions.regionid = tb_counties.region_id left join
tb_country on tb_regions.region_country = tb_country.countryid left join
tb_owner_order ON tb_owner_order.order_property_id = tb_lrproperties.propertyid and dateadd(m, 1, tb_owner_order.transactiondatetime) > current_timestamp and tb_owner_order.order_total < 5
WHERE left(TB_Town.Town_Name,1) = 'O'

)T
order by (case WHEN order_id IS NULL THEN 1 ELSE 0 END), order_id,rand_sort ASC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 09:40:15

that should be


SELECT T.*
FROM 
(
SELECT cast(tb_properties.propertyid as integer) as propertyid, cast(tb_properties.prop_branchref as varchar(10)) as prop_branchref,
cast(tb_properties.prop_name as varchar(50)) as prop_name, cast(tb_properties.prop_description as varchar(2000)) as prop_description,
cast(tb_properties.prop_description2 as varchar(2000)) as prop_description2, cast(tb_properties.prop_owner as integer) as prop_owner,
cast(tb_properties.rand_sort as uniqueidentifier) as rand_sort, cast(tb_photos.photoid as integer) as photoid,
cast(tb_owner_order.order_id as integer) as order_id, cast(tb_country.country_name as varchar(50)) as country_name,
cast(tb_regions.region_name as varchar(50)) as region_name, cast(tb_counties.county_name as varchar(50)) as county_name,
cast(tb_town.town_name as varchar(50)) as town_name,
case WHEN order_id IS NULL THEN 1 ELSE 0 END AS SortOrd
from tb_properties left join
tb_photos on tb_properties.propertyid = tb_photos.propertyid left join
tb_town on tb_town.town_id = tb_properties.prop_town left join
tb_counties on tb_counties.county_id = tb_properties.prop_county left join
tb_regions on tb_regions.regionid = tb_counties.region_id left join
tb_country on tb_regions.region_country = tb_country.countryid left join
tb_owner_order ON tb_owner_order.order_property_id = tb_properties.propertyid and dateadd(m, 1, tb_owner_order.transactiondatetime) > current_timestamp and tb_owner_order.order_total < 5
WHERE tb_photos.main_photo = 1 and left(TB_Town.Town_Name,1) = 'O'
UNION
SELECT cast(tb_lrproperties.propertyid as integer) as propertyid, cast(tb_lrproperties.prop_branchref as varchar(10)) as prop_branchref,
cast(tb_lrproperties.prop_name as varchar(50)) as prop_name, cast(tb_lrproperties.prop_description as varchar(2000)) as prop_description,
cast(tb_lrproperties.prop_description2 as varchar(2000)) as prop_description2, cast(tb_lrproperties.prop_owner as integer) as prop_owner,
cast(tb_lrproperties.rand_sort as uniqueidentifier) as rand_sort, cast(tb_photos.photoid as integer) as photoid,
cast(tb_owner_order.order_id as integer) as order_id, cast(tb_country.country_name as varchar(50)) as country_name,
cast(tb_regions.region_name as varchar(50)) as region_name, cast(tb_counties.county_name as varchar(50)) as county_name,
cast(tb_town.town_name as varchar(50)) as town_name,
case WHEN order_id IS NULL THEN 1 ELSE 0 END
from tb_lrproperties left join
tb_photos on tb_lrproperties.propertyid = tb_photos.propertyid left join
tb_town on tb_town.town_id = tb_lrproperties.prop_town left join
tb_counties on tb_counties.county_id = tb_lrproperties.prop_county left join
tb_regions on tb_regions.regionid = tb_counties.region_id left join
tb_country on tb_regions.region_country = tb_country.countryid left join
tb_owner_order ON tb_owner_order.order_property_id = tb_lrproperties.propertyid and dateadd(m, 1, tb_owner_order.transactiondatetime) > current_timestamp and tb_owner_order.order_total < 5
WHERE left(TB_Town.Town_Name,1) = 'O'

)T
order by SortOrd, order_id,rand_sort ASC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2010-03-05 : 10:20:50

It works, thank you so much guys

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 10:22:22

quote:
Originally posted by godspeedba

It works, thank you so much guys

which one works? mine of raky's?

Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2010-03-11 : 05:47:24

To be honest I only tried yours Visakh, you have helped so much in the past and I cannot thank you enough.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-11 : 11:28:02

quote:
Originally posted by godspeedba

To be honest I only tried yours Visakh, you have helped so much in the past and I cannot thank you enough.

You're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources