FWD » Core Development » Database

define temp-table ttEmployee

    field cEmployee as character

    field cManager  as character

    field cManager1  as character initial 'm'

    field cAddr     as character initial 'a'

    index idxEmployee as unique cEmployee.

define temp-table ttBenefits

    field cEmployee as character

    field cBenefits as character

    index idxEmp cEmployee.

define temp-table ttAddr

    field iAddr as int

    field cAddr as character

    field lHide  as logical

    index idxAddr as unique iAddr.    

define temp-table srcEmployee 

    field cEmployee as character

    field cManager  as character

    field iManager  as int

    field iAddr     as integer

    field lHide     as logical.

define temp-table srcManager 

    field iManager  as int

    field cManager1  as character

    field lHide     as logical.

define temp-table srcBenefits like ttBenefits.

create ttAddr.

assign 

    ttAddr.iAddr = 1

    ttAddr.cAddr = 'Home'

    ttAddr.lHide = false.

create ttAddr.

assign 

    ttAddr.iAddr = 2

    ttAddr.cAddr = 'Outerspace'

    ttAddr.lHide = false.

create srcManager.

assign 

    srcManager.cManager  = 'Mustaine2'

    srcManager.iManager     = 1

    srcManager.lHide = false.

create srcManager.

assign 

    srcManager.cManager  = 'Mustaine3'

    srcManager.iManager     = 2

    srcManager.lHide = false.

create srcEmployee.

assign 

    srcEmployee.cEmployee = 'Dave'

    srcEmployee.cManager  = 'Mustaine1'

    srcEmployee.iManager  = 2

    srcEmployee.iAddr     = 1.

create srcEmployee.

assign 

    srcEmployee.cEmployee = 'Mustaine1'

    srcEmployee.iManager  = 1

    srcEmployee.iAddr     = 2

    srcEmployee.lHide     = false.

create srcEmployee.

assign 

    srcEmployee.cEmployee = 'Toto'

    srcEmployee.cManager  = 'Mustaine'

    srcEmployee.iManager  = 1

    srcEmployee.iAddr     = 1

    srcEmployee.lHide     = false.

create srcBenefits.

assign 

    srcBenefits.cEmployee = 'Dave'

    srcBenefits.cBenefits = 'Voice training'.

create srcBenefits.

assign 

    srcBenefits.cEmployee = 'Dave'

    srcBenefits.cBenefits = 'Groupies'.

create srcBenefits.

assign 

    srcBenefits.cEmployee = 'Mustain'

    srcBenefits.cBenefits = 'Free beer'.

create srcBenefits.

assign 

    srcBenefits.cEmployee = 'Mustain1'

    srcBenefits.cBenefits = 'Free beer1'.

create srcBenefits.

assign 

    srcBenefits.cEmployee = 'Mustain2'

    srcBenefits.cBenefits = 'Free beer2'.

define dataset myRecurs for ttEmployee, ttBenefits

    data-relation r1 for ttEmployee, ttEmployee

    relation-fields (cManager, cEmployee) recursive

    data-relation r2 for ttEmployee, ttBenefits

    relation-fields(cEmployee, cEmployee).

define query qEmp for srcEmployee, srcManager, ttAddr.

define query qBen for srcBenefits.

define data-source dsEmployee for query qEmp srcEmployee, srcManager, ttAddr.

define data-source dsBen for query qBen srcBenefits.

buffer ttEmployee:attach-data-source(data-source dsEmployee:HANDLE).

buffer ttBenefits:attach-data-source(data-source dsBen:HANDLE).

query qEmp:query-prepare("for each srcEmployee where cEmployee = 'Dave' and srcEmployee.lHide = false, " + 

                         "each srcManager where srcManager.lHide = false and srcManager.iManager = srcEmployee.iManager, " +

                         "each ttAddr where ttAddr.lHide = false and ttAddr.iAddr = srcEmployee.iAddr").

//query qEmp:query-prepare("for each srcEmployee where cManager = 'Mustain'").

query qBen:query-prepare("for each srcBenefits").

OUTPUT TO recursive_relation_fill_tmp.txt. 

dataset myRecurs:fill().

//dataset myRecurs:fill().

for each  ttEmployee:

    message ttEmployee.cEmployee ttEmployee.cManager ttEmployee.cManager1 ttEmployee.cAddr.

    for each ttBenefits where ttBenefits.cEmployee = ttEmployee.cEmployee:

        message ttEmployee.cEmployee '->' ttBenefits.cBenefits. 

    end.

end.

OUTPUT CLOSE.