SQL statement to query on custom property

Question

I'm having issues getting the results that I want from the indexer.  I have added a new property that stores a list of relative paths String|Vector.  I am trying to issue a query to find files that contain certain relative paths.

propdesc:
<?xml  version="1.0" encoding="utf-8"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/windows/2006/propertydescription" schemaVersion="1.0">
    <propertyDescriptionList publisher="Apollo" product="Apollo">
        <propertyDescription name="Apollo.Dependencies.FilePath" formatID="{8B0740F1-708B-4e23-A8C1-DF31C1449E99}" propID="100">
            <labelInfo label="Dependency Path" hideLabel="false"/>
            <searchInfo inInvertedIndex="false" isColumn="true" maxSize="512" mnemonics="dependencypath|dependency|dependencies|depends|dep"/>
            <typeInfo type="String" multipleValues="true" isViewable="true" isQueryable="true"/>
            <displayInfo displayType="String">
                <stringFormat formatAs="FilePath"/>
            </displayInfo>  
        </propertyDescription>
    </propertyDescriptionList>
</schema>


The first entry in the [Result] list is the path of the file (System.ItemUrl) and all following results are the Apollo.Dependencies.FilePath entries for that file.


SELECT "Apollo.Dependencies.FilePath", "System.ItemUrl" FROM "SystemIndex" WHERE Scope = 'file:D:/environments' AND CONTAINS("System.FileExtension",'"prefab04c*"',1033)

-----------------
[Result]: d:\environments\specular_bloom.prefab04c
-----------------
[Result]: d:\environments\railrust06.prefab04c
[Result]: prefabs\prototype\1navigation\rails\railrust\railrust.mat04c
[Result]: materials\_global\nomark\nomark_metal.mat04c
-----------------
[Result]: d:\environments\railrust07.prefab04c
[Result]: materials\_global\nomark\nomark_metal.mat04c
[Result]: prefabs\prototype\1navigation\rails\railrust\railrust.mat04c
-----------------
[Result]: d:\environments\railrust09.prefab04c
[Result]: materials\_global\nomark\nomark_metal.mat04c
[Result]: prefabs\prototype\1navigation\rails\railrust\railrust.mat04c
-----------------
[Result]: d:\environments\railrust11.prefab04c
[Result]: materials\_global\nomark\nomark_metal.mat04c
[Result]: prefabs\prototype\1navigation\rails\railrust\railrust.mat04c
-----------------
[Result]: d:\environments\railrust12.prefab04c
[Result]: materials\_global\nomark\nomark_metal.mat04c
[Result]: prefabs\prototype\1navigation\rails\railrust\railrust.mat04c
-----------------
[Result]: d:\environments\railrust16.prefab04c
[Result]: materials\_global\nomark\nomark_metal.mat04c
[Result]: prefabs\prototype\1navigation\rails\railrust\railrust.mat04c



SELECT "Apollo.Dependencies.FilePath", "System.ItemUrl" FROM "SystemIndex" WHERE Scope = 'file:D:/environments' AND CONTAINS("System.FileExtension",'"prefab04c*"',1033) AND CONTAINS("Apollo.Dependencies.FilePath",'"prefab*"',1033)

This query results in 0 hits.

What I'm looking for is to get a list of files that depend on a file that contains the word "prefab".  I'm expecting the list below.

-----------------
[Result]: d:\environments\railrust06.prefab04c
[Result]: prefabs\prototype\1navigation\rails\railrust\railrust.mat04c
[Result]: materials\_global\nomark\nomark_metal.mat04c
-----------------
[Result]: d:\environments\railrust07.prefab04c
[Result]: materials\_global\nomark\nomark_metal.mat04c
[Result]: prefabs\prototype\1navigation\rails\railrust\railrust.mat04c
-----------------
[Result]: d:\environments\railrust09.prefab04c
[Result]: materials\_global\nomark\nomark_metal.mat04c
[Result]: prefabs\prototype\1navigation\rails\railrust\railrust.mat04c
-----------------
[Result]: d:\environments\railrust11.prefab04c
[Result]: materials\_global\nomark\nomark_metal.mat04c
[Result]: prefabs\prototype\1navigation\rails\railrust\railrust.mat04c
-----------------
[Result]: d:\environments\railrust12.prefab04c
[Result]: materials\_global\nomark\nomark_metal.mat04c
[Result]: prefabs\prototype\1navigation\rails\railrust\railrust.mat04c
-----------------
[Result]: d:\environments\railrust16.prefab04c
[Result]: materials\_global\nomark\nomark_metal.mat04c
[Result]: prefabs\prototype\1navigation\rails\railrust\railrust.mat04c

What am I doing wrong?

Thanks!

Answer 1

searchInfo inInvertedIndex="false"

You can't do a contains on a property that is not in the full text index. Set this value to TRUE. 

---

Microsoft

Answer 2

Thanks!  That did the trick.