August 22, 2016 at 4:42 am
I am hoping someone can help me?
I have been tasked with updating a report but for some reason when I try to run the SQL emabedded in the SSRS Report it does not return any results. But it works fine within the report.
I am passing in the exact same params so is a bit odd. The SQL is below:
declare @RefNumber varchar
set @RefNumber = ''
declare @AuditNote varchar
set @AuditNote = '2014'
declare @Loc varchar
set @Loc = 'welbeck'
--set @Loc = 'dudbridge'
declare @SalesStatus varchar
declare @Title varchar
declare @ExceptTitle varchar
declare @DHS varchar
declare @ArtworkStatus varchar
set @SalesStatus = '3,4,5,36,115,116,117,118,119,120'
set @ArtworkStatus = '1,2,3,4,5,6,7,8,9,10,11'
set @DHS = '1,2,5,6'
SELECT a.ArtworkId, dhs.DHCategory, a.RefNumber, a.Title ArtworkTitle, ast.Status, ss.Name SalesStatus, m.Name Medium, ct.Aka --, a.CompletionYear ---- selecting multiple fields line1
, CratesAndComp.Title, CratesAndComp.PackStatus, CratesAndComp.Location, CratesAndComp.LocationDetail
, CratesAndComp.Dimensions, Height, [Length], Depth, convert(decimal(25,5),convert(bigint,Height) *convert(bigint,[Length]) * convert(bigint,Depth))/1000000000 Volume
, case CratesAndComp.InsuranceComp when 0 then 0 else cst.TotalCost end TotalCost ---- selecting multiple fields line1
, isnull(HasAuditYear,0) HasAuditYear
FROM (select * from Artwork
where (
@RefNumber = ''
or (RefNumber in (select nstr from [dbo].[fn_iterStinglistToTable](@RefNumber,',')) and charindex(',',@RefNumber) >0)
or (RefNumber between (select min(nstr) from [dbo].[fn_iterStinglistToTable](@RefNumber,'-'))
and (select max(nstr) from [dbo].[fn_iterStinglistToTable](@RefNumber,'-')) and charindex('-',@RefNumber) >0)
or ( convert(varchar(100),RefNumber)= @RefNumber)
)
) a
LEFT JOIN (select ArtworkId, count(1) HasAuditYear -- display only artworks with note of type audit and description of note = Momart XXXX
from Note n
join NoteType nt on n.TypeId = nt.NoteTypeId
where convert(varchar(100),nt.Name) like '%' + @AuditNote + '%'
group by ArtworkId) nt on a.ArtworkId = nt.ArtworkId
INNER JOIN (select ac.ArtworkId, case when ac.StatusId in (2,3, 4) then 'Archive - ' + convert(varchar(4),isnull(orderno,'')) else convert(varchar(4),isnull(orderno,'')) end + '/' + convert(varchar(4),isnull(nocrates,0)) + ' ' + isnull(ac.Title,'') + ' ' + isnull(act.Name,'') Title ---- inner join
, ms.Name Location, case when NoComponents = Packed or (isnull(Packed,0) <> 0 and comp.PackedInsuredComponent =1) then isnull(comp.InsuranceComp,0) else 0 end InsuranceComp
, dbo.fn_getCrateDimensionsInString(ac.CrateId, ',') Dimensions, ac.Height, ac.[Length], ac.Depth
, case when isnull(Packed,0) = 0 then 'Empty' ----
when NoComponents = Packed then 'Packed' ----
else 'PartlyPacked' end PackStatus, ac.LocationDetail
from ArtworkCrate ac ----
left join (select artworkid, count(1) NoCrates ---- join
from ArtworkCrate ac---- from artworkcrate table
LEFT JOIN MovementSite ms on ac.LocationId = ms.MovementSiteId ---- left join
where History=0
and StatusId not in (2,3,4)
group by artworkid) nocrt on ac.artworkid = nocrt.artworkid ---- group by artworkid
left join ArtworkCrateType act on ac.TypeId = act.CrateTypeId ---- left join
LEFT JOIN (select CrateId, sum(convert(smallint,isnull(InsuranceComp,0))) InsuranceComp, sum(case when PackStatus = 1 then 1 else 0 end) 'Unpacked' ---- left join
, sum(case when PackStatus = 2 then 1 else 0 end) 'Packed', count(1) NoComponents
, sum(case when PackStatus = 2 and InsuranceComp=1 then 1 else 0 end) PackedInsuredComponent
from ArtworkComponent ---- from artworkcomponent table
group by CrateId) comp on ac.CrateId = comp.CrateId ---- group by crateid
LEFT JOIN MovementSite ms on ac.LocationId = ms.MovementSiteId ---- left join
where ac.History = 0 ---- where acc.history field = zero
and (
(@Loc = '' or case when CHARINDEX('/', ms.Name) = 0 then ms.Name else substring(ms.Name,1,CHARINDEX('/',ms.Name)-1) end like '%' + @Loc + '%') ----
or
(@Loc = 'Chalford' and case when CHARINDEX('/',ms.Name) = 0 then ms.Name else substring(ms.Name,1,CHARINDEX('/',ms.Name)-1) end like '%' + @Loc + '%') ----
or
(@Loc = 'Chalford' and case when CHARINDEX('/',ms.Name) = 0 then ms.Name else substring(ms.Name,1,CHARINDEX('/',ms.Name)-1) end like '%Unit 21%') ----
)
union all ---- !!!
select ac.ArtworkId, case when ac.StatusId in (2,3, 4) then 'Archive - ' + isnull(Title,'') else isnull(Title,'') end Title, ms.Name Location
, ac.InsuranceComp----case when acins.InsuranceComp > 0 then 1 else 0 end InsuranceComp---case when CrateId is null then ac.InsuranceComp else 0 end InsuranceComp ---- select fields
, dbo.fn_getComponentDimensionsInString(ac.ComponentId, ',') Dimensions, ac.Height, ac.[Length], ac.Depth
, cps.ComponentPackStatus, ac.LocationDetail
from ArtworkComponent ac ---- from artworkcomponent table
LEFT JOIN ComponentPackStatus cps on ac.PackStatus = cps.ComponentPackStatusId
LEFT JOIN MovementSite ms on ac.LocationId = ms.MovementSiteId ---- left join
---left join (select ArtworkId, sum(convert(int,isnull(InsuranceComp,0))) InsuranceComp from ArtworkComponent group by ArtworkId) acins on ac.ArtworkId = acins.ArtworkId
where (CrateId is null or (CrateId is not null and isnull(PackStatus,0) != 2)) ---- To display allocated but not packed components because they are at the same location as the crate
and ac.History = 0
and (
(@Loc = '' or case when CHARINDEX('/', ms.Name) = 0 then ms.Name else substring(ms.Name,1,CHARINDEX('/',ms.Name)-1) end like '%' + @Loc + '%') ----
or
(@Loc = 'Chalford' and case when CHARINDEX('/',ms.Name) = 0 then ms.Name else substring(ms.Name,1,CHARINDEX('/',ms.Name)-1) end like '%' + @Loc + '%') ----
or
(@Loc = 'Chalford' and case when CHARINDEX('/',ms.Name) = 0 then ms.Name else substring(ms.Name,1,CHARINDEX('/',ms.Name)-1) end like '%Unit 21%') ----
)
) CratesAndComp on a.ArtworkId = CratesAndComp.ArtworkId ---- and ac.history field = zero
INNER JOIN DHCategory dhs on a.DHCategoryId = dhs.DHCategoryId ---- inner join
INNER JOIN ArtworkStatus ast on a.StatusId = ast.StatusId ---- inner join
LEFT JOIN Medium m ON a.MediumId = m.MediumId ---- inner join
LEFT JOIN (select artworkid, sum(EqPounds) TotalCost ---- left join
from Cost ---- from cost table
group by ArtworkId) cst on a.ArtworkId = cst.ArtworkId ---- group by artworkid field
LEFT JOIN (select ArtworkId, max(case when LevelId =1 then Name end) MediumType, max(case when LevelId =4 then Name end) AKA ---- left join
from Artwork_Category ac ---- from artwork_category table
join Category c on ac.CategoryId = c.CategoryId ---- join
group by artworkid) ct on a.ArtworkId = ct.ArtworkId ---- group by
LEFT JOIN SalesStatus ss on a.ConsignmentStatusId = ss.SalesStatusId ---- left join
LEFT JOIN (select nstr from [dbo].[fn_iterStinglistToTable] (@ExceptTitle,',')) ExceptTitle on CratesAndComp.Title like '%' + case when @ExceptTitle ='' then CratesAndComp.Title else ExceptTitle.nstr end + '%' ---- left join
WHERE dhs.DHCategoryId in (@DHS) ----
and ast.Status in (@ArtworkStatus) ----
and (@Title ='' or CratesAndComp.Title like '%' + @Title + '%') ----
and (@ExceptTitle = '' or isnull(ExceptTitle.nstr,'0')='0') ----
and isnull(a.ConsignmentStatusId,-1) in (@SalesStatus) ----
order by convert(int,a.RefNumber) ---- order by4
I have broken it into parts and each part individually return reults but just not when combined together. It’s so odd that it works fine in the report, which just embeds the above SQL into the report, it doesn’t call a sproc.
I have broken it into the follow sections:
declare @RefNumber varchar
set @RefNumber = ''
declare @AuditNote varchar
set @AuditNote = '2014'
declare @Loc varchar
set @Loc = 'welbeck'
declare @SalesStatus varchar
declare @Title varchar
declare @ExceptTitle varchar
declare @DHS varchar
declare @ArtworkStatus varchar
set @SalesStatus = '3,4,5,36,115,116,117,118,119,120'
set @ArtworkStatus = '1,2,3,4,5,6,7,8,9,10,11'
THIS RETURNS RESULTS:
select * from Artwork
where (
@RefNumber = ''
or (RefNumber in (select nstr from [dbo].[fn_iterStinglistToTable](@RefNumber,',')) and charindex(',',@RefNumber) >0)
or (RefNumber between (select min(nstr) from [dbo].[fn_iterStinglistToTable](@RefNumber,'-'))
and (select max(nstr) from [dbo].[fn_iterStinglistToTable](@RefNumber,'-')) and charindex('-',@RefNumber) >0)
or ( convert(varchar(100),RefNumber)= @RefNumber)
)
THIS RETURNS RESULTS:
(select ArtworkId, count(1) HasAuditYear -- display only artworks with note of type audit and description of note = Momart XXXX
from Note n
join NoteType nt on n.TypeId = nt.NoteTypeId
where convert(varchar(100),nt.Name) like '%' + @AuditNote + '%'
group by ArtworkId)
THIS RETURNS RESULTS:
select ac.ArtworkId, case when ac.StatusId in (2,3, 4) then 'Archive - ' + isnull(Title,'') else isnull(Title,'') end Title, ms.Name Location
, ac.InsuranceComp----case when acins.InsuranceComp > 0 then 1 else 0 end InsuranceComp---case when CrateId is null then ac.InsuranceComp else 0 end InsuranceComp ---- select fields
, dbo.fn_getComponentDimensionsInString(ac.ComponentId, ',') Dimensions, ac.Height, ac.[Length], ac.Depth
, cps.ComponentPackStatus, ac.LocationDetail
from ArtworkComponent ac ---- from artworkcomponent table
LEFT JOIN ComponentPackStatus cps on ac.PackStatus = cps.ComponentPackStatusId
LEFT JOIN MovementSite ms on ac.LocationId = ms.MovementSiteId ---- left join
---left join (select ArtworkId, sum(convert(int,isnull(InsuranceComp,0))) InsuranceComp from ArtworkComponent group by ArtworkId) acins on ac.ArtworkId = acins.ArtworkId
where (CrateId is null or (CrateId is not null and isnull(PackStatus,0) != 2)) ---- To display allocated but not packed components because they are at the same location as the crate
and ac.History = 0
and (
(@Loc = '' or case when CHARINDEX('/', ms.Name) = 0 then ms.Name else substring(ms.Name,1,CHARINDEX('/',ms.Name)-1) end like '%' + @Loc + '%') ----
or
(@Loc = 'Chalford' and case when CHARINDEX('/',ms.Name) = 0 then ms.Name else substring(ms.Name,1,CHARINDEX('/',ms.Name)-1) end like '%' + @Loc + '%') ----
or
(@Loc = 'Chalford' and case when CHARINDEX('/',ms.Name) = 0 then ms.Name else substring(ms.Name,1,CHARINDEX('/',ms.Name)-1) end like '%Unit 21%') ----
)
THIS RETURNS RESULTS:
select ac.ArtworkId, case when ac.StatusId in (2,3, 4) then 'Archive - ' + convert(varchar(4),isnull(orderno,'')) else convert(varchar(4),isnull(orderno,'')) end + '/' + convert(varchar(4),isnull(nocrates,0)) + ' ' + isnull(ac.Title,'') + ' ' + isnull(act.Name,'') Title ---- inner join
, ms.Name Location, case when NoComponents = Packed or (isnull(Packed,0) <> 0 and comp.PackedInsuredComponent =1) then isnull(comp.InsuranceComp,0) else 0 end InsuranceComp
, dbo.fn_getCrateDimensionsInString(ac.CrateId, ',') Dimensions, ac.Height, ac.[Length], ac.Depth
, case when isnull(Packed,0) = 0 then 'Empty' ----
when NoComponents = Packed then 'Packed' ----
else 'PartlyPacked' end PackStatus, ac.LocationDetail
from ArtworkCrate ac ----
left join (select artworkid, count(1) NoCrates ---- join
from ArtworkCrate ac---- from artworkcrate table
LEFT JOIN MovementSite ms on ac.LocationId = ms.MovementSiteId ---- left join
where History=0
and StatusId not in (2,3,4)
group by artworkid) nocrt on ac.artworkid = nocrt.artworkid ---- group by artworkid
left join ArtworkCrateType act on ac.TypeId = act.CrateTypeId ---- left join
LEFT JOIN (select CrateId, sum(convert(smallint,isnull(InsuranceComp,0))) InsuranceComp, sum(case when PackStatus = 1 then 1 else 0 end) 'Unpacked' ---- left join
, sum(case when PackStatus = 2 then 1 else 0 end) 'Packed', count(1) NoComponents
, sum(case when PackStatus = 2 and InsuranceComp=1 then 1 else 0 end) PackedInsuredComponent
from ArtworkComponent ---- from artworkcomponent table
group by CrateId) comp on ac.CrateId = comp.CrateId ---- group by crateid
LEFT JOIN MovementSite ms on ac.LocationId = ms.MovementSiteId ---- left join
where ac.History = 0 ---- where acc.history field = zero
and (
(@Loc = '' or case when CHARINDEX('/', ms.Name) = 0 then ms.Name else substring(ms.Name,1,CHARINDEX('/',ms.Name)-1) end like '%' + @Loc + '%') ----
or
(@Loc = 'Chalford' and case when CHARINDEX('/',ms.Name) = 0 then ms.Name else substring(ms.Name,1,CHARINDEX('/',ms.Name)-1) end like '%' + @Loc + '%') ----
or
(@Loc = 'Chalford' and case when CHARINDEX('/',ms.Name) = 0 then ms.Name else substring(ms.Name,1,CHARINDEX('/',ms.Name)-1) end like '%Unit 21%') ----
)
THIS DOESN’T RETURN RESULTS:
SELECT a.ArtworkId, a.RefNumber, a.Title ArtworkTitle,
CratesAndComp.Title, CratesAndComp.PackStatus, CratesAndComp.Location, CratesAndComp.LocationDetail
, CratesAndComp.Dimensions, Height, [Length], Depth, convert(decimal(25,5),convert(bigint,Height) *convert(bigint,[Length]) * convert(bigint,Depth))/1000000000 Volume
, isnull(HasAuditYear,0) HasAuditYear
FROM (select * from Artwork
where (
@RefNumber = ''
or (RefNumber in (select nstr from [dbo].[fn_iterStinglistToTable](@RefNumber,',')) and charindex(',',@RefNumber) >0)
or (RefNumber between (select min(nstr) from [dbo].[fn_iterStinglistToTable](@RefNumber,'-'))
and (select max(nstr) from [dbo].[fn_iterStinglistToTable](@RefNumber,'-')) and charindex('-',@RefNumber) >0)
or ( convert(varchar(100),RefNumber)= @RefNumber)
)
) a
LEFT JOIN (select ArtworkId, count(1) HasAuditYear -- display only artworks with note of type audit and description of note = Momart XXXX
from Note n
join NoteType nt on n.TypeId = nt.NoteTypeId
where convert(varchar(100),nt.Name) like '%' + @AuditNote + '%'
group by ArtworkId) nt on a.ArtworkId = nt.ArtworkId
INNER JOIN (select ac.ArtworkId, case when ac.StatusId in (2,3, 4) then 'Archive - ' + convert(varchar(4),isnull(orderno,'')) else convert(varchar(4),isnull(orderno,'')) end + '/' + convert(varchar(4),isnull(nocrates,0)) + ' ' + isnull(ac.Title,'') + ' ' + isnull(act.Name,'') Title ---- inner join
, ms.Name Location, case when NoComponents = Packed or (isnull(Packed,0) <> 0 and comp.PackedInsuredComponent =1) then isnull(comp.InsuranceComp,0) else 0 end InsuranceComp
, dbo.fn_getCrateDimensionsInString(ac.CrateId, ',') Dimensions, ac.Height, ac.[Length], ac.Depth
, case when isnull(Packed,0) = 0 then 'Empty' ----
when NoComponents = Packed then 'Packed' ----
else 'PartlyPacked' end PackStatus, ac.LocationDetail
from ArtworkCrate ac ----
left join (select artworkid, count(1) NoCrates ---- join
from ArtworkCrate ac---- from artworkcrate table
LEFT JOIN MovementSite ms on ac.LocationId = ms.MovementSiteId ---- left join
where History=0
and StatusId not in (2,3,4)
group by artworkid) nocrt on ac.artworkid = nocrt.artworkid ---- group by artworkid
left join ArtworkCrateType act on ac.TypeId = act.CrateTypeId ---- left join
LEFT JOIN (select CrateId, sum(convert(smallint,isnull(InsuranceComp,0))) InsuranceComp, sum(case when PackStatus = 1 then 1 else 0 end) 'Unpacked' ---- left join
, sum(case when PackStatus = 2 then 1 else 0 end) 'Packed', count(1) NoComponents
, sum(case when PackStatus = 2 and InsuranceComp=1 then 1 else 0 end) PackedInsuredComponent
from ArtworkComponent ---- from artworkcomponent table
group by CrateId) comp on ac.CrateId = comp.CrateId ---- group by crateid
LEFT JOIN MovementSite ms on ac.LocationId = ms.MovementSiteId ---- left join
where ac.History = 0 ---- where acc.history field = zero
and (
(@Loc = '' or case when CHARINDEX('/', ms.Name) = 0 then ms.Name else substring(ms.Name,1,CHARINDEX('/',ms.Name)-1) end like '%' + @Loc + '%') ----
or
(@Loc = 'Chalford' and case when CHARINDEX('/',ms.Name) = 0 then ms.Name else substring(ms.Name,1,CHARINDEX('/',ms.Name)-1) end like '%' + @Loc + '%') ----
or
(@Loc = 'Chalford' and case when CHARINDEX('/',ms.Name) = 0 then ms.Name else substring(ms.Name,1,CHARINDEX('/',ms.Name)-1) end like '%Unit 21%') ----
)
union all ---- !!!
select ac.ArtworkId, case when ac.StatusId in (2,3, 4) then 'Archive - ' + isnull(Title,'') else isnull(Title,'') end Title, ms.Name Location
, ac.InsuranceComp----case when acins.InsuranceComp > 0 then 1 else 0 end InsuranceComp---case when CrateId is null then ac.InsuranceComp else 0 end InsuranceComp ---- select fields
, dbo.fn_getComponentDimensionsInString(ac.ComponentId, ',') Dimensions, ac.Height, ac.[Length], ac.Depth
, cps.ComponentPackStatus, ac.LocationDetail
from ArtworkComponent ac ---- from artworkcomponent table
LEFT JOIN ComponentPackStatus cps on ac.PackStatus = cps.ComponentPackStatusId
LEFT JOIN MovementSite ms on ac.LocationId = ms.MovementSiteId ---- left join
---left join (select ArtworkId, sum(convert(int,isnull(InsuranceComp,0))) InsuranceComp from ArtworkComponent group by ArtworkId) acins on ac.ArtworkId = acins.ArtworkId
where (CrateId is null or (CrateId is not null and isnull(PackStatus,0) != 2)) ---- To display allocated but not packed components because they are at the same location as the crate
and ac.History = 0
and (
(@Loc = '' or case when CHARINDEX('/', ms.Name) = 0 then ms.Name else substring(ms.Name,1,CHARINDEX('/',ms.Name)-1) end like '%' + @Loc + '%') ----
or
(@Loc = 'Chalford' and case when CHARINDEX('/',ms.Name) = 0 then ms.Name else substring(ms.Name,1,CHARINDEX('/',ms.Name)-1) end like '%' + @Loc + '%') ----
or
(@Loc = 'Chalford' and case when CHARINDEX('/',ms.Name) = 0 then ms.Name else substring(ms.Name,1,CHARINDEX('/',ms.Name)-1) end like '%Unit 21%') ----
)
) CratesAndComp on a.ArtworkId = CratesAndComp.ArtworkId ---- and ac.history field = zero
Any help would be greatly appreciated.
Many thanks,
David.
August 22, 2016 at 7:39 am
Breaking up the query changed results. For example, the predicate
nt on a.ArtworkId = nt.ArtworkId
was not tested.
And to be sure that T-SQL is what SSRS is running, it would be best to trace or collect an extended event.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply