I can’t seem to get this to work…error box reports this -
ValueError: SQL Error: Error converting data type varchar to float.
ShopOrderNumber = fpmi.db.runScalarQuery(“SELECT MAX(ShopOrderNumber) AS ShopOrderNumber FROM Batch WHERE LineNumber = ‘{Root Container.LocationID}’ AND (Stop IS NULL)GROUP BY ShopOrderNumber, LineNumber”,“DowntimeSQL”)
It would be helpful to have more fpmi.db examples with parameters.
thanks
travis
Travis,
I think that the main problem is that you’ve embedded {Root Container.LocationID} into a jython string and are expecting it to be replaced by the value a property named LocationId on the Root Container of your window. This technique (curly-brace enclosed property paths) only works in expressions and SQL query bindings, not in action scripts.
That said, the error that you’re getting seems to be coming from the database. My suspicion is that your ShopOrderNumber column isn’t a numeric-typed column, rather, it is a string (aka varchar). The error you’re seeing would be coming from the MAX function, which only knows how to sort numbers, not strings.
In any case, here is how to use the parameter Root Container.LocationID in a query in an action script:
[code]
rootContainer = fpmi.gui.getParentWindow(event).rootContainer
locationID = rootContainer.getPropertyValue(“LocationID”)
query = “SELECT MAX(ShopOrderNumber) AS ShopOrderNumber FROM Batch WHERE LineNumber = ‘%s’ AND (Stop IS NULL) GROUP BY ShopOrderNumber, LineNumber” % locationID
ShopOrderNumber = fpmi.db.runScalarQuery(query,“DowntimeSQL”)[/code]
What this script does is use Python string replacement to replace the %s in the string with the value of a variable (locationID). Note that I have assumed that the LocationID property is a string because of how you used it in the query (with single quotes). If it is an integer, replace the %s with a %d.
Hope this helps,
On second thought, it may be that the SQL MAX() function does handle strings. In this case, I would also look at the datatype of LineNumber. Perhaps the database doesn’t like the fact that you’re comparing it with a string? (in the WHERE clause)
Let me know if this helps - if not, please post back with the database types for the columns in your Batch table.
Thanks,