Is there a simple way to return a repeating dataset from a Stored Procedure?

Comments

3 comments

  • Avatar
    MikeM

    So table views does work well for handling the join.

    0
    Comment actions Permalink
  • Avatar
    MikeM

    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. 

    1
    Comment actions Permalink
  • Avatar
    Loren Bratzler

    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

    0
    Comment actions Permalink

Please sign in to leave a comment.