April 25, 2011 at 9:57 am
I got the below from one of the views. Can someone explain what these mean
1. row_number() over (Partition by a.CustomerID
order by case when addressTypeId = 17 then 0
when addressTypeId = 1 then 1
else 2 end
How can a order by be 0, i know it can 1,2.
2. ISNULL(adr.rowId, 1) = 1
I know adr.rowId is replaced by 1 if there is a null value in adr.rowId but what does that '=1' mean ??
With myCte (CustomerId, ContactName, Address1, address2, city, stateTerritory, PostalCode, addressTypeId, rowId)
as
( Select c.customerId, a.ContactName, a.address1, a.address2, a.city, a.stateTerritory, a.PostalCode,
a.addressTypeId,
row_number() over (Partition by a.CustomerID
order by case when addressTypeId = 17 then 0
when addressTypeId = 1 then 1
else 2 end ) as rowId
from AX.Customer c
inner join AX.CustomerAddress a on c.CustomerID = a.CustomerID
where a.Active = 'true'
and a.IsPrimary = 'true'
)
SELECTTOP (100) PERCENT
ord.orderId,
oln.OrderLineID,
ord.FirstPubDate,
oln.runDate as PubDate,
ord.LastPubDate,
ord.ProductType,
ord.ProductTypeId,
ad.ZoneID, ad.ZoneName,
ad.SectionID,
ad.SectionName,
ad.PageID,
ad.PageName,
ad.PositionX,
ad.PositionY,
ad.Sales,
ord.OrderDescription,
ord.SoldToCustomerNumber,
ord.SalesPerson,
ord.Status,
ord.Active,
ord.Insertions,
ad.ColorType as Color,
oln.BusinessUnitName,
oln.BusinessUnitAbbreviation,
ord.SoldToCustomer,
oln.VersionNumber,
dsv.Description,
oln.InsertionNo,
oln.TearSheetCount,
oln.VersionDescription,
vpr.ProductName,
vpr.BusinessUnitID,
vpr.ProductTypeName,
adr.ContactName,
oln.ProductId
FROMdbo.vwRpt_Order ord(noLock)
INNER JOIN dbo.vwRpt_OrderLine oln(noLock)ON ord.OrderID = oln.OrderID
LEFT JOIN dbo.vwRpt_Ad ad(noLock)ON oln.OrderId = ad.orderId
And oln.OrderLineID = ad.OrderLineID
INNER JOIN dbo.vwRpt_Product vpr(noLock)ON oln.ProductID = vpr.ProductID
INNER JOIN Display.SectionVersion dsv(noLock)On ad.SectionVersionID = dsv.SectionVersionID
LEFT JOIN myCte adr(noLock)ON ord.SoldToCustomerId = adr.CustomerID
Where
oln.TearSheetCount > 0
and ISNULL(adr.rowId,1) = 1
April 25, 2011 at 10:23 am
if you can post the full script that would be helpful. usually in the order by you would see a column name not a number:unsure:
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 25, 2011 at 12:13 pm
The "order by 0" piece just puts that first, before 1 or 2. It's just a way to sort the data based on more complex rules.
The IsNull part means it will only select rows where adr.rowID = 1 or is null. First, it replaces nulls with 1s, then it checks if that is equal to 1 (since it's in the Where clause, that's what it's doing). Hence, null or has a value of 1 in that column. I'm guessing, but it probably just means whomever wrote this query didn't know about "is null" in a Where clause, and didn't know about how search arguments work with indexes. It could probably be rewritten as "adr.rowID is null". You'll need to check if there are rows that have a 1 in that column to be sure.
- 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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply