My apologies for posting an OT message to a ColdFusion list. RDMS are so
closely tied to our day-to-day job in writing CF, that perhaps the list will be
tolerant of such a question.
I'm writing a View for our CRM package to use. The SQL works as I've written
it, but Management Studio keeps aliasing my tables and I can't find a way to
either a) prevent Management Studio from adding the aliases or b) correctly
write my own aliases which returns the proper data. If I execute the SQL as
written, it works. However, after it's executed the first time, the View
referenced in the 2nd JOIN is aliased and causes <Unsupported Data Type> to be
returned for MyCustomField2 on subsequent executions.
The query is quite large, but I've simplified it for this list.
WORKING SQL
============================================
SELECT dbo.tblOpportunities.OpportunityKeyID,
dbo.tblOpportunties.OpportunityName,
F1.CustomFieldValue AS MyCustomField1,
F2.CustomFieldValue AS MyCustomField2
FROM tblOpportunities
LEFT OUTER JOIN
(
SELECT OpportunityKeyID, CustomFieldValue
FROM dbo.tvw_CustomOpportunityFieldData
WHERE OrdinalPosition = 1
) AS F1 ON F1.OpportunityKeyID = dbo.tblOpportunities.OpportunityKeyID
LEFT OUTER JOIN
(
SELECT OpportunityKeyID, CustomFieldValue
FROM dbo.tvw_CustomOpportunityFieldData
WHERE OrdinalPosition = 2
) AS F2 ON F2.OpportunityKeyID = dbo.tblOpportunities.OpportunityKeyID
SQL MANAGEMENT STUDIO ALIAS ADDED
================================================
SELECT dbo.tblOpportunities.OpportunityKeyID,
dbo.tblOpportunties.OpportunityName,
F1.CustomFieldValue AS MyCustomField1,
F2.CustomFieldValue AS MyCustomField2
FROM tblOpportunities
LEFT OUTER JOIN
(
SELECT OpportunityKeyID, CustomFieldValue
FROM dbo.tvw_CustomOpportunityFieldData
WHERE OrdinalPosition = 1
) AS F1 ON F1.OpportunityKeyID = dbo.tblOpportunities.OpportunityKeyID
LEFT OUTER JOIN
(
SELECT OpportunityKeyID, CustomFieldValue
FROM dbo.tvw_CustomOpportunityFieldData AS
tvw_CustomOpportunityFieldData_2 /****** THIS WAS ALTERED *******/
WHERE OrdinalPosition = 2
) AS F2 ON F2.OpportunityKeyID = dbo.tblOpportunities.OpportunityKeyID
________________________________
Confidentiality Warning: This e-mail contains information intended only for the
use of the individual or entity named above. If the reader of this e-mail is
not the intended recipient or the employee or agent responsible for delivering
it to the intended recipient, any dissemination, publication or copying of this
e-mail is strictly prohibited. The sender does not accept any responsibility
for any loss, disruption or damage to your data or computer system that may
occur while using data contained in, or transmitted with, this e-mail. If you
have received this e-mail in error, please immediately notify us by return
e-mail.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:329307
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
