Hi Jason, I would recommended creating the view from a query window, and not the design view that SQL Management studio uses
Simply add a 'Create View dbo.xxxx AS' before the statement that works and run it in the query window Hope this helps -Dave On Tue, Dec 22, 2009 at 11:34 AM, Jason Durham <jdur...@cti-stl.com> wrote: > > 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:329309 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
