Hi there,
try this here using the map command should do it:
| dbxquery query="select m.SORFID, m.requested_by, w.USER_NAME AS \"Request_by_Username\",m.approved_by,w2.USER_NAME AS \"Approved_by_Username\", m.completed_by,
p.po_number, d.product_id, d.sorf_cases, d.custid,m.requested_date , h.house_desc,m.notes
from sorfmain m
INNER JOIN sorfpurch p ON m.sorfid=p.sorfid
INNER JOIN sorfdetail d ON p.sorfdetailid=d.sorfdetailid AND m.sorfid=d.sorfid
INNER JOIN webapps_owner.user_d w ON m.requested_by=w.user_id
INNER JOIN webapps_owner.user_d w2 ON m.approved_by = w2.user_id
INNER JOIN DW_MAIN.house h ON h.house_id=d.house_id and m.requested_date > sysdate - 5 and m.sorfid='132373' " connection=DW_PROD shortnames=true | fields Request_by_Username PO_NUMBER PRODUCT_ID HOUSE_DESC SORFID Approved_by_Username REQUESTED_DATE NOTES SORF_CASES
| map search="| dbxquery query=\"select * from purchase_orders_line_item
where purchase_orders_id = (select purchase_orders_Id from purchase_orders where tc_purchase_orders_id = $PO_NUMBER$) and sku = $PRODUCT_ID$\" connection=$HOUSE_DESC$_WM13 shortnames=true | eval Request_by_Username=\"$Request_by_Username$\" | eval Sorf=\"$SORFID$\" | eval House=\"$HOUSE_DESC$\" | eval Approved_by_Username=\"$Approved_by_Username$\" | eval PO_NUMBER = \"$PO_NUMBER$\" | eval PRODUCT_ID=\"$PRODUCT_ID$\" | eval RequestDate=\"$REQUESTED_DATE$\" | eval Cases=\"$SORF_CASES$\" | eval Notes=\"$NOTES$\" | table SKU Request_by_Username Sorf PO_NUMBER PRODUCT_ID House Approved_by_Username RequestDate Notes Cases"
| join type=left Request_by_Username [ | inputlookup users.csv | eval sAMAccountName=lower(sAMAccountName) | rename sAMAccountName AS Request_by_Username, mail as R.Mail, sn AS R.LName, givenName AS R.FName | table R.Mail, Request_by_Username ]
| join type=left Approved_by_Username [ | inputlookup users.csv | eval sAMAccountName=lower(sAMAccountName) | rename sAMAccountName AS Approved_by_Username, mail as A.Mail, sn AS A.LName, givenName AS A.FName | table A.Mail, Approved_by_Username ]
... View more