SQL Query result as number

[ptc-6292144]

Hello,

I'm trying to cross reference ID across several different database tables. I start out by having an input number for the initial SQL query which I enter and then I have created a difference service that will use the result from the query to perform a query from another database table and will return a selected result, however I am getting the error that it cannot convert an infotable to a number and I can't switch the result from an infotable to a number inside the initial service SQL query.

Any help is appreciated.

[paic]

Hi Michael,

you will have to accept the result from the SQL Query whatever way it is given to you, so you must match up your datashape with the returned result.

So either your query has to extract the information properly or you use another service that calls the SQL Query, accepts the result and then grab the Number you need and send that on to the next SQL Query call.

I hope that made sense.

[ptc-6292144]

Pai,

Appreciate the response. Still a little confused. The Local JavaScript I have for the final operation is below.

var params = {

PU_Id: '304' /* NUMBER /</div><div>};</div><div>var paramss = {</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>PP_Id: me.ppidCopy(params) / NUMBER /</div><div>};</div><div>var paramsss = {</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>Prod_Id: me.BeloitEPRMprodidCopy(paramss) / NUMBER */

};

// result: INFOTABLE dataShape: "BeloitEPRMprodcodeproddesc"

var result = me.BeloitEPRMprodcodeCopy(paramsss);

The first parameter I set manually within the code (PU_Id) which when run in the me.ppidCopy service returns a single number (the PP_Id used for the next service, me.BeloitEPRMprodidCopy) The next service also returns a single value which is used in the final service (me.BeloitEPRMprodcodeCopy) which returns two values based on the result from the previous service. When trying to test this I get the following error.

Error executing service

Wrapped java.lang.Exception: Unable To Convert From com.thingworx.types.InfoTable to NUMBER Cause: Unable To Convert From com.thingworx.types.InfoTable to NUMBER

all of my services contain the same Data Shape which has all the fields within the services defined as numbers as well.

Thank you again for your help

[paic]

Hi Michael, not sure what your availability will be next week, but I'd like to set up a call with you to take a look at this.

Thanks.

[abalousek]

Without seeing the other services (me.ppidCopy and me.BeloitEPRMprodidCopy) to be sure, what I suspect is happening is that these services are SQL (Query) type services (with inputs PP_Id and Prod_Id respectively that are base type of NUMBER), and this kind of service (SQL (Query)) will only ever output the result as an INFOTABLE type. Therefore when you chain the output of one as the input of the next you're getting a conversion error trying to force an INFOTABLE in as a NUMBER. You'll need to wrap your queries in another Local (Javascript) service and handle the INFOTABLE to number parsing/conversion in order for it to have the proper type, or modify your script above to handle it:

var params = {

PU_Id: '304' /* NUMBER /</div><div>};</div><div><br></div><div>var paramss = {</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>PP_Id: me.ppidCopy(params).rows[0].fieldName / NUMBER /</div><div>};</div><div>var paramsss = {</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>Prod_Id: me.BeloitEPRMprodidCopy(paramss).rows[0].fieldName / NUMBER /</div><div>};</div><div>// result: INFOTABLE dataShape: "BeloitEPRMprodcodeproddesc"</div><div>var result = me.BeloitEPRMprodcodeCopy(paramsss);</div></div><div><br></div><div>***Note that the original SQL Query services must be changed to output an INFOTABLE and given a DataShape with a "fieldName"</div></p>

[ptc-6292144]

That did it! Thank you so much Andy! Still getting used to JavaScript. I've made a couple programs using VBA and haven't ever used JavaScript before.