How can I test for no rows back from Oracle query

[vincenr]

I am trying to figure out how to route to a ‘no data’ path when nothing is returned from my select query. Can someone help me out?

What I’ve tried is (in ‘Is there data?’ snap): $retval != null (then go to data path); otherwise, go to nodata path. But I’m guessing since the select didn’t return anything, there is no $retval to look at (catch22).

I also tried:

[nsingam]

Hi,

You can try using the gate snap after Oracle execute snap. Gate snap will always provide the output, even if there is no data then an empty array will be there. In the router check $input0.length > 0 for records else true. Add the splitter snap in the output view of where records were found and split data.

[vincenr]

This is what I got to work last night, which to me at least seems a little more straight forward. Basically, Oracle snap does union with a dummy record with the desired value to make it take the ‘no data’ path. When I check for no data, I’m assuming it is using the ‘AUDIT_COUNT’ value (this is the column I’m using for the data/nodata decision snap), of the first record because it’s working correctly every time. Then, if there are records other than the dummy record, I filter out the dummy record so it doesn’t show up in the csv that gets emailed.

[koryknick]

Rather than returning a dummy record, you could also check the returned document equal to an empty object. For example:

$ == {}

This can be your Router check condition if no data was returned by the select since if anything was returned, it would not be an empty object.

[vincenr]

Ok, thanks