March 19, 2010 at 9:51 am
I am attempting to create a view using the select statement below. As I piece it together, all works fine until I get to the derived table. Then I get the error that the derived table has more columns than specified. My goal is to determine if a shipment is for mulitple orders and if any of those orders have been flagged with a 3rd party billing address identify the most recent order tied to that shipment.
My query is below. The derived table runs ok when run separately. Any ideas?
select distinct shl.shipkey,
(count(distinct sol.sokey)) as SOCount,
case when (sum(ai.thirdpartybill) >= 1) then 'Yes'
else 'No'
end as thirdpartybill,
f.b as frtorder
from shipline shl
inner join soline sol (nolock) on shl.solinekey = sol.solinekey
inner join SOHeader s (nolock) on s.sokey = sol.sokey
left join (select distinct shl.shipkey a, max(ai.sokey) b
from shipline shl
inner join soline sol (nolock) on shl.solinekey = sol.solinekey
inner join SOHeader s (nolock) on s.sokey = sol.sokey
where ai.thirdpartybill > 0
group by shl.shipkey) as f (nolock) on f.a = shl.shipkey
group by shl.shipkey
Thanks in advance.
March 19, 2010 at 10:25 am
Can you provide table definitions for the tables the view selects from?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 19, 2010 at 12:17 pm
Two quick suggestions without having seen the table defs.
1. Move the derived table to a cte and perform your left outer join on that.
or
2. Change the table alias in the derived table for shipline (shl to sl for example).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 19, 2010 at 1:06 pm
And, you really should consider dropping the use of the NOLOCK hint everywhere. You may want to start by reading this: http://blogs.msdn.com/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx.
March 19, 2010 at 1:57 pm
Lynn Pettis (3/19/2010)
And, you really should consider dropping the use of the NOLOCK hint everywhere. You may want to start by reading this: http://blogs.msdn.com/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx.
Agreed - nolock can cause problems / and at the very least mask problems.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 20, 2010 at 2:37 pm
Another point on hints, from Books Online:
Omitting the WITH keyword is a deprecated feature: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
The following table hints are allowed with and without the WITH keyword: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, FASTFIRSTROW, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, and NOEXPAND. When these table hints are specified without the WITH keyword, the hints should be specified alone.
I usually recommend setting the correct isolation level at connection time, or setting it once using SET TRANSACTION ISOLATION LEVEL, instead of using hints on every table reference. It is too easy to forget one, and makes your code harder to read and maintain.
Note that READUNCOMMITTED means exactly the same as NOLOCK. Neither mean that no locks are ever taken - just that Shared locks are not taken when reading data.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 22, 2010 at 8:28 am
I got it to work. Thanks to all for the advice.
March 22, 2010 at 10:17 am
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 22, 2010 at 10:20 am
dpelan (3/22/2010)
I got it to work. Thanks to all for the advice.
One last quick question.
What did you do to resolve it?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply