Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Senhores boa tarde,
Estou tendo um problema com uma consulta SQL que roda normalmente no servidor de banco, porém, quando tento puxar os dados usando o QLIKVIEW, o mesmo aparece erro. Alguém poderia me da um norte de como resolver, visto que, não sou um usuário iniciante no qliview.
Vagner, você pode postar uma imagem do erro em questão e a query que você está tentando fazer?
Syntax error Unknown statement: WITH IRClassification as ( SELECT IncidentClassificationId Id, ParentId, ID EnumID, d.DisplayName, Ordinal, 1 AS Level, cast(':' + cast(IncidentClassificationId as varchar) + ':' as varchar (100)) AS Struc, cast('\' + cast(d.DisplayName as varchar) + '\' as varchar (100)) AS FullName FROM dbo.IncidentClassification AS t INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id AND d.InsertedBatchId = t.InsertedBatchId WHERE ParentId = 1 UNION ALL SELECT IncidentClassificationId Id, t.ParentId, t.id enumID, d.DisplayName, (i.Level+1)*1000 + t.Ordinal, i.Level+1, cast(i.Struc + cast(IncidentClassificationId as varchar)+ ':' as varchar(100)) AS Struc, cast(i.FullName + cast(d.DisplayName as varchar) + '\' as varchar(100)) AS FullName FROM dbo.IncidentClassification AS t INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id AND d.InsertedBatchId = t.InsertedBatchId JOIN IRClassification i on i.Id = t.ParentId ), Classifications As ( SELECT ISNULL(Parent.Id,Child.Id) ParentId, Child.ID ChildId, Child.enumID, ISNULL(Parent.DisplayName,Child.DisplayName) [Parent Classification], Child.DisplayName Classification, SUBSTRING(Child.FullName,2,LEN(Child.FullName)-2) [Full Classification Path] FROM IRClassification Child LEFT OUTER JOIN IRClassification AS Parent ON Parent.ID = Child.ParentId --ORDER BY Child.FullName ), IRResolutionCategory as ( SELECT IncidentResolutionCategoryId Id, ParentId, ID EnumID, d.DisplayName, Ordinal, 1 AS Level, cast(':' + cast(IncidentResolutionCategoryId as varchar) + ':' as varchar (100)) AS Struc, cast('\' + cast(d.DisplayName as varchar) + '\' as varchar (100)) AS FullName FROM dbo.IncidentResolutionCategory AS t INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id AND d.InsertedBatchId = t.InsertedBatchId WHERE ParentId = 1 UNION ALL SELECT IncidentResolutionCategoryId Id, t.ParentId, t.id enumID, d.DisplayName AS SupportGroup, (i.Level+1)*1000 + t.Ordinal, i.Level+1, cast(i.Struc + cast(IncidentResolutionCategoryId as varchar)+ ':' as varchar(100)) AS Struc, cast(i.FullName + cast(d.DisplayName as varchar) + '\' as varchar(100)) AS FullName FROM dbo.IncidentResolutionCategory AS t INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id AND d.InsertedBatchId = t.InsertedBatchId JOIN IRResolutionCategory i on i.Id = t.ParentId ), ResolutionCategory AS ( SELECT ISNULL(Parent.Id,Child.Id) ParentId, Child.ID ChildId, Child.enumID, ISNULL(Parent.DisplayName,Child.DisplayName) [Parent Incident Resolution Category], Child.DisplayName [Incident Resolution Category] , SUBSTRING(Child.FullName,2,LEN(Child.FullName)-2) [Full Incident Resolution Category Path] FROM IRResolutionCategory Child LEFT OUTER JOIN IRResolutionCategory AS Parent ON Parent.ID = Child.ParentId ), IRTierQueues as ( SELECT IncidentTierQueuesId Id, ParentId, ID EnumID, d.DisplayName, Ordinal, 1 AS Level, cast(':' + cast(IncidentTierQueuesId as varchar) + ':' as varchar (100)) AS Struc, cast('\' + cast(d.DisplayName as varchar) + '\' as varchar (100)) AS FullName FROM dbo.IncidentTierQueuesvw AS t INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id WHERE ParentId = 1 UNION ALL SELECT IncidentTierQueuesId Id, t.ParentId, t.id enumID, d.DisplayName AS SupportGroup, (i.Level+1)*1000 + t.Ordinal, i.Level+1, cast(i.Struc + cast(IncidentTierQueuesId as varchar)+ ':' as varchar(100)) AS Struc, cast(i.FullName + cast(d.DisplayName as varchar) + '\' as varchar(100)) AS FullName FROM dbo.IncidentTierQueuesvw AS t INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id JOIN IRTierQueues i on i.Id = t.ParentId ), TierQueues AS ( SELECT ISNULL(Parent.Id,Child.Id) ParentId, Child.ID ChildId, Child.enumID, ISNULL(Parent.DisplayName,Child.DisplayName) [Parent Tier Queue], Child.DisplayName [Tier Queue] , SUBSTRING(Child.FullName,2,LEN(Child.FullName)-2) [Full Tier Queue Path] FROM IRTierQueues Child LEFT OUTER JOIN IRTierQueues AS Parent ON Parent.ID = Child.ParentId ), IRSource AS ( SELECT ID EnumID, d.DisplayName, Ordinal FROM dbo.IncidentSourcevw AS t INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id ), IRStatus AS ( SELECT ID EnumID, d.DisplayName, Ordinal FROM dbo.IncidentStatusvw AS t INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id ), IRImpact AS ( SELECT ID EnumID, d.DisplayName, Ordinal FROM dbo.IncidentImpactvw AS t INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id ), IRUrgency AS ( SELECT ID EnumID, d.DisplayName, Ordinal FROM dbo.IncidentUrgencyvw AS t INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id ) --------------------------- Incident Query Below --------------------------------------- Select distinct I.ID, I.Title, I.Description, I.CreatedDate, I.ClosedDate, 'Created Time' = CAST(DATEPART(HOUR, I.CreatedDate) AS char(2)) + ':' + CAST(DATEPART(MINUTE, I.CreatedDate)/30*30 AS char(2)), I.[FirstAssignedDate], I.[FirstResponseDate], I.[ResolvedDate], /* CASE WHEN SUM(TimeInMinutes) IS NULL THEN 0 ELSE SUM(TimeInMinutes) END AS 'Support Minutes', CASE WHEN SUM(TimeInMinutes) > @SupportUnits THEN ROUND(SUM(TimeInMinutes)/@SupportUnits,0) WHEN SUM(TimeInMinutes) between 5 and @SupportUnits THEN 1 ELSE 0 END as SupportUnits, */ iclf.[Parent Classification], CASE WHEN iclf.Classification IS NULL THEN 'Unclassified' ELSE iclf.Classification END AS 'Classification', iclf.[Full Classification Path], Afuu.Company 'Affected User Company', Afuu.Department 'Affected User Department', Afuu.Office 'Affected User Office', CASE WHEN Afuu.DisplayName IS NULL THEN 'Unassigned' ELSE Afuu.DisplayName END AS 'Affected User', asuu.Company 'Assigned User Company', asuu.Department 'Assigned User Department', asuu.Office 'Assigned User Office', CASE WHEN asuu.DisplayName IS NULL THEN 'Unassigned' ELSE asuu.DisplayName END AS 'Assigned User', CASE WHEN ircv.[Incident Resolution Category] IS NULL AND isv.DisplayName = 'Active' THEN 'Unresolved' WHEN ircv.[Incident Resolution Category] IS NULL AND isv.DisplayName = 'Closed' THEN 'Unspecified Resolution' WHEN ircv.[Incident Resolution Category] IS NULL AND isv.DisplayName IS NULL THEN 'Unresolved' ELSE ircv.[Incident Resolution Category] END AS 'Incident Resolution Category', CASE WHEN irtq.[Parent Tier Queue] IS NULL THEN 'Unassigned' ELSE irtq.[Parent Tier Queue] END AS 'Parent Tier Queue', CASE WHEN irtq.[Tier Queue] IS NULL THEN 'Unassigned' ELSE irtq.[Tier Queue] END AS 'Tier Queue', CASE WHEN iscv.DisplayName IS NULL THEN 'Unassigned' ELSE iscv.DisplayName END AS 'Source', CASE WHEN isv.DisplayName IS NULL THEN 'New' ELSE isv.DisplayName END AS 'Status', iriv.DisplayName 'Impact', iruv.DisplayName 'Urgency' From IncidentDimvw I -- Work Item left outer join WorkItemDim W on I.EntityDimKey = W.EntityDimKey /* AND W.IsDeleted = 0 */ --Billable Time --left outer join WorkItemHasBillableTimeFactvw BFact on W.WorkItemDimKey = BFact.WorkItemDimKey and Bfact.DeletedDate is Null --left outer join BillableTimeDimvw B on BFact.WorkItemHasBillableTime_BillableTimeDimKey = B.BillableTimeDimKey -- Affected User left outer join WorkItemAffectedUserFactvw afu on W.WorkItemDimKey = afu.WorkItemDimKey /* AND afu.DeletedDate IS NULL */ left outer join UserDim afuu on afu.WorkItemAffectedUser_UserDimKey = afuu.UserDimKey -- Assigned to User left outer join WorkItemAssignedToUserFactvw asu on W.WorkItemDimKey = asu.WorkItemDimKey /* AND asu.DeletedDate IS NULL */ left outer join UserDim asuu on asu.WorkItemAssignedToUser_UserDimKey = asuu.UserDimKey -- Status List left outer join IRStatus isv on I.Status = isv.EnumID -- Source List left outer join IRSource iscv on I.Source = iscv.EnumID -- Impact List left outer join IRImpact iriv on I.Impact = iriv.EnumID -- Urgency List left outer join IRUrgency iruv on I.Urgency = iruv.EnumID --Classification List left outer join IncidentClassification iclv ON iclv.IncidentClassificationId = I.Classification_IncidentClassificationId left outer join Classifications iclf on iclv.IncidentClassificationId = iclf.ChildId --Resolution Category List left outer join ResolutionCategory ircv on I.ResolutionCategory = ircv.EnumID --Tier Queue List left outer join TierQueues irtq on I.TierQueue = irtq.EnumID /* Where I.IsDeleted <> 1 and I.CreatedDate between GETDATE()-@FilterByLastDays and GETDATE() */ Group by I.ID, I.[FirstAssignedDate], I.[FirstResponseDate], I.[ResolvedDate], Afuu.DisplayName, Afuu.Company, Afuu.Department, Afuu.Office, asuu.DisplayName, asuu.Company, asuu.Department, asuu.Office, i.IsDeleted, W.IsDeleted, I.Classification_IncidentClassificationId, I.CreatedDate, I.ClosedDate, iscv.DisplayName, isv.DisplayName, iriv.DisplayName, iruv.Displayname, I.Title, I.Description, I.Classification, I.Classification_IncidentClassificationId,i.ResolutionCategory ,iclf.[Parent Classification], iclf.Classification, iclf.[Full Classification Path] ,ircv.[Incident Resolution Category] ,irtq.[Parent Tier Queue], irtq.[Tier Queue] WITH IRClassification as ( SELECT IncidentClassificationId Id, ParentId, ID EnumID, d.DisplayName, Ordinal, 1 AS Level, cast(':' + cast(IncidentClassificationId as varchar) + ':' as varchar (100)) AS Struc, cast('\' + cast(d.DisplayName as varchar) + '\' as varchar (100)) AS FullName FROM dbo.IncidentClassification AS t INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id AND d.InsertedBatchId = t.InsertedBatchId WHERE ParentId = 1 UNION ALL SELECT IncidentClassificationId Id, t.ParentId, t.id enumID, d.DisplayName, (i.Level+1)*1000 + t.Ordinal, i.Level+1, cast(i.Struc + cast(IncidentClassificationId as varchar)+ ':' as varchar(100)) AS Struc, cast(i.FullName + cast(d.DisplayName as varchar) + '\' as varchar(100)) AS FullName FROM dbo.IncidentClassification AS t INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id AND d.InsertedBatchId = t.InsertedBatchId JOIN IRClassification i on i.Id = t.ParentId ), Classifications As ( SELECT ISNULL(Parent.Id,Child.Id) ParentId, Child.ID ChildId, Child.enumID, ISNULL(Parent.DisplayName,Child.DisplayName) [Parent Classification], Child.DisplayName Classification, SUBSTRING(Child.FullName,2,LEN(Child.FullName)-2) [Full Classification Path] FROM IRClassification Child LEFT OUTER JOIN IRClassification AS Parent ON Parent.ID = Child.ParentId --ORDER BY Child.FullName ), IRResolutionCategory as ( SELECT IncidentResolutionCategoryId Id, ParentId, ID EnumID, d.DisplayName, Ordinal, 1 AS Level, cast(':' + cast(IncidentResolutionCategoryId as varchar) + ':' as varchar (100)) AS Struc, cast('\' + cast(d.DisplayName as varchar) + '\' as varchar (100)) AS FullName FROM dbo.IncidentResolutionCategory AS t INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id AND d.InsertedBatchId = t.InsertedBatchId WHERE ParentId = 1 UNION ALL SELECT IncidentResolutionCategoryId Id, t.ParentId, t.id enumID, d.DisplayName AS SupportGroup, (i.Level+1)*1000 + t.Ordinal, i.Level+1, cast(i.Struc + cast(IncidentResolutionCategoryId as varchar)+ ':' as varchar(100)) AS Struc, cast(i.FullName + cast(d.DisplayName as varchar) + '\' as varchar(100)) AS FullName FROM dbo.IncidentResolutionCategory AS t INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id AND d.InsertedBatchId = t.InsertedBatchId JOIN IRResolutionCategory i on i.Id = t.ParentId ), ResolutionCategory AS ( SELECT ISNULL(Parent.Id,Child.Id) ParentId, Child.ID ChildId, Child.enumID, ISNULL(Parent.DisplayName,Child.DisplayName) [Parent Incident Resolution Category], Child.DisplayName [Incident Resolution Category] , SUBSTRING(Child.FullName,2,LEN(Child.FullName)-2) [Full Incident Resolution Category Path] FROM IRResolutionCategory Child LEFT OUTER JOIN IRResolutionCategory AS Parent ON Parent.ID = Child.ParentId ), IRTierQueues as ( SELECT IncidentTierQueuesId Id, ParentId, ID EnumID, d.DisplayName, Ordinal, 1 AS Level, cast(':' + cast(IncidentTierQueuesId as varchar) + ':' as varchar (100)) AS Struc, cast('\' + cast(d.DisplayName as varchar) + '\' as varchar (100)) AS FullName FROM dbo.IncidentTierQueuesvw AS t INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id WHERE ParentId = 1 UNION ALL SELECT IncidentTierQueuesId Id, t.ParentId, t.id enumID, d.DisplayName AS SupportGroup, (i.Level+1)*1000 + t.Ordinal, i.Level+1, cast(i.Struc + cast(IncidentTierQueuesId as varchar)+ ':' as varchar(100)) AS Struc, cast(i.FullName + cast(d.DisplayName as varchar) + '\' as varchar(100)) AS FullName FROM dbo.IncidentTierQueuesvw AS t INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id JOIN IRTierQueues i on i.Id = t.ParentId ), TierQueues AS ( SELECT ISNULL(Parent.Id,Child.Id) ParentId, Child.ID ChildId, Child.enumID, ISNULL(Parent.DisplayName,Child.DisplayName) [Parent Tier Queue], Child.DisplayName [Tier Queue] , SUBSTRING(Child.FullName,2,LEN(Child.FullName)-2) [Full Tier Queue Path] FROM IRTierQueues Child LEFT OUTER JOIN IRTierQueues AS Parent ON Parent.ID = Child.ParentId ), IRSource AS ( SELECT ID EnumID, d.DisplayName, Ordinal FROM dbo.IncidentSourcevw AS t INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id ), IRStatus AS ( SELECT ID EnumID, d.DisplayName, Ordinal FROM dbo.IncidentStatusvw AS t INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id ), IRImpact AS ( SELECT ID EnumID, d.DisplayName, Ordinal FROM dbo.IncidentImpactvw AS t INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id ), IRUrgency AS ( SELECT ID EnumID, d.DisplayName, Ordinal FROM dbo.IncidentUrgencyvw AS t INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = @Language AND d.ElementName = t.Id ) --------------------------- Incident Query Below --------------------------------------- Select distinct I.ID, I.Title, I.Description, I.CreatedDate, I.ClosedDate, 'Created Time' = CAST(DATEPART(HOUR, I.CreatedDate) AS char(2)) + ':' + CAST(DATEPART(MINUTE, I.CreatedDate)/30*30 AS char(2)), I.[FirstAssignedDate], I.[FirstResponseDate], I.[ResolvedDate], /* CASE WHEN SUM(TimeInMinutes) IS NULL THEN 0 ELSE SUM(TimeInMinutes) END AS 'Support Minutes', CASE WHEN SUM(TimeInMinutes) > @SupportUnits THEN ROUND(SUM(TimeInMinutes)/@SupportUnits,0) WHEN SUM(TimeInMinutes) between 5 and @SupportUnits THEN 1 ELSE 0 END as SupportUnits, */ iclf.[Parent Classification], CASE WHEN iclf.Classification IS NULL THEN 'Unclassified' ELSE iclf.Classification END AS 'Classification', iclf.[Full Classification Path], Afuu.Company 'Affected User Company', Afuu.Department 'Affected User Department', Afuu.Office 'Affected User Office', CASE WHEN Afuu.DisplayName IS NULL THEN 'Unassigned' ELSE Afuu.DisplayName END AS 'Affected User', asuu.Company 'Assigned User Company', asuu.Department 'Assigned User Department', asuu.Office 'Assigned User Office', CASE WHEN asuu.DisplayName IS NULL THEN 'Unassigned' ELSE asuu.DisplayName END AS 'Assigned User', CASE WHEN ircv.[Incident Resolution Category] IS NULL AND isv.DisplayName = 'Active' THEN 'Unresolved' WHEN ircv.[Incident Resolution Category] IS NULL AND isv.DisplayName = 'Closed' THEN 'Unspecified Resolution' WHEN ircv.[Incident Resolution Category] IS NULL AND isv.DisplayName IS NULL THEN 'Unresolved' ELSE ircv.[Incident Resolution Category] END AS 'Incident Resolution Category', CASE WHEN irtq.[Parent Tier Queue] IS NULL THEN 'Unassigned' ELSE irtq.[Parent Tier Queue] END AS 'Parent Tier Queue', CASE WHEN irtq.[Tier Queue] IS NULL THEN 'Unassigned' ELSE irtq.[Tier Queue] END AS 'Tier Queue', CASE WHEN iscv.DisplayName IS NULL THEN 'Unassigned' ELSE iscv.DisplayName END AS 'Source', CASE WHEN isv.DisplayName IS NULL THEN 'New' ELSE isv.DisplayName END AS 'Status', iriv.DisplayName 'Impact', iruv.DisplayName 'Urgency' From IncidentDimvw I -- Work Item left outer join WorkItemDim W on I.EntityDimKey = W.EntityDimKey /* AND W.IsDeleted = 0 */ --Billable Time --left outer join WorkItemHasBillableTimeFactvw BFact on W.WorkItemDimKey = BFact.WorkItemDimKey and Bfact.DeletedDate is Null --left outer join BillableTimeDimvw B on BFact.WorkItemHasBillableTime_BillableTimeDimKey = B.BillableTimeDimKey -- Affected User left outer join WorkItemAffectedUserFactvw afu on W.WorkItemDimKey = afu.WorkItemDimKey /* AND afu.DeletedDate IS NULL */ left outer join UserDim afuu on afu.WorkItemAffectedUser_UserDimKey = afuu.UserDimKey -- Assigned to User left outer join WorkItemAssignedToUserFactvw asu on W.WorkItemDimKey = asu.WorkItemDimKey /* AND asu.DeletedDate IS NULL */ left outer join UserDim asuu on asu.WorkItemAssignedToUser_UserDimKey = asuu.UserDimKey -- Status List left outer join IRStatus isv on I.Status = isv.EnumID -- Source List left outer join IRSource iscv on I.Source = iscv.EnumID -- Impact List left outer join IRImpact iriv on I.Impact = iriv.EnumID -- Urgency List left outer join IRUrgency iruv on I.Urgency = iruv.EnumID --Classification List left outer join IncidentClassification iclv ON iclv.IncidentClassificationId = I.Classification_IncidentClassificationId left outer join Classifications iclf on iclv.IncidentClassificationId = iclf.ChildId --Resolution Category List left outer join ResolutionCategory ircv on I.ResolutionCategory = ircv.EnumID --Tier Queue List left outer join TierQueues irtq on I.TierQueue = irtq.EnumID /* Where I.IsDeleted <> 1 and I.CreatedDate between GETDATE()-@FilterByLastDays and GETDATE() */ Group by I.ID, I.[FirstAssignedDate], I.[FirstResponseDate], I.[ResolvedDate], Afuu.DisplayName, Afuu.Company, Afuu.Department, Afuu.Office, asuu.DisplayName, asuu.Company, asuu.Department, asuu.Office, i.IsDeleted, W.IsDeleted, I.Classification_IncidentClassificationId, I.CreatedDate, I.ClosedDate, iscv.DisplayName, isv.DisplayName, iriv.DisplayName, iruv.Displayname, I.Title, I.Description, I.Classification, I.Classification_IncidentClassificationId,i.ResolutionCategory ,iclf.[Parent Classification], iclf.Classification, iclf.[Full Classification Path] ,ircv.[Incident Resolution Category] ,irtq.[Parent Tier Queue], irtq.[Tier Queue]