I’m a complainer, I know.
When I signed up for this job, I knew that I would be working with some ColdFusion legacy code with datestamps that go back to the late 1990s. I did not however, realize that the authors were people posing as programmers and that they were masochists, to boot. I get the feeling that some of this spaghetti code would violate the Geneva Conventions. I frequently find myself asking, “Who the hell would do this?”
 |
Here is the query from the ColdFusion error screen that I was presented with today:
SELECT contact.contactid,contact.pub,FirstName, LastName,isnull(company.companyname,' ') companyname,contact.note, (SELECT max(TelNum) FROM ContactPhone WHERE ContactPhone.contactId = Contact.ContactId AND ContactPhone.pub = Contact.Pub AND PrimaryPhone=1) as TelNum FROM Contact left outer join Company on contact.companyid=company.companyid and contact.companypub=company.pub WHERE Contact.Pub=nnnnn and ( ( Contact.Active=1 and contact.deactivatedate is null and contact.pub in (select brokerpub from Brokers_Master bm where bm.status=1 and bm.companyid in (select x.companyid from company x where x.parent_company_id = )) ) or ( contact.contactid in (select aptcontact.contactid from aptcontact where aptcontact.aptPub=nnnnn and aptcontact.aptid = 0) ) ) or ( contact.pub <> 1 and (contact.pub in (select brokerpub from Brokers_Master bm where bm.status=1 and bm.companyid in (select x.companyid from company x where x.parent_company_id = )) or (contact.contactid in (select aptcontact.contactid from aptcontact where aptcontact.aptid = 0 ))) and Contact.deactivatedate is null AND (contact.companyid=0 OR (IsPrimary=1 AND Contact.Pub=nnnnn) OR (contact.CompanyId in (SELECT companyid from company pc1 WHERE parent_company_id = ))) AND Active=1 AND company.GRP='everyone' ) ORDER BY companyname, isnull(PrimaryCompContact,0) desc, Lastname, FirstName, ContactID |
Using the nifty SQL formatter, the result was:
SELECT contact.contactid ,
contact.pub ,
FirstName ,
LastName ,
isnull(company.companyname,' ') companyname,
contact.note ,
(SELECT MAX(TelNum)
FROM ContactPhone
WHERE ContactPhone.contactId = Contact.ContactId
AND ContactPhone.pub = Contact.Pub
AND PrimaryPhone =1
) AS TelNum
FROM Contact
LEFT OUTER JOIN Company
ON contact.companyid =company.companyid
AND contact.companypub=company.pub
WHERE Contact.Pub =nnnnn
AND
(
(
Contact.Active=1
AND contact.deactivatedate IS NULL
AND contact.pub IN
(SELECT brokerpub
FROM Brokers_Master bm
WHERE bm.status =1
AND bm.companyid IN
(SELECT x.companyid
FROM company x
WHERE x.parent_company_id =
)
)
)
OR
(
contact.contactid IN
(SELECT aptcontact.contactid
FROM aptcontact
WHERE aptcontact.aptPub=nnnnn
AND aptcontact.aptid = 0
)
)
)
OR
(
contact.pub <> 1
AND
(
contact.pub IN
(SELECT brokerpub
FROM Brokers_Master bm
WHERE bm.status =1
AND bm.companyid IN
(SELECT x.companyid
FROM company x
WHERE x.parent_company_id =
)
)
OR
(
contact.contactid IN
(SELECT aptcontact.contactid
FROM aptcontact
WHERE aptcontact.aptid = 0
)
)
)
AND Contact.deactivatedate IS NULL
AND
(
contact.companyid=0
OR
(
IsPrimary =1
AND Contact.Pub=nnnnn
)
OR
(
contact.CompanyId IN
(SELECT companyid
FROM company pc1
WHERE parent_company_id =
)
)
)
AND Active =1
AND company.GRP='everyone'
)
ORDER BY companyname ,
isnull(PrimaryCompContact,0) DESC,
Lastname ,
FirstName ,
ContactID
|
Moral of the story: SUBSELECT is not your friend, at least not when it was written by someone else.
Leave a Comment
