Question

How to Iterate thru recordset

  • 8 August 2019
  • 1 reply
  • 1 view
f.kemminje
Badge +3

I have two oracle recordset(output of sql executors).

let me say recordset A has two column segment_id and cable_id

and one more recordset B has segment_id and Previous_segment_id

 

record set A

SEGMENT_ID | CABLE_ID

21 | 0

111 | 0

53 | 0

68 | 1

1 | 0

 

Recors set B

SEGMENT_ID | PREVIOUS_SEGMENT_ID

21 | 111

111 | 53

53 | 68

68 | 1

1 | 9

Function needed here is...

if the set cable_id of segment_id is 0(zero), then

I need to loop thru Second set B by fetching previous Ids.(i pasted image below)

 

in the example for the segment_id 21, cable id is 0, so i have to move forward by taking its previous id(PREVIOUS ID IS IN SET B)

which is 111, but for for 111 again cable id is zero(in the record set A), so kepp on looping

until i get 68( because for 68 cable id is 1). now i need to stop here because i got some positive value(greator than zero)

at the end for segment id 21 should have cable id 1. like wise

segment_id 11 =cable id 1

segment_id 53 = cable id 1

how can i achive this thru fme ?

1 reply

takashi
Rank
Userlevel 2
Badge +17

Hi @fkemminje, some recursive functions are required, and regular transformers don't support that, as far as I know. Probably scripting would be necessary.

Python scripting may be an effective way, but I would provide a more interesting solution with XQuery expressions in XMLTemplater and XMLXQueryExtractor.

0684Q00000ArJsZQAV.png

XMLTemplater Root Template (XQuery) Expression:

<root>{
    fme:process-features("SUB_A", "SEGMENT_ID", fme:get-attribute("SEGMENT_ID"))
}</root>

XMLTemplater SUB_A Template (XQuery) Expression:

let $segment_id := xs:integer(fme:get-attribute("SEGMENT_ID"))
let $cable_id := xs:integer(fme:get-attribute("CABLE_ID"))
return
<segment id="{$segment_id}">{
    if (0 < $cable_id) then $cable_id
    else fme:process-features("SUB_B", "SEGMENT_ID", $segment_id)
}</segment>

XMLTemplater SUB_B Template (XQuery) Expression:

fme:process-features("SUB_A", "SEGMENT_ID", fme:get-attribute("PREVIOUS_SEGMENT_ID"))

XMLXQueryExtractor XQuery Expression:

for $x in //segment
let $id := $x/text()
return if ($id) then (xs:string($x/@id), $id) else xs:string($x/@id)

----------

The XMLTempater creates XML document representing sequence of segments for each segment. e.g. for the segment ID=21:

<?xml version="1.0" encoding="UTF-8"?>
<root>
    <segment id="21">
        <segment id="111">
            <segment id="53">
                <segment id="68">1</segment>
            </segment>
        </segment>
    </segment>
</root>

Then, the XMLXQueryExtractor translates the XML to a sequence of segment IDs separated by hyphen.

21-111-53-68-1

 

Full result:

0684Q00000ArJSrQAN.png

 

Reply


Community Stats

28,688
Posts
109,428
Replies
36,543
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.