"Outer join operators cannot be specified"

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 2005 Forums

Transact-SQL (2005)

"Outer join operators cannot be specified"

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2008-09-02 : 09:22:40

I'm trying to modify an old query that had implicit table joins into one where that table joins are explicit, but it's giving me an error I've not seen before.

The error is "Outer join operators cannot be specified in a query containing joined tables".

The query is:

      select c.clientID,
        c.displayname as 'clientName',        c.salessupportID,        a.name as 'supportManager',       
 (select count(distinct ab.adID) from [jobs]..AdBooking ab where c.clientID *= ab.clientID and ab.displayStart < @now and ab.displayEnd > @now) as 'adCount',
        sum(unitsRemaining) as 'adWeeksRemaining',
        co.orderID,
        max(co.dateEnd) as 'expiryDate'
    into #tmp
    from [user]..Client c
        inner join [user]..ClientOrder co on c.clientID = co.clientID
        inner join [user]..ClientOrderItem coi on co.orderID = coi.orderID
        inner join [user]..Administrator a on c.salessupportID *= a.adminID
    where 
      and co.status = 2
      and coi.adWeekBookingInd = 'Y'
      and (@managerID < 0
           or @managerID = isnull(c.salessupportID, 0))
    group by c.clientID,        c.displayname,        c.salessupportID,        a.name,        co.orderID

    select clientID,        clientName,        salessupportID,        supportManager,        max(adCount) as 'adCount',
        sum(adWeeksRemaining) as 'adWeeksRemaining',        count(orderID) as 'orderCount',        max(expiryDate) as 'maxExpiryDate'
    from #tmp    group by clientID,        clientName,        salessupportID,        supportManager,        adCount    order by clientName

Anyone spot the problem?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 09:27:27

This correlated subquery.

(select count(distinct ab.adID ... )

Replace *= with =.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-02 : 09:35:47

You cant use join syntaxes like *=,=* etc from sql 2005 (compatibility level 90) onwards. please use ANSI join syntax instead like LEFT JOIN,RIGHT JOIN,...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 09:38:40

[code]SELECT c.clientID,
c.displayName AS clientName,
c.salesSupportID,
a.name AS supportManager,
MAX(y.adCount) AS adCount,
SUM({Table alias missing}.unitsRemaining) AS adWeeksRemaining,
COUNT({Table alias missing}.orderID) AS orderCount,
MAX(co.dateEnd) as maxExpiryDate
FROM [user]..Client as c
INNER JOIN [user]..ClientOrder AS co ON co.clientID = c.clientID
AND co.status = 2
INNER JOIN [user]..ClientOrderItem AS coi ON coi.orderID = co.orderID
AND coi.adWeekBookingInd = 'Y'
LEFT JOIN [user]..Administrator AS a ON a.adminID = c.salesSupportID
LEFT JOIN (
SELECT clientID,
COUNT(DISTINCT adID) AS adCount
FROM [jobs]..AdBooking
WHERE displayStart < @Now
AND displayEnd > @Now
GROUP BY clientID
) AS y ON y.clientID = c.clientID
WHERE @ManagerID < 0
OR @ManagerID = c.salesSupportID
OR (@ManagerID = 0 AND c.salesSupportID IS NULL)
GROUP BY c.clientID,
c.displayName,
c.salesSupportID,
a.name
ORDER BY c.displayName[/code]

E 12°55'05.63"
N 56°04'39.26"

mattt
Posting Yak Master

194 Posts

Posted - 2008-09-02 : 10:02:38

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 10:05:34

Thank you for the feedback.

If you get a NULL value for adCount, try this instead

MAX(COALESCE(y.adCount, 0)) AS adCount

E 12°55'05.63"
N 56°04'39.26"

mattt
Posting Yak Master

194 Posts

Posted - 2008-09-02 : 10:16:44

quote:

Originally posted by Peso

SELECT		c.clientID,
		c.displayName AS clientName,
		c.salesSupportID,
		a.name AS supportManager,
		MAX(y.adCount) AS adCount,
		SUM({Table alias missing}.unitsRemaining) AS adWeeksRemaining,
		COUNT({Table alias missing}.orderID) AS orderCount,
		MAX(co.dateEnd) as maxExpiryDate
FROM		[user]..Client as c
INNER JOIN	[user]..ClientOrder AS co ON co.clientID = c.clientID
			AND co.status = 2
INNER JOIN	[user]..ClientOrderItem AS coi ON coi.orderID = co.orderID
			AND coi.adWeekBookingInd = 'Y'
LEFT JOIN	[user]..Administrator AS a ON a.adminID = c.salesSupportID
LEFT JOIN	(
			SELECT		clientID,
					COUNT(DISTINCT adID) AS adCount
			FROM		[jobs]..AdBooking
			WHERE		displayStart < @Now
					AND displayEnd > @Now
			GROUP BY	clientID
		) AS y ON y.clientID = c.clientID
WHERE		@ManagerID < 0
		OR @ManagerID = c.salesSupportID
		OR (@ManagerID = 0 AND c.salesSupportID IS NULL)
GROUP BY	c.clientID,
	        c.displayName,
		c.salesSupportID,
		a.name
ORDER BY	c.displayName

E 12°55'05.63"
N 56°04'39.26"

Actually this is returning very slightly different data to the previous query - in one row the "orderCount" has doubled from 2 to 4. Any idea what might have caused this (and I know that trying to figure it out without the table structure is difficult, but I'm asking for an indulgence because I don't really understand the query).

Cheers,
Matt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 10:22:25

[code]SELECT clientID,
clientName,
salesSupportID,
supportManager,
MAX(adCount) AS adCount,
SUM(adWeeksRemaining) AS adWeeksRemaining,
COUNT(orderCount) AS orderCount,
MAX(maxExpiryDate) AS maxExpiryDate
FROM (
SELECT c.clientID,
c.displayName AS clientName,
c.salesSupportID,
a.name AS supportManager,
MAX(y.adCount) AS adCount,
SUM({Table alias missing}.unitsRemaining) AS adWeeksRemaining,
COUNT({Table alias missing}.orderID) AS orderCount,
MAX(co.dateEnd) as maxExpiryDate
FROM [user]..Client as c
INNER JOIN [user]..ClientOrder AS co ON co.clientID = c.clientID
AND co.status = 2
INNER JOIN [user]..ClientOrderItem AS coi ON coi.orderID = co.orderID
AND coi.adWeekBookingInd = 'Y'
LEFT JOIN [user]..Administrator AS a ON a.adminID = c.salesSupportID
LEFT JOIN (
SELECT clientID,
COUNT(DISTINCT adID) AS adCount
FROM [jobs]..AdBooking
WHERE displayStart < @Now
AND displayEnd > @Now
GROUP BY clientID
) AS y ON y.clientID = c.clientID
WHERE @ManagerID < 0
OR @ManagerID = c.salesSupportID
OR (@ManagerID = 0 AND c.salesSupportID IS NULL)
GROUP BY c.clientID,
c.displayName,
c.salesSupportID,
a.name,
co.orderID
) AS d
ORDER BY clientName[/code]

E 12°55'05.63"
N 56°04'39.26"

mattt
Posting Yak Master

194 Posts

Posted - 2008-09-02 : 10:34:10

Lovely, thanks. The two are now producing identical results.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources