Skip to main content

Hi  

I am new to building SQL statements using XML to send to FSM. Are there any samples of building a fairly simple query which contains an aggregate function and a group by statement.

I can’t make it work - I keep getting an error

“The argument tableNameIn does not contain a valid value”

The SQL is quite basic

SELECT s.part_id

, s.place_id

, s.>location]

, COUNT(s.part_id) AS stock_qty

FROM dbo.stock s

INNER JOIN dbo.stock_serial_id ss ON (s.part_id = ss.part_id AND s.place_id = ss.place_id AND s.location = ss.location)

WHERE s.part_id = '12345'

AND s.place_id = 'FOOBAR'

AND s.location = 'BARFOO'

AND ss.usable = 'Y'

GROUP BY   s.part_id

, s.place_id

, s.location

, ss.usable

 

Cheers

Ganners

 

I guess it’s harder than I thought!

I managed to work around this problem by creating a database view which conceals the complexity (ha) of the join/agg func/group by. This is exposed to the API by adding metadata.

I’d still like to know how to handle the slightly more complex SQL in the XML if anyone knows.


I assume you were doing a hierarchy select in XML?   Aggregate functions in SQL are not supported by hierarchy selects.  Instead do a tabular select which has some support for aggregates.   However for anything really complex defining a view as you have done is likely the best approach.


Thanks for your response.

I had tried both hierarchy and tabular selects without much joy. For the sake of completeness can you point me at any samples of aggregate functions in tabular select XML? I’m just curious to know how it’s done!


Hi @ganners ,

in this post What is the use of MPM in FSM? | IFS Community, you will find a documentation related to XML messages.

Hope it answers your question regarding the aggregate functions in tabular select.

 

Cheers,

~Juni


Reply