It looks like the reason for the original rewrite was due to subquerying
the same table twice. Nothing wrong with that, but the wizard decides to
ensure that every reference is unique, thus the aliasing of the second
reference. You could get around that by adding your own table aliases in
the subqueries:
SELECT o.OpportunityKeyID,
o.OpportunityName,
F1.CustomFieldValue AS MyCustomField1,
F2.CustomFieldValue AS MyCustomField2
FROM tblOpportunities AS o LEFT OUTER JOIN
(
SELECT OpportunityKeyID,
CustomFieldValue
FROM dbo.tvw_CustomOpportunityFieldData AS of1 --NOTE the table
alias here
WHERE OrdinalPosition = 1
) AS F1 ON F1.OpportunityKeyID = o.OpportunityKeyID LEFT OUTER JOIN
(
SELECT OpportunityKeyID,
CustomFieldValue
FROM dbo.tvw_CustomOpportunityFieldData AS of2 --NOTE the table
alias here
WHERE OrdinalPosition = 2
) AS F2 ON F2.OpportunityKeyID = o.OpportunityKeyID
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329322
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
