For some weird reason, they’ve got a Revisions table with a field called DocRef here.
The DocRef field contains a ; seperated list of GUIDs. In the new model we’re implementing, we’ve got an own table for files, so I had to figure out something smart to get this ; seperated list to be records.
First I had to fix sfnc_System_SplitGuid. Just remove the PRIMARY KEY setting on the PrimKey field in the return @GuidTable table variable. This is of course fixed on our main database in Ølen now, so you don’t need to fix it after next build. After that I wrote the following query:
SELECT
R.Domain, R.DocDwgID, R.Rev, S.PrimKey AS FileRef
FROM atbl_DocCtrl_Revisions AS R WITH (NOLOCK)
CROSS APPLY sfnc_System_SplitGuid(REPLACE(REPLACE(REPLACE(R.DocRef, ';', ','), '{', ''), '{', '')) AS S
WHERE R.DocRef IS NOT NULL
The nice thing about this is that we’re using CROSS APPLY to “apply” a UDF to a recordset. Sweet thing!
BTW! Today’s song is: Backstreet Boys – I want it that way
Like this:
Like Loading...
Related