gistfile1.sql

SET CONCAT_NULL_YIELDS_NULL OFF;

SELECT DISTINCT d.DocId,
       gi8.GenIdxData + '->' + gi7.GenIdxData + '->' + gi6.GenIdxData + '->' + gi5.GenIdxData + '->' + gi4.GenIdxData + '->' + gi3.GenIdxData + '->' + gi2.GenIdxData + '->' + gi.GenIdxData AS folder_structure,
       ot.OwnerTypeName AS doc_folder, 
       o.OwnerFolderDate AS folder_date, 
       o.OwnerCreateDTime AS folder_create_time,
       gid.GenIdxDefDsplName AS label_name,
       gi.GenIdxData AS label_value,
       ot2.OwnerTypeName AS parent_folder2,
       gid2.GenIdxDefDsplName AS label_name2,
       gi2.GenIdxData AS label_value2,
       ot3.OwnerTypeName AS parent_folder3,
       gid3.GenIdxDefDsplName AS label_name3,
       gi3.GenIdxData AS label_value3,
       ot4.OwnerTypeName AS parent_folder4,
       gid4.GenIdxDefDsplName AS label_name4,
       gi4.GenIdxData AS label_value4,
       ot5.OwnerTypeName AS parent_folder5,
       gid5.GenIdxDefDsplName AS label_name5,
       gi5.GenIdxData AS label_value5,
       ot6.OwnerTypeName AS parent_folder6,
       gid6.GenIdxDefDsplName AS label_name6,
       gi6.GenIdxData AS label_value6,
       ot7.OwnerTypeName AS parent_folder7,
       gid7.GenIdxDefDsplName AS label_name7,
       gi7.GenIdxData AS label_value7,
       ot8.OwnerTypeName AS parent_folder8,
       gid8.GenIdxDefDsplName AS label_name8,
       gi8.GenIdxData AS label_value8
FROM Documents AS d
  INNER JOIN DocTypes AS dt ON d.DocType = dt.DocType
  INNER JOIN DocsOwners AS do ON d.DocId = do.DocId

  INNER JOIN Owners AS o ON do.OwnerId = o.OwnerId
  INNER JOIN OwnerTypes AS ot ON o.OwnerType = ot.OwnerType
  LEFT OUTER JOIN GenericIndices AS gi ON o.OwnerId = gi.GenIdxOwnerId 
  LEFT OUTER JOIN GenericIndexDefs AS gid ON gi.GenIdxDefId = gid.GenIdxDefId

  LEFT OUTER JOIN Owners AS o2 ON o.ParentOwnerId = o2.OwnerId 
  LEFT OUTER JOIN OwnerTypes AS ot2 ON o2.OwnerType = ot2.OwnerType
  LEFT OUTER JOIN GenericIndices AS gi2 ON o2.OwnerId = gi2.GenIdxOwnerId 
  LEFT OUTER JOIN GenericIndexDefs AS gid2 ON gi2.GenIdxDefId = gid2.GenIdxDefId

  LEFT OUTER JOIN Owners AS o3 ON o2.ParentOwnerId = o3.OwnerId 
  LEFT OUTER JOIN OwnerTypes AS ot3 ON o3.OwnerType = ot3.OwnerType
  LEFT OUTER JOIN GenericIndices AS gi3 ON o3.OwnerId = gi3.GenIdxOwnerId 
  LEFT OUTER JOIN GenericIndexDefs AS gid3 ON gi3.GenIdxDefId = gid3.GenIdxDefId

  LEFT OUTER JOIN Owners AS o4 ON o3.ParentOwnerId = o4.OwnerId 
  LEFT OUTER JOIN OwnerTypes AS ot4 ON o4.OwnerType = ot4.OwnerType
  LEFT OUTER JOIN GenericIndices AS gi4 ON o4.OwnerId = gi4.GenIdxOwnerId 
  LEFT OUTER JOIN GenericIndexDefs AS gid4 ON gi4.GenIdxDefId = gid4.GenIdxDefId

  LEFT OUTER JOIN Owners AS o5 ON o4.ParentOwnerId = o5.OwnerId 
  LEFT OUTER JOIN OwnerTypes AS ot5 ON o5.OwnerType = ot5.OwnerType
  LEFT OUTER JOIN GenericIndices AS gi5 ON o5.OwnerId = gi5.GenIdxOwnerId 
  LEFT OUTER JOIN GenericIndexDefs AS gid5 ON gi5.GenIdxDefId = gid5.GenIdxDefId

  LEFT OUTER JOIN Owners AS o6 ON o5.ParentOwnerId = o6.OwnerId 
  LEFT OUTER JOIN OwnerTypes AS ot6 ON o6.OwnerType = ot6.OwnerType
  LEFT OUTER JOIN GenericIndices AS gi6 ON o6.OwnerId = gi6.GenIdxOwnerId 
  LEFT OUTER JOIN GenericIndexDefs AS gid6 ON gi6.GenIdxDefId = gid6.GenIdxDefId

  LEFT OUTER JOIN Owners AS o7 ON o6.ParentOwnerId = o7.OwnerId 
  LEFT OUTER JOIN OwnerTypes AS ot7 ON o7.OwnerType = ot7.OwnerType
  LEFT OUTER JOIN GenericIndices AS gi7 ON o7.OwnerId = gi7.GenIdxOwnerId 
  LEFT OUTER JOIN GenericIndexDefs AS gid7 ON gi7.GenIdxDefId = gid7.GenIdxDefId

  LEFT OUTER JOIN Owners AS o8 ON o7.ParentOwnerId = o8.OwnerId 
  LEFT OUTER JOIN OwnerTypes AS ot8 ON o8.OwnerType = ot8.OwnerType
  LEFT OUTER JOIN GenericIndices AS gi8 ON o8.OwnerId = gi8.GenIdxOwnerId 
  LEFT OUTER JOIN GenericIndexDefs AS gid8 ON gi8.GenIdxDefId = gid8.GenIdxDefId
WHERE dt.DocTypeName IN ('CFO SBU') 
  AND d.ModifyDateTime >= '2011-05-20 12:00:00 AM' AND d.ModifyDateTime < '2011-05-21 12:00:00 AM'