Hi,
Any help would be really appreciated on this one.
I am trying to join 2 data tables together. Table 2 has more rows than Table 1 but I only want to bring through those rows in Table 2 that match to those in Table 1 (For example the load would only bring through invoice details for those invoices numbers contained in Table 1).
Currently my script retrieves all invoice numbers from the table "F_INV_NUM" and then brings through all invoice details for all invoices from table "T_AC_INVOICE_ITEMS".
My current code is below;
LOAD
"F_INV_COMPANY_ID"&'|'&"F_INV_NUM"&'|'&"F_INV_TRANSTYPE"&'|'&"F_INV_SUBNO" as %KEY_INVOICE_ITEM,
"F_INV_NUM" AS [Invoice Number];
SQL SELECT * FROM
FLEETWARE."T_AC_INVOICES"
LEFT JOIN
"F_ITM_COMPANY_ID"&'|'&"F_ITM_NUM"&'|'&"F_ITM_TRANSTYPE"&'|'&"F_ITM_SUBNO" as %KEY_INVOICE_ITEM,
"F_ITM_NUM" as [Inv Item Number],
"F_ITM_ACTION" as [Inv Item Detail];
SQL SELECT *FROM
FLEETWARE."T_AC_INVOICE_ITEMS"
I think some variation of LEFT OUTER JOIN is the way forward which specifies the condition that "F_INV_NUM" = "F_ITM_NUM".
As I understand it LEFT OUTER JOIN would only bring through those entries in table 2 which can be matched to those entries in table 1. I'm just not sure of how to execute it.
Thanks in advance
