February 28, 2023 at 10:51 am
I have removed the trigger and indexed views also, but it comes after sometimes.
I have taken permission , i will soon send the code
February 28, 2023 at 11:38 am
this this the orignal query
INSERT INTO [ReportSyncQueue]
([ProjectId]
,[LineloopId]
,[SubSystemId]
,[ActivityHeaderId]
,[ActivityId]
,[TableName]
,[ClassAuditId]
,[Remarks]
,[Status]
,[CreatedDt]
,[UpdatedDt]
,[CDataSource]
,[UDataSource]
,IsSyncMtr
,IsSyncDR
,IsSyncPB
,ReportDt
)
SELECT l.ProjectId, l.LineloopId,AH.SubSystemId,ah.ActivityHeaderId,a.ActivityId,'PipeJoinMethod' AS TabelName
,PJLCA.PipeJoinMethodId1 AS ClassauditId
,null,1,getdate(),getdate(),'HOSO','HOSO'
,0 AS IsSyncMtr
,0 AS IsSyncDr
,1 AS ISSyncPB
,NULL AS ReportDt
FROM
ActivityHeader ah
JOIN WeldingAudit sa ON ah.ActivityHeaderId=sa.ActivityHeaderId AND sa.Status=1
join Activity a on a.ActivityId=ah.ActivityId and a.Status=1
join LineLoop l on ah.LineLoopId=l.LineLoopId and l.Status=1
join PipeJointLegacyClassAudit PJLCA on PJLCA.ClassAuditId = sa.WeldingAuditId and pjlca.Status =1
join Pipejoinmethod pjm ON pjlca.PipeJoinMethodId1 = pjm.Pipejoinmethodid and pjm.status=1
WHERE ah.Status=1 and a.ActivityId in (5,23)
AND PJM.PipeJoinMethodId in (select value from dbo.fnSeprator(@strPipeJointMethodIds,','))
--all ID's like ProjectId lineloopid is bigint,Status is smallint,
-- Column_name Type
--ReportSyncQueueId bigint
--ProjectId bigint
--LineloopId bigint
--SubSystemId bigint
--ActivityHeaderId bigint
--ActivityId bigint
--TableName varchar
--ClassAuditId bigint
--Remarks varchar
--Status smallint
--CreatedDt datetime
--UpdatedDt datetime
--CDataSource varchar
--UDataSource varchar
--IsSyncMtr bit
--IsSyncDR bit
--IsSyncPB bit
--ReportDt datetime
** Object: UserDefinedFunction [dbo].[fnSeprator] Script Date: 28-02-2023 16:43:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-----*******************************
--Hemant Kumar
--purpose to seprate the string with given seprator
--it will return you table
------------------------------------
ALTER function [dbo].[fnSeprator]
(
@strInp varchar(max),
@strSeprator varchar(20)
)returns @retTable table
(
indx smallint primary key,
value varchar(max)
)
as
begin
declare @intTotalLen smallint
declare @intTmpLen smallint
declare @intSepLen smallint
declare @flag bit
declare @strTmp varchar(max)
declare @strRemainingPart varchar(max)
declare @tmpIndex smallint
declare @intStartPos smallint
declare @intEndPos smallint
set @intSepLen=datalength(@strSeprator)
set @strRemainingPart=@strInp
set @intStartPos=1
set @tmpIndex=1
set @flag=1
set @intTotalLen=datalength(@strInp)
if(@intTotalLen>0)
begin
while(@flag=1)
begin
set @intEndPos=charindex(@strSeprator,@strRemainingPart)
if(@intEndPos>0)
begin
set @intTmpLen=@intEndPos-@intStartPos
set @strTmp=subString(@strRemainingPart,@intStartPos,@intTmpLen)
begin
insert into @retTable
(
indx,
value
)
values
(
@tmpIndex,
@strTmp
)
end
set @tmpIndex=@tmpIndex+1
set @intTmpLen=@intTotalLen-@intEndPos
set @intStartPos=@intEndPos+@intSepLen--@intEndPos+1
set @strRemainingPart=substring(@strRemainingPart,@intStartPos,@intTmpLen)
set @intStartPos=1
end
else
begin
set @flag=0
begin
insert into @retTable
(
indx,
value
)
values
(
@tmpIndex,
@strRemainingPart
)
end
end
end
end
return
end
February 28, 2023 at 5:18 pm
What is the data type of PJM.PipeJoinMethodId ?
If it is numeric, the value column from the table variable will be implicitly converted to be compared with it.
If the value column contains numbers, could you try explicitly converting it? I assume it's a generic separator function which is why it returns varchar(max).
AND PJM.PipeJoinMethodId in (select value from dbo.fnSeprator(@strPipeJointMethodIds,','))
AND PJM.PipeJoinMethodId in (select CAST(value AS INT) from dbo.fnSeprator(@strPipeJointMethodIds,','))
If PJM.PipeJoinMethodId is non-numeric, what is the value of the @strPipeJointMethodIds variable that you have seen the conversion problem with?
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply