I am trying to add a calculated field in a BAQ that is showing who posted the Group. SO my question is what is the formula to do this. I think a conditional statement will work, something like:
Case when ChgLog.LogText like ‘%Posted: False -> True%’
then substring(ChgLog.LogText, 2 ,8 ) else ‘Unknown’ end
The last part is where I’m stumped.
Some times the log text will display:
Unfortunately it shows the last userid that made the change. This particular Chglog is for Cash Receipts. So I need to see who actually posted the transaction.
It basically two replaces. The first (inner) replaces double LF’s with a single pipe. The second (outer) replaces any remaining singl LF’s with a pipe.
Now to find the position of… QtyBearing: False -> True
I had to put this together for an Audit, just throwing it in for reference =) Not the prettiest all I could come up with in 15 minutes
SELECT *, WhoChangedItQuery.LogSplit AS WhoChangedIt, WhatWasChangedQuery.LogSplit AS WhatWasChanged FROM (
SELECT * FROM
(
SELECT
Company, Identifier, TableName, DateStampedOn,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS LogSplit
FROM
(
SELECT Ice.ChgLog.Company, Ice.ChgLog.Identifier, Ice.ChgLog.TableName, Ice.ChgLog.DateStampedOn, CAST('<XMLRoot><RowData>' + REPLACE(Ice.ChgLog.LogText,' ','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM Ice.ChgLog
WHERE Ice.ChgLog.Identifier IN ('Company', 'Plant', 'PlantConfCtrl') AND DateStampedOn >= '1/1/2018'
) AS t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
) mark
WHERE LEN(mark.LogSplit) <= 6 AND mark.LogSplit NOT LIKE '%smSupplierID%' AND mark.LogSplit NOT LIKE '%NextCustID%'
) WhoChangedItQuery
JOIN
(
SELECT * FROM
(
SELECT
Company, Identifier, TableName, DateStampedOn,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS LogSplit
FROM
(
SELECT Ice.ChgLog.Company, Ice.ChgLog.Identifier, Ice.ChgLog.TableName, Ice.ChgLog.DateStampedOn, CAST('<XMLRoot><RowData>' + REPLACE(Ice.ChgLog.LogText,' ','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM Ice.ChgLog
WHERE Ice.ChgLog.Identifier IN ('Company', 'Plant', 'PlantConfCtrl') AND DateStampedOn >= '1/1/2018'
) AS t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
) mark
WHERE LEN(mark.LogSplit) >= 6 AND mark.LogSplit NOT LIKE '%smSupplierID%' AND mark.LogSplit NOT LIKE '%NextCustID%'
) WhatWasChangedQuery ON WhatWasChangedQuery.Company = WhoChangedItQuery.Company AND WhatWasChangedQuery.Identifier = WhoChangedItQuery.Identifier AND WhatWasChangedQuery.TableName = WhoChangedItQuery.TableName AND WhatWasChangedQuery.DateStampedOn = WhoChangedItQuery.DateStampedOn
WHERE WhatWasChangedQuery.LogSplit NOT LIKE '%New Record%'
@ckrusen, your worked for me but if it is the second line. It has all this space before it. I tried to do a left trim but I am not getting it to remove that space. I’ve tired a few things…
@hkeric.wci, I forwarded yours to my partner. I thinks yours will work better for what he is doing.