swettywap
Reputation: 1
View Error - Create View or Function Failed Because No Column Name was Specified for Column 1
I am trying to update my view to be in xml format. I keep getting the same error but I have aliased all my columns.... I have validated that every column has an alias. I have included error below.
Create View or Function failed because no column name was specified for column 1.
Any help is appreciated.
USE [SANDBOX_2021]
GO
/****** Object: View [dbo].[vw_BartenderLabelQuery_V2] Script Date: 5/7/2021 1:00:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER view [dbo].[vw_BartenderLabelQuery_V2] as
Select T0.ItemCode as 'ItemCode'
, T0.ItemName 'ItemName'
, T0.U_Genus_Code 'Genus'
, T0.FrgnName 'ForeignName'
, T0.U_LabelDesc 'LabelDescription'
, SUBSTRING(T0.ItemName, PATINDEX('%[0-9]%', T0.ItemName), LEN(T0.ItemName)) 'ItemName2'
, cast(T1.DocNum as varchar(50)) 'ProductionOrder'
, cast(T1.DocNum as varchar(50))'BatchNumber'
, cast(Convert(date,T1.DueDate,112) as varchar(max)) 'ManufactureDate'
, cast(convert(date, T1.Duedate + T0.U_ExpirationDays,112) as varchar(max)) 'ExpirationDate'
, '0' 'StartQtyLabels'
, cast(FLOOR(T1.PlannedQty/T0.U_NetContents) as nvarchar(max)) 'EndQtyLabels'
, T0.U_StaticPressure 'StaticPressure'
, T0.U_HeaterSettings 'HeaterSettings'
, T0.U_DynamicPressure 'DynamicPressure'
, T0.U_HoseHeaters 'HoseHeaters'
, T0.U_StorageTemp 'StorageTemp'
, T0.U_ShelfLife 'ShelfLife'
, Char(34) + T0.U_MixingType + Char(34) 'MixingType'
, Case when T0.U_ApplicationSL = 'Spanish' then (Select char(34) + LNS.U_RecordDescription + Char(34)from [@UDT_BARTENDER_LINES] LNS where LNS.DocEntry = 23 and LNS.U_LanguageGroup = 'S' and LNS.U_RecordID = (Select top 1 U_RecordID + 1 from [@UDT_BARTENDER_LINES] where DocEntry = 23 and U_RecordDescription = T0.U_MixingType))
when T0.U_ApplicationSL = 'French' then (Select char(34) + LNS.U_RecordDescription + Char(34) from [@UDT_BARTENDER_LINES] LNS where LNS.DocEntry = 23 and LNS.U_LanguageGroup = 'F' and LNS.U_RecordID = (Select top 1 U_RecordID + 2 from [@UDT_BARTENDER_LINES] where DocEntry = 23 and U_RecordDescription = T0.U_MixingType)) end 'MixingTypeSecondLanguage'
, cast(T0. U_NetContents as nvarchar(max)) + ' ' + Cast(T0.InvntryUom as nvarchar(max)) 'NetContentsLabel'
, T0.U_Spec 'Spec'
, T0.U_Color 'Color'
, T0.U_Reactivity 'Reactivity'
, T0.U_BorderColor 'BorderColor'
, char(34) + T0.U_Application + cHAR(34) 'Application'
, Case when T0.U_ApplicationSL = 'Spanish' then (Select char(34) + LNS.U_RecordDescription + Char(34) from [@UDT_BARTENDER_LINES] LNS where LNS.DocEntry = 21 and LNS.U_LanguageGroup = 'S' and LNS.U_RecordID = (Select U_RecordID + 1 from [@UDT_BARTENDER_LINES] where DocEntry = 21 and U_RecordDescription = T0.U_Application))
when T0.U_ApplicationSL = 'French' then (Select char(34) + LNS.U_RecordDescription + Char(34) from [@UDT_BARTENDER_LINES] LNS where LNS.DocEntry = 21 and LNS.U_LanguageGroup = 'F' and LNS.U_RecordID = (Select U_RecordID + 2 from [@UDT_BARTENDER_LINES] where DocEntry = 21 and U_RecordDescription = T0.U_Application)) end 'ApplicationSecondLanguauge'
,char(34) + T0.U_Danger + char(34) 'Danger'
, char(34) + T0.U_Danger2 + char(34) 'Danger2'
, char(34) + T0.U_Danger3 + char(34) 'Danger3'
, char(34) + T0.U_Danger4 + char(34)'Danger4'
, char(34) + T0.U_Danger5 + char(34)'Danger5'
, char(34) + T0.U_Danger6 + char(34)'Danger6'
, char(34) + T0.U_Danger7 + char(34)'Danger7'
, char(34) + T0.U_Danger8 + char(34) 'Danger8'
, char(34) + T0.U_Danger9 + char(34) 'Danger9'
, char(34) + T0.U_Danger10 + char(34) 'Danger10'
, char(34) + T0.U_Danger11 + char(34)'Danger11'
, char(34) + T0.U_Danger12 + char(34) 'Danger12'
, char(34) + T0.U_Danger13 + char(34) 'Danger13'
, char(34) + T0.U_Danger14 + char(34) 'Danger14'
, char(34) + ' ' + char(34) 'Danger15'
--, char(34) + T0.U_Danger14 + char(34) 'Danger15'
, char(34) + T0.U_ApprovalsCompliance + char(34) 'ApprovalsCompliance'
, T0.U_ApprovalsCompliance2 'ApprovalsCompliance2'
, T0.U_ApprovalsCompliance3 'Approvalscompliance3'
, T0.U_ApprovalsCompliance4 'ApprovalsCompliance4'
, T0.U_ApprovalsCompliance5 'ApprovalsCompliance5'
, char(34) + T0.U_Precaution + char(34) 'Precaution'
, char(34) + T0.U_Precaution2 + char(34) 'Precaution2'
, char(34) + T0.U_Precaution3 + char(34) 'Precaution3'
, char(34) + T0.U_Precaution4 + char(34) 'Precaution4'
, char(34) + T0.U_Precaution5 + char(34) 'Precaution5'
, char(34) + T0.U_Precaution6 + char(34) 'Precaution6'
, char(34) + T0.U_Precaution7 + char(34) 'Precaution7'
, char(34) + T0.U_Precaution8 + char(34) 'Precaution8'
, char(34) + T0.U_Precaution9 + char(34) 'Precaution9'
, char(34) + T0.U_Precaution10 + char(34) 'DocID'
, char(34) + T0.U_Warning + Char(34) 'Warning'
, char(34) + T0.U_Warning2 + Char(34)'Warning2'
, char(34) + T0.U_Warning3 + Char(34) 'Warning3'
, char(34) + T0.U_Warning4 + Char(34) 'Warning4'
, char(34) + T0.U_Warning5 + Char(34) 'Warning5'
,char(34) + T0.U_Warning6 + Char(34) 'Warning6'
, char(34) + T0.U_Warning7 + Char(34)'Warning7'
, char(34) + T0.U_Warning8 + Char(34) 'Warning8'
, char(34) + T0.U_Warning9 + Char(34) 'Warning9'
, char(34) + T0.U_Warning10 + Char(34) 'Warning10'
, char(34) + T0.U_Warning11 + Char(34) 'Warning11'
, T0.U_MixWell 'MixWell'
, char(34) + T0.U_MixingType2 + char(34) 'MixingType2'
, Case when T0.U_ApplicationSL = 'Spanish' then (Select char(34) + LNS.U_RecordDescription + Char(34)from [@UDT_BARTENDER_LINES] LNS where LNS.DocEntry = 23 and LNS.U_LanguageGroup = 'S' and LNS.U_RecordID = (Select U_RecordID + 1 from [@UDT_BARTENDER_LINES] where DocEntry = 23 and U_RecordDescription = T0.U_MixingType2))
when T0.U_ApplicationSL = 'French' then (Select char(34) + LNS.U_RecordDescription + Char(34) from [@UDT_BARTENDER_LINES] LNS where LNS.DocEntry = 23 and LNS.U_LanguageGroup = 'F' and LNS.U_RecordID = (Select U_RecordID + 2 from [@UDT_BARTENDER_LINES] where DocEntry = 23 and U_RecordDescription = T0.U_MixingType2)) end 'MixingTypeSecondLanguage2'
, char(34) + T0.U_MixingType3 + char(34) 'MixingType3'
, Case when T0.U_ApplicationSL = 'Spanish' then (Select char(34) + LNS.U_RecordDescription + Char(34) from [@UDT_BARTENDER_LINES] LNS where LNS.DocEntry = 23 and LNS.U_LanguageGroup = 'S' and LNS.U_RecordID = (Select U_RecordID + 1 from [@UDT_BARTENDER_LINES] where DocEntry = 23 and U_RecordDescription = T0.U_MixingType3))
when T0.U_ApplicationSL = 'French' then (Select char(34) + LNS.U_RecordDescription + Char(34) from [@UDT_BARTENDER_LINES] LNS where LNS.DocEntry = 23 and LNS.U_LanguageGroup = 'F' and LNS.U_RecordID = (Select U_RecordID + 2 from [@UDT_BARTENDER_LINES] where DocEntry = 23 and U_RecordDescription = T0.U_MixingType3)) end 'MixingTypeSecondLanguage3'
, T0.U_LowVoc 'LowVoc'
, T0.U_Recirculate 'DoNotRecirculate'
, T0.U_CodeReport 'CodeReport'
, T0.U_IconSet 'IconSet'
, T0.U_LabelEntry 'LabelEntry'
, T0.U_Background 'Background'
, Char(34) + T0.U_Application2 + Char(34) 'Application2'
, Case when T0.U_ApplicationSL = 'Spanish' then (Select char(34) + LNS.U_RecordDescription + Char(34) from [@UDT_BARTENDER_LINES] LNS where LNS.DocEntry = 21 and LNS.U_LanguageGroup = 'S' and LNS.U_RecordID = (Select U_RecordID + 1 from [@UDT_BARTENDER_LINES] where DocEntry = 21 and U_RecordDescription = T0.U_Application2))
when T0.U_ApplicationSL = 'French' then (Select char(34) + LNS.U_RecordDescription + Char(34) from [@UDT_BARTENDER_LINES] LNS where LNS.DocEntry = 21 and LNS.U_LanguageGroup = 'F' and LNS.U_RecordID = (Select U_RecordID + 2 from [@UDT_BARTENDER_LINES] where DocEntry = 21 and U_RecordDescription = T0.U_Application2)) end 'ApplicationSecondLanguauge2'
, Char(34) + T0.U_Application3 + Char(34) 'Application3'
, Case when T0.U_ApplicationSL = 'Spanish' then (Select char(34) + LNS.U_RecordDescription + Char(34) from [@UDT_BARTENDER_LINES] LNS where LNS.DocEntry = 21 and LNS.U_LanguageGroup = 'S' and LNS.U_RecordID = (Select U_RecordID + 1 from [@UDT_BARTENDER_LINES] where DocEntry = 21 and U_RecordDescription = T0.U_Application3))
when T0.U_ApplicationSL = 'French' then (Select LNS.U_RecordDescription from [@UDT_BARTENDER_LINES] LNS where LNS.DocEntry = 21 and LNS.U_LanguageGroup = 'F' and LNS.U_RecordID = (Select U_RecordID + 2 from [@UDT_BARTENDER_LINES] where DocEntry = 21 and U_RecordDescription = T0.U_Application3)) end 'ApplicationSecondLanguauge3'
, Char(34) + T0.U_Application4 + Char(34) 'Application4'
, Case when T0.U_ApplicationSL = 'Spanish' then (Select char(34) + LNS.U_RecordDescription + Char(34) from [@UDT_BARTENDER_LINES] LNS where LNS.DocEntry = 21 and LNS.U_LanguageGroup = 'S' and LNS.U_RecordID = (Select U_RecordID + 1 from [@UDT_BARTENDER_LINES] where DocEntry = 21 and U_RecordDescription = T0.U_Application4))
when T0.U_ApplicationSL = 'French' then (Select char(34) + LNS.U_RecordDescription + Char(34) from [@UDT_BARTENDER_LINES] LNS where LNS.DocEntry = 21 and LNS.U_LanguageGroup = 'F' and LNS.U_RecordID = (Select U_RecordID + 2 from [@UDT_BARTENDER_LINES] where DocEntry = 21 and U_RecordDescription = T0.U_Application4)) end 'ApplicationSecondLanguauge4'
, Char(34) + T0.U_Application5 + Char(34) 'Application5'
, Case when T0.U_ApplicationSL = 'Spanish' then (Select char(34) + LNS.U_RecordDescription + Char(34) from [@UDT_BARTENDER_LINES] LNS where LNS.DocEntry = 21 and LNS.U_LanguageGroup = 'S' and LNS.U_RecordID = (Select U_RecordID + 1 from [@UDT_BARTENDER_LINES] where DocEntry = 21 and U_RecordDescription = T0.U_Application5))
when T0.U_ApplicationSL = 'French' then (Select char(34) + LNS.U_RecordDescription + Char(34) from [@UDT_BARTENDER_LINES] LNS where LNS.DocEntry = 21 and LNS.U_LanguageGroup = 'F' and LNS.U_RecordID = (Select U_RecordID + 2 from [@UDT_BARTENDER_LINES] where DocEntry = 21 and U_RecordDescription = T0.U_Application5)) end 'ApplicationSecondLanguauge5'
, Char(34) + T0.U_Application6 + Char(34) 'Application6'
, Case when T0.U_ApplicationSL = 'Spanish' then (Select char(34) + LNS.U_RecordDescription + Char(34) from [@UDT_BARTENDER_LINES] LNS where LNS.DocEntry = 21 and LNS.U_LanguageGroup = 'S' and LNS.U_RecordID = (Select U_RecordID + 1 from [@UDT_BARTENDER_LINES] where DocEntry = 21 and U_RecordDescription = T0.U_Application6))
when T0.U_ApplicationSL = 'French' then (Select char(34) + LNS.U_RecordDescription + Char(34) from [@UDT_BARTENDER_LINES] LNS where LNS.DocEntry = 21 and LNS.U_LanguageGroup = 'F' and LNS.U_RecordID = (Select U_RecordID + 2 from [@UDT_BARTENDER_LINES] where DocEntry = 21 and U_RecordDescription = T0.U_Application6)) end 'ApplicationSecondLanguauge6'
from OITM T0 with(nolock)
Inner Join OWOR T1 with(nolock) on T0.ItemCode = T1.ItemCode
left join OITL T2 with(nolock) on T1.DocEntry = T2.BaseEntry and T2.BaseType = 202 and T2.DocType = 60
left Join ITL1 T3 with(nolock) on T2.LogEntry = T3.LogEntry
for xml auto;
GO
Upvotes: 0
Views: 1981
Answers (1)
Martin Smith
Reputation: 452977
I have validated that every column has an alias
This just controls what the elements are called in the XML the single column XML resultset still needs a column name.
If you just run the SELECT on its own the implicit column name given by SQL Server is XML_F52E2B61-18A1-11d1-B105-00805F49916B. Sticking to this does have an advantage in that SSMS does treat it as XML rather than ntext.
You can declare the name for the alias just after the view name as below
ALTER VIEW [dbo].[vw_BartenderLabelQuery_V2]([XML_F52E2B61-18A1-11d1-B105-00805F49916B]) as
Select T0.ItemCode ...
FOR XML AUTO;
You could also do
ALTER VIEW [dbo].[vw_BartenderLabelQuery_V2]
as
SELECT (SELECT T0.ItemCode ... FOR XML AUTO, TYPE) AS X
Unfortunately neither method retains the XML SELECT root plan operator however
so both methods potentially are at a performance disadvantage compared to just running the SELECT directly
Upvotes: 2
Related Questions
- Cannot create a view in SQL
- Creating a view: error MSG 156, Level 15, State 1
- Getting error while creating View in SQL server
- Creating view in SQL Server and it's giving me a message
- whats the error in creating this sql server view?
- Issue when trying to create View using WITH
- SQL Server : Alter View error - no column name specified
- SQL views - cannot create view
- trying to create view in sql server
- Error while creating view in sql server