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:
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.
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.
Ok, thanks
Quick question about the first two snaps in my pipeline (Oracle Execution and the mapper snap), if there is more than one row of results in the ‘Select’ query, how does snapLogic decide which value to assign to ‘AUDIT_COUNT’ in the mapper snap? Because my comparison in the ‘Is there data’ router looks to see what the value is… if it’s not zero, it executes the upper pipeline; if it’s zero, it executes the lower pipeline. So far it’s working, but I’m not sure if it’s because it just takes ‘AUDIT_COUNT’ from the first row of results, which will either be zero if the dummy record is the only row, or something other than zero if there are any other rows (ex: AUDIT_COUNT is 2 or 5 etc).
But will the $ = {} work if my router snap has a mapper snap and pipeline execution snap in between it and the Oracle Execute?
Ok, so I changed the pipeline to compare $ = {} but it’s not working for me.
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.
@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.
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.
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?
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?
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.
Here is an example pipeline that performs the step that I had described, but I hadn’t tested it until now to see that there are two Mappers that need to be introduced to get the behavior I described.
Basically, the “Map dummy value” snap just creates an input document for the Oracle - Execute snap to enable the Passthrough so it will generate something on the output even if no records are present from the query. The “Remove $original” strips that dummy element off the output records - finally, the “Has data?” Router snap can check to see if the input document is empty using the syntax I had provided earlier.
Pipeline attached: Community 13057 - Oracle Execute with no data_2022_08_04.slp (6.8 KB)
I am revisiting this issue because I still can’t get your $={} comparison to work when there is data vs. when there isn’t? Can you assist with this?
After your Oracle snap, put an Exit snap with threshold set to zero.
And run the above pipline as a child via PipeExec with error view. If pipeexec’s error view gets populated then there is output of query. If only output view of the pipeexec gets populated then there is no output from the query. HTH
