Trying to figure out why "milestonecode" is not showing up in the final table results
| makeresults
| addinfo
| map search="dbxquery query=\"SELECT Incident_Number, Detailed_Decription FROM HPD_Help_Desk WHERE Assigned_Group = 'Order Processing' AND Reported_Date between $info_min_time$ AND $info_max_time$\" connection=\"ITSM\"| rex field=Detailed_Decription \"ORDERID-->(?\S+).*ORDERTYPE--->(?\S+)\" | join order_id type=left [| dbxquery shortnames=true connection=\"AUXDB\" query=\"SELECT oh.lastupdateddate AS lastupdateddate, NVL(om.rowcount, 0) as milestonecount FROM PLPRODSER1.ORDER_HEADER oh LEFT OUTER JOIN (SELECT orderid, COUNT(orderid) AS rowcount FROM PLPRODSER1.ORDERMILESTONE WHERE UPPER(MILESTONECODE) IN ('PAYMENTPOSTED','PAYMENTXFERRED','REFUNDPOSTED','BACKOUTAPPLIED','PSTVOIDCOVADJ','EXCHANGECHGADJ','ACCOUNTREFILLED','BACKOUTCHGADJ','BALANCEDXFERRED','CHARGEADJCARE','BACKOUTREVERSAL','BACKOUTNONCOVADJ','D2AEPINCOVADJ','PAYMENTERROR') AND STATUS = 'SUCCESS' GROUP BY orderid) om ON oh.orderid = om.orderid\" | fields milestonecount, lastupdateddate]"
| table Incident_Number, order_id, order_type, milestonecount
The second query has been verified using dbconnect and its returning back the proper fields with counts
SELECT oh.lastupdateddate AS lastupdateddate, NVL(om.rowcount, 0) as milestonecount FROM PLPRODSER1.ORDER_HEADER oh LEFT OUTER JOIN (SELECT orderid, COUNT(orderid) AS rowcount FROM PLPRODSER1.ORDERMILESTONE WHERE UPPER(MILESTONECODE) IN ('PAYMENTPOSTED','PAYMENTXFERRED','REFUNDPOSTED','BACKOUTAPPLIED','PSTVOIDCOVADJ','EXCHANGECHGADJ','ACCOUNTREFILLED','BACKOUTCHGADJ','BALANCEDXFERRED','CHARGEADJCARE','BACKOUTREVERSAL','BACKOUTNONCOVADJ','D2AEPINCOVADJ','PAYMENTERROR') AND STATUS = 'SUCCESS' GROUP BY orderid) om ON oh.orderid = om.orderid
and the first query is also working fine, whenI remove the join logic
the final result is showing the first 3 columns, but milestonecount is blank
