Flow JDBC step Complex Object Output

[Victor Sirianni]

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

[Rohit Duseja2]

Hi 

1st image seems to be your script in action, right?

Step1: In that case create and output variable in script section like this

Step 2: 

Create an output variable type reference and respective table.

Once you have done both publish it and try to add this is flow and you will get whole record and you can use it as per requirement.

If you want more help maybe we can connect.

Please mark this as helpful and correct if this helps you.

Regards,

Rohit Duseja.

[Victor Sirianni]

Thanks, Rohit,

We're actually using a JDBC step to get a value from an Oracle database, which doesn't have the Output Variables section you highlighted. It creates a complex object and which we are trying to parse into an Action Output variable.

I've added additional screenshots to the question to clarify; thanks!

[Victor Sirianni · Accepted solution]

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;

[P-Rudenko-SN]

Hi @Victor Sirianni I have the same requirement, but in the output I still have the empty value. Did you do the script in your output variable?