How do I join PurchaseOrderLine and PurchaseOrderLineDistribution in the Data Lake?

jhedahl

Hello, I'm hoping someone can help me with a Compass query to join PurchaseOrderLine and PurchaseOrderLineDistribution. I'm using the delivered replication sets, and I'm struggling on the indexing.

This is the S3 Lawson query I'm trying to replicate.

SELECT
    MMDIST.DIST_COMPANY,
    MMDIST.ACCT_UNIT,
    POLINE.ITEM
FROM
    LSLMDB.ls_apps.MMDIST MMDIST
INNER JOIN
    LSLMDB.ls_apps.POLINE POLINE
    ON
        MMDIST.COMPANY = POLINE.COMPANY
        AND MMDIST.DOC_NUMBER = POLINE.PO_NUMBER
        AND MMDIST.DOC_NBR_NUM = POLINE.PO_RELEASE
        AND MMDIST.PO_CODE = POLINE.PO_CODE
        AND MMDIST.LINE_NBR = POLINE.LINE_NBR
WHERE
    MMDIST.SYSTEM_CD = 'PO'
    AND MMDIST.DOC_TYPE = 'PT'
    AND MMDIST.LINE_SEQ = 1
    AND POLINE.ITEM_TYPE = 'N'
    AND POLINE.REC_QTY > 0
GROUP BY
    MMDIST.DIST_COMPANY,
    MMDIST.ACCT_UNIT,
    POLINE.ITEM

This is the closest I've gotten, but the delivered tables are missing a ton of indexing fields like System Code and Document Type.

SELECT

  "FSM_PurchaseOrderLineDistribution"."FinanceDimension1",

  "FSM_PurchaseOrderLineDistribution"."FinanceDimension3"

  "FSM_PurchaseOrderLine"."Item"

FROM

  "FSM_PurchaseOrderLine"

INNER JOIN "FSM_PurchaseOrderLineDistribution"

  ON

    "FSM_PurchaseOrderLine"."Company" = "FSM_PurchaseOrderLineDistribution"."Company"

    AND "FSM_PurchaseOrderLine"."PurchaseOrder" = "FSM_PurchaseOrderLineDistribution"."PurchaseOrder"

    AND "FSM_PurchaseOrderLine"."LineNumber" = "FSM_PurchaseOrderLineDistribution"."LineNumber"

Stace Webley

What you have seems like it would work to me.  I join on company, purchase order, and line number as well.