Gravatar for shirazi@rdacorp.com

Question by aga, Nov 20, 2015 10:50 AM

Creating AccessorSubQueries

Hi

Im trying to use AccessorSubQueries to index an Oracle database. If I do not use the AccessorSubQueries then all my documents get indexed. But when I use the AccessorSubQueries none of the documents get indexed and the log is filled with this warning:

Unable to index document : There is a formatting error in a sub query. Cannot find master key %[item_id].

<?xml version="1.0" encoding="utf-8" ?> 
<ODBC>
    <CommonMapping>
        <AllowedUsers>
            <AllowedUser type="Windows" allowed="true">
                <Name>everyone</Name>
                <Server/>
            </AllowedUser>
        </AllowedUsers>
    </CommonMapping>
    <Mapping type="Products">
        <Accessor type="query">
            select im.item_id  from t im  where im.item_id = 10000 order by im.item_id
        </Accessor>
        <AccessorSubQueries>
            <AccessorSubQuery name="productPros" separator=";" behaviorOnMultiRows="join" allowDuplicates="false"> 
                select p.item_Id from t p where p.item_Id= %[item_id]                   
            </AccessorSubQuery>
        </AccessorSubQueries>
        <Fields>
            <Uri>https://www.x.com/?sku=%[legacy_sku]</Uri>
            <ClickableUri>https://www.x.com</ClickableUri>
            <FileName>%[alt_description]</FileName>
            <Title>%[alt_description]</Title>
            <Body>
            </Body>
            <CustomFields>

            </CustomFields>
        </Fields>
    </Mapping>   
</ODBC>
1 Reply
Gravatar for mgrondines@coveo.com

Answer by mgrondines, Dec 4, 2015 10:38 AM

The key in the Sub Query must match exactly what is returned by the server. More importantly, it is case-sensitive. For instance, you ask for im.item_id, but the server might return something like Item_ID. Ensure that the key returned matches the returned key.

Ask a question