hi,
i am passing date as parameter in a stored procedure, i was joining two tables to display them in report,Summary table and Values table
i was getting values when date has one record , if there are more than one record i am getting below error
" Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. "
my SP is like this
ALTER PROCEDURE [dbo].[SP_RPT_SHP_TEST]
@rdate varchar(50)
AS
declare @saildt varchar(50)
BEGIN
SET NOCOUNT ON;
SET @rDate=(SELECT REPLACE(@rdate,'"',''))
Set @saildt=(select SailDate from TRAN_SHIPMENT_SUMMARY where SailDate= @rDate )
BEGIN
--if(@saildt=@rDate)
--begin
--select datename(month,TSSV.SailDate) as [Month]
--select TSSV.Shp_summary_Id,TSSV.SailDate,
select (select(datename(month,TSSV.SailDate))) as [Month], (select Vessel_Name from getVessel where Vessel_ID=TSSV.Vessel_Id) AS [Vessel],TSSV.SailDate as 'Sailed On',TSSV.Shp_Qdmt as 'QTY-DMT', TSSV.Moist as '% Moisture' ,
max(case when SHPV.Shp_Parameter_Id= '39' then Value else 0 END) as [+18mm],
max(case when SHPV.Shp_Parameter_Id= '40' then Value else 0 END) as [-18mm+16mm],
max(case when SHPV.Shp_Parameter_Id= '41' then Value else 0 END) as [-16mm +12.5mm] -----multiple---
from dbo. TRAN_SHIPMENT_SUMMARY TSSV
inner join TRAN_SHIPMENT_VALUES SHPV on TSSV.Shp_summary_Id=SHPV.Shp_summary_Id group by TSSV.Shp_summary_Id,TSSV.SailDate,TSSV.Vessel_Id,TSSV.Shp_Qdmt, TSSV.Moist order by convert(datetime,TSSV.SailDate)
--END WHERE SHPV.SailDate=@rdate
END
END
Could some one help to achive the output