I'm trying to use the Connect Web Services' QueryCSV operation but keep getting "Unknown table or column '<name>'" errors.
Environment:
Connect Web Services ("SOAP API")
Resolution:
Ensure that your query selects specific fields from the object, or objects, you are attempting to query rather than the entire object itself.
Cause:
The "QueryCSV" operation is designed to return specific fields for an object rather than an entire object. If one uses a query to select one or more objects in their entirety, rather than specific fields that have been defined for that object(s), the web service will return a "RequestErrorFault".
Take the query shown in the following SOAP envelope as an example:
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Header>
<ns7:ClientInfoHeader xmlns:ns7="urn:messages.ws.rightnow.com/v1_2" soapenv:mustUnderstand="0">
<ns7:AppID>My App</ns7:AppID>
</ns7:ClientInfoHeader>
</soapenv:Header>
<soapenv:Body>
<ns7:QueryCSV xmlns:ns7="urn:messages.ws.rightnow.com/v1_2">
<ns7:Query>SELECT Incident From Incident WHERE ID IN (217, 218)</ns7:Query>
<ns7:PageSize>100</ns7:PageSize>
</ns7:QueryCSV>
</soapenv:Body>
</soapenv:Envelope>
Because the query is attempting to retrieve an entire object ("Incident"), the web service will return a response like the following:
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Header/>
<soapenv:Body>
<soapenv:Fault>
<faultcode>soapenv:Sender</faultcode>
<faultstring>Unknown table or column 'Incident'</faultstring>
<detail>
<n0:RequestErrorFault xmlns:n0="urn:faults.ws.rightnow.com/v1_2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<n0:exceptionCode>INVALID_REQUEST</n0:exceptionCode>
<n0:exceptionMessage>Unknown table or column 'Incident'</n0:exceptionMessage>
</n0:RequestErrorFault>
</detail>
</soapenv:Fault>
</soapenv:Body>
</soapenv:Envelope>