error?? Union Order by - 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

SQL Server Development (2000)

error?? Union Order by

Author

Topic

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-01-30 : 17:48:04

Here is my query, I keep getting this error message: "Server: Msg 104, Level 15, State 1, Line 9
ORDER BY items must appear in the select list if the statement contains a UNION operator."

I can't figure out why this is happening any help??

declare @loginid int, @venueid int, @rundate datetime, @clientid int

set @loginid = 1
set @venueid = 1
set @rundate = '2/1/2007'
set @clientid = 12404

select distinct
sf.startdate,
b.businessname as [Venue Name],
dat.displayareatype + ' (' + da.displayareaname + ') - ' + dp.displayplatformname as [Board Name],
cb.businessname [Client Name],
aw.artname as [Artwork Name],
pt.placementtypename as [Revenue Type],
frs.runname as ServiceRun,
a.address1 as Address1,
isnull(a.address2,'') as Address2,
a.city as City,
st.stateabbr as State,
a.postalcode as Zip,
cc.startdate,
cc.enddate,
sf.enddate
from
clientcontract cc
inner join clientcontractdetail ccd on cc.clientcontractid = ccd.clientcontractid and ccd.deletedate is null
inner join business cb on cc.businessid = cb.businessid
inner join ArtWork aw on ccd.artworkid = aw.artworkid and aw.deletedate is null
inner join PlacementType pt on ccd.placementtypeid = pt.placementtypeid and pt.deletedate is null
inner join slotfill sf on ccd.clientcontractdetailid = sf.clientcontractdetailid and sf.deletedate is null
inner join displayplatform dp on sf.displayplatformid = dp.displayplatformid and dp.deletedate is null
inner join displayarea da on dp.displayareaid = da.displayareaid
inner join DisplayAreaType dat on da.displayareatypeid = dat.displayareatypeid and dat.deletedate is null
inner join siteregionvenue srv on da.siteregionvenueid = srv.siteregionvenueid
inner join franchiseservicerun frs on srv.franchiseservicerunid = frs.franchiseservicerunid
inner join business b on srv.businessid = b.businessid
left join address a on a.addressid = (select top 1 addressid from address where businessid = b.businessid and deletedate is null order by addresstypeid)
left join state st on a.stateid = st.stateid
inner join franchiseregionvenue frv on srv.franchiseregionvenueid = frv.franchiseregionvenueid
inner join business fb on frv.businessid = fb.businessid
inner join Contact c on cc.salesrepcontactid = c.contactid
where
cc.businessid = case @clientid when 0 then cc.businessid else @clientid end
and @rundate <= sf.enddate
and cc.venueid = case @venueid when 0 then cc.venueid else @venueid end
and (fb.businessid = (select businessid from Contact nc inner join Login l on nc.contactid = l.contactid where l.loginid = @loginid)
or c.businessid = (select businessid from Contact nc inner join Login l on nc.contactid = l.contactid where l.loginid = @loginid))

Union

select distinct
sf.startdate,
b.businessname,
dat.displayareatype + ' (' + da.displayareaname + ') - ' + dp.displayplatformname,
cb.businessname,
aw.artname,
pt.placementtypename,
frs.runname,
a.address1,
isnull(a.address2,''),
a.city,
st.stateabbr,
a.postalcode,
cc.startdate,
cc.enddate,
sf.enddate
from
clientcontract cc
inner join clientcontractdetail ccd on cc.clientcontractid = ccd.clientcontractid and ccd.deletedate is null
inner join artwork aw on ccd.artworkid = aw.artworkid and aw.deletedate is null
inner join PlacementType pt on ccd.placementtypeid = pt.placementtypeid and pt.deletedate is null
inner join slotfill sf on ccd.clientcontractdetailid = sf.clientcontractdetailid and sf.deletedate is null
and sf.deletedate is null
and sf.enddate < @rundate
--inner join slottype st on sf.slottypeid = st.slottypeid
inner join slottypedetail std on sf.slottypeid = std.slottypeid
--inner join slottypedetail std2 on std.slotnumber = std2.slotnumber
--inner join slottype st2 on std2.slottypeid = st2.slottypeid and st.displayplatformtypeid = st2.displayplatformtypeid
inner join displayplatform dp on sf.displayplatformid = dp.displayplatformid and dp.deletedate is null
inner join displayarea da on dp.displayareaid = da.displayareaid
inner join DisplayAreaType dat on da.displayareatypeid = dat.displayareatypeid and dat.deletedate is null
inner join siteregionvenue srv on da.siteregionvenueid = srv.siteregionvenueid
inner join franchiseservicerun frs on srv.franchiseservicerunid = frs.franchiseservicerunid
inner join business b on srv.businessid = b.businessid
left join address a on a.addressid = (select top 1 addressid from address where businessid = b.businessid and deletedate is null order by addresstypeid)
left join state st on a.stateid = st.stateid
inner join franchiseregionvenue frv on srv.franchiseregionvenueid = frv.franchiseregionvenueid
inner join business fb on frv.businessid = fb.businessid
inner join Contact c on cc.salesrepcontactid = c.contactid
inner join business cb on cc.businessid = cb.businessid
where cc.businessid = @clientid
and cc.venueid = @venueid
and not exists(select *
from
slotfill sf1
inner join slottypedetail std1 on sf1.slottypeid = std1.slottypeid
and std1.slotnumber = std.slotnumber
where sf1.displayplatformid = sf.displayplatformid
and sf1.deletedate is null
and sf1.startdate between sf.enddate and @rundate)

order by b.businessname, sf.startdate

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 00:00:42

1) Drop the two DISTINCT keywords. The UNION operator will handle that for you.
2) Change ORDER BY b.BusinessName, sf.StartDate to ORDER BY 2, 1

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources