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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy