Integrations and APIs for Service

Get Involved. Join the Conversation.

Topic

    Leonardo Barbosa
    XML error Unknown table or column SOAP UIAnswered
    Topic posted February 23, 2015 by Leonardo BarbosaBronze Medal: 1,250+ Points 
    183 Views, 3 Comments
    Title:
    XML error Unknown table or column SOAP UI
    Content:

    Hello for all,

    I am facing some problems with Get request on SOAP UI. See the "Query" request code:

    </soapenv:Header>
       <soapenv:Body>
          <v1:QueryCSV>
             <v1:Query>SELECT 
            Opportunity.customfields.c.idpedido,
            Opportunity.customfields.c.tipodepedido.name,
            Opportunity.CreatedTime,
            Opportunity.customfields.c.cpf,
             Opportunity.customfields.c.condicaodepagamento,
             Opportunity.customfields.c.mensagemnota,
            Opportunity.customfields.c.idantigopedido,
            Opportunity.customfields.c.numerodopedido,
            itemdopedido,
            CodigodoPedido,
            Quantidade,
            precototal, 
            precounitario
            FROM CO.Detalhedopedido where (Opportunity.customfields.c.flagerp = '1' and Opportunity.customfields.c.statuspedido.name = 'Envio ERP')</v1:Query>
             <v1:Delimiter>|</v1:Delimiter>
          </v1:QueryCSV>
       </soapenv:Body>
    </soapenv:Envelope>

    The expected result are values of Opportunity custom fields and some fields of CO called Detalhedopedido. But the current behavior is:

     

    <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 'Opportunity'</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 'Opportunity'</n0:exceptionMessage>
                </n0:RequestErrorFault>
             </detail>
          </soapenv:Fault>
       </soapenv:Body>
    </soapenv:Envelope>

    I don't have any idea about this because Opportunity is a default table.

    Someone can help me with this? Any idea about issue?

    Sincerely,

    Best Comment

    Bhagwan Singh Mer

    The ROQL looks incorrect. Did you test it using Connect Object Explorer?

    Customer Portal > Customer Portal Administration > Connect Object Explorer

    What is the relationship from CO.Detalhedopedido to Opportunity ? I would suggest that you look at the technical documentation for ROQL tabular query

    Here is an excerpt from the documentation

    For example, if there is a relationship from Incident to ServiceProduct named AssignedProduct, the following query would fetch the reference number of all incidents assigned to the account name Joe Smith, along with the name of each incident, associated product, and the ID and name of the product at the next level in that product's hierarchy.


    SELECT I.ReferenceNumber, I.AssignedProduct.Name, I.AssignedProduct.Parent.ID, I.AssignedProduct.Parent.Name, FROM Incident I WHERE I.AssignedTo.Account.Name='Joe Smith';

    Comment

     

    • Bhagwan Singh Mer

      The ROQL looks incorrect. Did you test it using Connect Object Explorer?

      Customer Portal > Customer Portal Administration > Connect Object Explorer

      What is the relationship from CO.Detalhedopedido to Opportunity ? I would suggest that you look at the technical documentation for ROQL tabular query

      Here is an excerpt from the documentation

      For example, if there is a relationship from Incident to ServiceProduct named AssignedProduct, the following query would fetch the reference number of all incidents assigned to the account name Joe Smith, along with the name of each incident, associated product, and the ID and name of the product at the next level in that product's hierarchy.


      SELECT I.ReferenceNumber, I.AssignedProduct.Name, I.AssignedProduct.Parent.ID, I.AssignedProduct.Parent.Name, FROM Incident I WHERE I.AssignedTo.Account.Name='Joe Smith';

    • Leonardo Barbosa

      Hello,

      Thank's for your time and asking your questions:

      Did you test it using Connect Object Explorer?

      Yes, I did and recive the same error

      What is the relationship from CO.Detalhedopedido to Opportunity ?

      CO.Detalhedopedido is a chield object of Opportunity 

      I'll look the documentation to find the better solution

      Regards,

    • Leonardo Barbosa

      Hello,

      Only FYI see the correct code:

         <soapenv:Body>
            <v1:QueryCSV>
               <v1:Query>SELECT
                                 CO.Detalhedopedido.CodigodoPedido,
                                 CO.Detalhedopedido.Quantidade,
                                 CO.Detalhedopedido.precototal,
                                 CO.Detalhedopedido.precounitario,
                                 CO.DetalhedoPedido.idPedido.customfields.c.idpedido,
                                 CO.DetalhedoPedido.idPedido.customfields.c.tipodepedido.name,
                                 CO.DetalhedoPedido.idPedido.CreatedTime,
                                 CO.DetalhedoPedido.idPedido.customfields.c.cpf,
                                 CO.DetalhedoPedido.idPedido.customfields.c.condicaodepagamento,
                                 CO.DetalhedoPedido.idPedido.customfields.c.mensagemnota,
                                 CO.DetalhedoPedido.idPedido.customfields.c.idantigopedido,
                                 CO.DetalhedoPedido.idPedido.customfields.c.numerodopedido
                                 FROM CO.Detalhedopedido WHERE (CO.DetalhedoPedido.idPedido.customfields.c.flagerp = '1' and  CO.DetalhedoPedido.idPedido.customfields.c.statuspedido.name = 'Envio ERP')</v1:Query>
               <v1:Delimiter>|</v1:Delimiter>
            </v1:QueryCSV>
         </soapenv:Body>
      </soapenv:Envelope>

       

      I really appreciated your help

      Regards,