In the previous post, I covered how to normalize hard coded expressions inside a query. In this post, there is a specific problem related to PeopleSoft; it’s that often the PROCESS_INSTANCE is hard coded and needs to be normalized.

For example, here’s an INSERT statement from a Profiler trace of a PeopleSoft system:

INSERT INTO PS_REQ_HDR_T4 ( PROCESS_INSTANCE , BUSINESS_UNIT , REQ_ID) SELECT DISTINCT 26337824 , BUSINESS_UNIT , REQ_ID FROM PS_REQ_DST_SUM_T4 WHERE PROCESS_INSTANCE = 26337824 AND (QTY_OPEN_STD > 0 OR AMT_OPEN <> 0)

The problem is, we can’t do any aggregations on this to find the number of unique queries in the file as all of them could be unique simply because the PROCESS_INSTANCE keeps changing… but in reality… it’s all the same query… there really is only ONE unique query.

What we want to do is make a function that strips out the hard coded values and replaces them with “xxx” or whatever you want to put in.

select dbo.norm_psft('INSERT INTO PS_REQ_HDR_T4 ( PROCESS_INSTANCE , BUSINESS_UNIT , REQ_ID) SELECT DISTINCT 26337824 , BUSINESS_UNIT , REQ_ID FROM PS_REQ_DST_SUM_T4 WHERE PROCESS_INSTANCE = 26337824 AND (QTY_OPEN_STD > 0 OR AMT_OPEN <> 0)')

-- the dbo.norm_psft function will convert

INSERT INTO PS_REQ_HDR_T4 ( PROCESS_INSTANCE , BUSINESS_UNIT , REQ_ID) SELECT DISTINCT 26337824 , BUSINESS_UNIT , REQ_ID FROM PS_REQ_DST_SUM_T4 WHERE PROCESS_INSTANCE = 26337824 AND (QTY_OPEN_STD > 0 OR AMT_OPEN <> 0)

-- into:

INSERT INTO PS_REQ_HDR_T4 ( PROCESS_INSTANCE , BUSINESS_UNIT , REQ_ID) SELECT DISTINCT xxx , BUSINESS_UNIT , REQ_ID FROM PS_REQ_DST_SUM_T4 WHERE PROCESS_INSTANCE = xxx AND (QTY_OPEN_STD > 0 OR AMT_OPEN <> 0)

Here’s the code:

create function norm_psft(@str varchar(8000))
returns varchar(8000)
as begin

-- usage:
-- select dbo.norm_psft('INSERT INTO PS_REQ_HDR_T4 ( PROCESS_INSTANCE , BUSINESS_UNIT , REQ_ID) SELECT DISTINCT 26337824 , BUSINESS_UNIT , REQ_ID FROM PS_REQ_DST_SUM_T4 WHERE PROCESS_INSTANCE = 26337824 AND (QTY_OPEN_STD > 0 OR AMT_OPEN <> 0)')
-- select dbo.norm_psft('frank mcbath')


-- testing
-- declare @str varchar(8000) 
-- set @str = 'INSERT INTO PS_REQ_HDR_T4 ( PROCESS_INSTANCE , BUSINESS_UNIT , REQ_ID) SELECT DISTINCT 26337824 , BUSINESS_UNIT , REQ_ID FROM PS_REQ_DST_SUM_T4 WHERE PROCESS_INSTANCE = 26337824 AND (QTY_OPEN_STD > 0 OR AMT_OPEN <> 0)'


declare @i int
declare @c char(1)
declare @pi_offset_len int
set @i = charindex( 'PROCESS_INSTANCE = ', @str)

if (@i > 1)
begin

set @i = charindex( 'PROCESS_INSTANCE = ', @str)
set @i = @i + 20 -- this is the beginning char after the '= '

while (@i < (len(@str)+1))
begin

-- figure out how long the process instance integer is

 set @c = substring(@str,@i,1)

 if substring(@str,@i,1) = ' '
 begin
 set @pi_offset_len = @i
 break
 end

 set @i=@i+1

end

-- dig the PID out of the string... only one occurence is needed
declare @pid nvarchar(20)
set @pid = substring(
@str, 
(charindex( 'PROCESS_INSTANCE = ', @str)+19), 
@i-(charindex( 'PROCESS_INSTANCE = ', @str)+19))

-- now replace ALL occurences of the PID in the string and return the fixed string:

end

if (charindex( 'PROCESS_INSTANCE = ', @str) > 0)
 set @str = replace(@str, @pid, 'xxx')
 
return @str

end