Query to fetch data model output in hdl format

Content

We are trying to automate HDL process using BI publisher report and Extract.

Data model query to include output for Worker dat file with two metadata lines (Assignment and Work terms).

We are able to fetch data only for one metadata. Can two metadata lines output be fetched? Along with this we are using Bursting query and using split by key.

 

Sample query we used:

 SELECT 1 key, 
'METADATA|Assignment|AssignmentId|WorkTermsAssignmentId|PersonNumber|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|LocationId|WorkAtHomeFlag|' AS ASG FROM DUAL
UNION ALL
select  1 key, 'MERGE|Assignment|'||A.assignment_id||'|'||XXXXXXX  AS ASG

from (

select --------