Flow JDBC step Complex Object Output

Victor Sirianni · ‎10-06-2020

Hello,

We have a requirement to set the priority of a request equal to the highest priority open order in our Oracle database. Using Flow Designer we've created a JDBC step and successfully tested a connection and query. The query returns an integer between 1 - 5.

Example Query

SELECT
  
  NVL(
      MIN(CASE freight_carrier_code
          WHEN 'PICKUP'  THEN 2
          WHEN 'COURIER' THEN 2
                         ELSE 3
          END)
      , 3) AS priority

FROM
  ont.oe_order_headers_all

WHERE
  open_flag = 'Y'
  AND sold_to_org_id = /*cust_account_id input variable*/

We are struggling, however, to use the value to set the priority of the requested item. When we create Action Output variables we are not able to set it equal to the result set value and use later in flow.

Any suggestions would be greatly appreciated!

Additional Screenshots

Victor Sirianni · ‎10-08-2020

Not sure it's the best solution, but we were able to script and Action Output and get the needed value:

var obj = fd_data._1__jdbc_step.resultset;
var str = JSON.stringify(obj);
var parser = new JSONParser();
var parsed = parser.parse(str);
var result = parsed.Records[0].priority;

return result;

View solution in original post

Osterber · ‎05-24-2023

This is how i did it

in the Outputs

And then in the actual flow i did it like this

Just output to the log

and in the message is one of the two records