sql statement for a DataView

From: Pax (?-a-n-d-r-e-i-###?###-p-o-p-o-v-i-c-i-?_at_yahoo.com)
Date: 08/20/04 

Date: Fri, 20 Aug 2004 13:31:19 +0300

Hi, John!
Sorry for this delay.
I'm not sure I understand the problem. Is it the logic of the statement that
you are concerned with (that you can't read)? Is there any problem with the
text of the post? I'll paste the statement again to make it clear. If the
logic is the problem (I'm almost sure it isn't :-) then it's about selecting
documents that contain a certain string, in this case "ss". The statement
has minor modification from that in proc_FetchDocSearchResults in the
content database.

Thanks.

Pax

***************************************
SELECT
 NULL as c1,
 NULL as c2,
 NULL as c3,
 Docs.DirName,
 Docs.LeafName,
 Docs.DocLibRowId,
 Docs.ListId,
 Lists.tp_Title as Lists_tp_Title,
 UserData.tp_Author,
 UserData.tp_Editor,
 UserInfo.tp_Title as UserInfo_tp_Title,
 Docs.TimeLastModified AS LastModified,
 Docs.[Size],
 Docs.Id AS ItemId,
 Docs#CT.Rank AS CT#Rank,
 CASE WHEN (Docs.Type = 1)
      THEN 3
      ELSE 2
 END as c4
FROM
 STS_MyServer_1605625003..Docs
INNER JOIN
 CONTAINSTABLE (STS_MyServer_1605625003..Docs, *, '"ss*"') AS Docs#CT
ON
Docs#CT.[Key] = Docs.ID
LEFT OUTER JOIN
 STS_MyServer_1605625003..Lists
ON
Docs.ListId = Lists.tp_ID
LEFT OUTER JOIN
 STS_MyServer_1605625003..UserData
ON
(Docs.ListId = UserData.tp_ListId) AND
(Docs.DocLibRowId = UserData.tp_ID)
LEFT OUTER JOIN
 STS_MyServer_1605625003..UserInfo
ON
(UserData.tp_Editor = UserInfo.tp_ID) AND
(UserData.tp_SiteId = UserInfo.tp_SiteID)
WHERE
((Lists.tp_Flags IS NULL) OR
 ((Lists.tp_Flags & 0x400) = 0) OR
 (UserData.tp_ModerationStatus IS NULL) OR
 (UserData.tp_ModerationStatus = 0)) AND
((Docs.Type = 0) OR
 ((Docs.Type = 1) AND
  (Docs.DoclibRowId IS NOT NULL)))
ORDER BY
CT#Rank DESC,
LastModified DESC,
ItemId ASC
***************************************