Comment

Nick Mellor January 23, 2009

Hi Peter,

This is a poser for you: an MS SQL view with lots of CASE statements. It's ugly as hell, but that's exactly why I need to pretty-print it. I'm not the original author :-)

SELECT TOP (100) PERCENT SEG.segID, SEG.segEndDate, SEG.segTypeID, SEG.segStartDate, SEG.segItemID, SEG.segQuoteCost, SEG.segNetCost,
SEG.segActualCost, SEG.segBookConfDate, SEG.segBookConfID, SEG.TripID, SEG.operatorID, SEG.segFld1, SEG.segStartTime, SEG.segEndTime,
SEG.attractID, SEG.segStartOfficeID, SEG.segEndOfficeID, SEG.segDays, SEG.segPaymentInfo, SEG.segAdminInfo, SEG.routeID, SEG.segAdults,
SEG.segChildren, SEG.segBabies, SEG.segFamilies, SEG.segPensioners, SEG.segBookRef, SEG.segDesc, SEG.segDateEmailed,
SEG.segDateConfirmed, SEG.segAmended, SEG.passID, SEG.segConfirmed, SEG.segConfComments, SEG.segCommPaid, SEG.segError,
SEG.segStartLocID, SEG.segEndLocID, SEG.segLength, SEG.segPriceStartDate, SEG.segPriceEndDate, SEG.segPriceInfo, SEG.segPricedByAgent,
SEG.segPriceSimple, CASE WHEN SEG.segTypeID = 3 THEN Air.name ELSE OPER.operatorName END AS operatorName,
CASE WHEN SEG.segTypeID = 3 THEN segAdminInfo ELSE CASE WHEN SEG.segItemID IS NOT NULL AND
SEG.segItemID != 0 THEN ITEM.segItemTitle WHEN SEG.routeID IS NOT NULL AND
SEG.routeID != 0 THEN ROUTE.routeTitle WHEN SEG.attractID IS NOT NULL AND
SEG.attractID != 0 THEN ATTRACT.attractTitle WHEN SEG.specialID != 0 AND SEG.specialID IS NOT NULL THEN SPECIAL.Title END END AS itemTitle,
SEGTYPE.segTypeName, CASE WHEN SEG.segTypeID = 4 OR
SEG.segTypeID = 5 OR
SEG.segTypeID = 12 THEN ROUTEOFFSTART.officeNameText ELSE OFFSTART.officeNameText END AS startOfficeText,
CASE WHEN SEG.segTypeID = 4 OR
SEG.segTypeID = 5 OR
SEG.segTypeID = 12 THEN ROUTEOFFEND.officeNameText ELSE OFFEND.officeNameText END AS endOfficeText, dbo.tbltrips.tripNetCost AS tripCost,
LOCSTART.locTitle AS startLocTitle, LOCEND.locTitle AS endLocTitle, CASE WHEN SEG.segTypeID = 3 THEN 0 ELSE 1 END AS IsFlight,
OFFSTART.officePhone, OFFSTART.officeAddress, SEG.segPrevCost, SEG.specialID
FROM dbo.tblsegtypes AS SEGTYPE RIGHT OUTER JOIN
dbo.OfficeList AS ROUTEOFFSTART INNER JOIN
dbo.tblRoute AS ROUTE ON ROUTEOFFSTART.officeID = ROUTE.routeStartOfficeID INNER JOIN
dbo.OfficeList AS ROUTEOFFEND ON ROUTE.routeEndOfficeID = ROUTEOFFEND.officeID RIGHT OUTER JOIN
dbo.tbloperator AS OPER RIGHT OUTER JOIN
dbo.tblsegment AS SEG INNER JOIN
dbo.tbltrips ON SEG.TripID = dbo.tbltrips.tripID LEFT OUTER JOIN
dbo.tblSpecial AS SPECIAL ON SEG.specialID = SPECIAL.specialID LEFT OUTER JOIN
dbo.tbllocation AS LOCEND ON SEG.segEndLocID = LOCEND.locID LEFT OUTER JOIN
dbo.tbllocation AS LOCSTART ON SEG.segStartLocID = LOCSTART.locID LEFT OUTER JOIN
dbo.tblsegitem AS ITEM ON SEG.segItemID = ITEM.segItemID ON OPER.operatorID = SEG.operatorID ON ROUTE.routeID = SEG.routeID ON
SEGTYPE.segTypeID = SEG.segTypeID LEFT OUTER JOIN
dbo.OfficeList AS OFFSTART ON SEG.segStartOfficeID = OFFSTART.officeID LEFT OUTER JOIN
dbo.OfficeList AS OFFEND ON SEG.segEndOfficeID = OFFEND.officeID LEFT OUTER JOIN
dbo.tblattractitem AS ATTRACT ON SEG.attractID = ATTRACT.attractID LEFT OUTER JOIN
dbo.tblGalileoAirline AS AIR ON AIR.code = { fn LEFT(SEG.segFld1, 2) }
WHERE (SEG.segID <> 0) AND (ITEM.segItemNonActive = 0)
ORDER BY SEG.segStartDate, SEG.segEndDate, IsFlight, SEG.segStartTime, SEG.segEndTime