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:

[smudassir]

@vincenr , there are multiple solutions to this problem.
- solution 1: You can have a stored procedure created. The procedure returns the output of the select query if exists. Otherwise, the procedure can just return a custom message for no output. The downstream snap will assume there is no output if the custom message is found.
- solution 2: Connect JSON Formatter + Binary to Document (encode/decode set to "none’) to the output of the execute snap. In the output of the Binary to Document snap, content will be just [ ] if select query has no output.

[vincenr]

But that doesn’t answer my question about how my scenario would work with the two snaps. Please see my last question that I asked.

[vincenr]

Again, if my Oracle execute says for example, select audit account from tableA order by audit count descending, and let’s say that there’s one row with audit count of five and dummy record with audit count zero. In the mapper snap immediately following the Oracle execute snap, when I map the audit account from that select query to a new parameter, what value will be assigned to that parameter? Will it be the audit count from the first row in the select results or what?

[smudassir]

How will the snap know if a record is dummy or real? It just feels are the incoming rows from table are real. If a column’s zero value is a dummy record then you have to customize your pipeline to handle this.
Secondly, the mapper snap will be invoked for every output from the Execute snap. So it executes for audit count five, and audit count zero.

Does this help you?

[vincenr]

So, what I ended up doing and which I humbly feel is the best solution, is to get the max count from the join between the dummy record with zero count and the other rows (if they exist). Then I check to see if maxcount > 0; if it is, I do a second select from Oracle to get the actual data without the dummy row of course; otherwise, I simply end the pipeline without doing anything other than writing to our batch history table that the pipeline was run etc…

I appreciate your feedback and other possible solutions. The format to json and binary to document would probably work, but that’s two snaps in addition to the oracle snap that would always need to happen before my ‘Is there data’ comparison, regardless of whether there are exceptions or not. With the design I went with, although there is one additional Oracle execute snap, that upper pipeline path will only get run maybe 2-3% of the time max being that exceptions happen very rarely.

For anyone interested, the ‘Filter’ snap is so I can toggle on/off whether to send an email when there are no exceptions, mainly for testing purposes, and of course, it’s controlled by a pipeline parameter. Also, the ‘Pipeline Execute’ snaps are simply to record when the pipeline starts and stops in our batch history table.