View Error - Create View or Function Failed Because No Column Name was Specified for Column 1

Question

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

That is quite a query. I would comment out ALL but one column and try to create it. I would then start to uncomment out one column at a time until I hit the error again. That seems to be simplest way to quickly track it down. — JMabee, May 07 '21 at 20:41
[Edit] the question and provide a [example], i.e. the `CREATE` statements of the tables or other objects involved (paste the **text**, don't use images, don't link to external sites), `INSERT` statements for sample data (dito) and the desired result with that sample data in tabular text format. — sticky bit, May 07 '21 at 21:47
Side note: Don't get used to use single quotes for identifiers such as column aliases. Yes, sadly SQL Server accepts that, but in SQL single quotes are usually for string (or date...) literals. Should you ever use another DBMS (or future SQL Server versions become more sane about this) you'll likely get an error. Use square brackets for identifiers that have special characters in them or are case sensitive or preferably don't use special characters and case sensitive identifiers at all. Identifiers don't need to be "pretty", "pretty" headers and such are a job for the presentation layer. — sticky bit, May 07 '21 at 21:47
@MartinSmith you should definitely post that as an answer. You'd get my vote. — AlwaysLearning, May 08 '21 at 13:07

Martin Smith · Answer 1 · 2021-05-08T14:03:26.650

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

This answer also works when creating a view with a "FOR JSON" query clause. If the error is seen, change from "CREATE VIEW [JSONView] as" to something like "CREATE VIEW [JSONView] ([JSONColumn]) as". — peterk411, Apr 14 '23 at 16:03

View Error - Create View or Function Failed Because No Column Name was Specified for Column 1

1 Answers1