Forum Discussion

Max's avatar
Max
New Contributor II
2 years ago

Move control to appropriate router after SQL statement returns no results

In this pipeline im taking 2 parameters and using them in a SQL select statement in the where clause. . I can have 1 of 3 out comes. I will return 1 result, 2 results or no results.  Im using the aggregate snap to evaluate how many results are returned from the SQL select, then based on the result passing to the appropriate router. This works great for the first  2 scenarios but i get errors when it tries to evaluate "no result" from the SQL select. Any advice as to how i can accomplish this?

5 Replies

  • SpiroTaleski's avatar
    SpiroTaleski
    Valued Contributor
    2 years ago

    @Max 

    If you want to stop the flow(in case SQL Select does not return anything) then you can do that by using Filter Snap(ex: with condition if that CustomerNbr field exist. ex: $.hasPath("CustomerNbr")). 

    • Max's avatar
      Max
      New Contributor II
      2 years ago

      Hello, thank you for your response. Im trying to continue on with the flow if the SQL select snap does not return a result. The flow stopping if there is no result, is the problem. The goal here is, if there is one result go the the first router output, if there are 2 results move to the second router output, if there are no results returned from the SQL select snap then move to the third router output. Given what im trying to do, is the aggregate snap the correct way of doing this or is there a different/better way to get where im going?

  • SpiroTaleski's avatar
    SpiroTaleski
    Valued Contributor
    2 years ago

    Max 

    If there are no results from the Select, than I don't see any point of counting the records(when there are no records returned). So after Select snap, you can add Router snap and check if there are records returned or not.

     - If there are records, then go to the Aggregate Snap, and next router (after Aggregate snap, which now will have only 2 branches - when there are 1 or 2 results).

     - If there are no records returned from Select Snap, not route to the Aggregate Snap, and procced on the branch that supposed to be used in case of no records.

    • Max's avatar
      Max
      New Contributor II
      2 years ago

      I have removed the aggregate snap and connected the router snap to the SQL select snap, Now from the router snap, how to you check to see if there are results returned from SQL select snap?

  • SpiroTaleski's avatar
    SpiroTaleski
    Valued Contributor
    2 years ago

    Max 

    As I mentioned above, you can do a check by some field that always be returned from the SQL Select. Ex: $.hasPath("CustomerNbr"))