Is there a simple way to return a repeating dataset from a Stored Procedure?
Query Multiple doesn't work because the repeating set is a join from two tables. I might try looking into Table views to see if that is something that might work as an alternative, but thought I would ask here at the same time :)
Thanks!
-
Yay! Finally found a way to Query multiple result and manipulating returned set based on variables while not on a form.
So Create a Table View of the joined result set you want.
On the Query Mutiple Shape, use a union and negate the first results.
i.e. WHERE 1 = 0 UNION SELECT Column1, case when ..... other complex logic from TableView where regularwhereclause.
you can make your super complex query with all the AP variables you want,and be able to map the results to your repeating nodes.
-
Mike,
It took me a while to digest what you are doing here but now that I understand, I see another way to use your "UNION" approach. I have had instances in the past where I needed to do a query multiple activity against joined tables. Since the Query Multiple activity does not allow you to enter manual queries, I would typically create a view in my database that has the tables joined and then use that view in my query multiple activity.
I have had some cases where I needed to join multiple tables in order to filter results, but I only need to return fields from the primary table. With your "UNION" approach, I can do this now without having to create a new view in the database!
For example: I have two tables, Table_A and Table_B that I can join together on a key field. I need to pull data from Table_A but I only want to pull that data if there is a certain value in the corresponding Table_B record.
With the "UNION" approach, I can configure my Query Multiple activity to use Table_A and then put the following in the WHERE clause:
WHERE 1 = 2
UNION
SELECT a.*
FROM Table_A a
JOIN Table_B b on a.Key = b.Key
WHERE b.ValueField = 'XYZ'The thing to remember is that the SELECT statement after the UNION must select all the fields in the table or the UNION statement will fail. I assume this is because, behind the scenes, AgilePoint is selecting all fields from the table you specify in the configuration. So that is why I select a.* in the union query. Then I use the mapping tool to map only the fields I am interested to my repeating elements the same as you would on any other query with just a single table.
Thank you for this "outside-the-config" thinking! I have already made use of it one application and can see myself using it many others down the road!
Loren
Please sign in to leave a comment.
Comments
3 comments