I am new to Lawson and have been tasked to write an SQL that lists shipments with other data. Not knowing much about Lawson this is what I've come up with;
SELECT
WHS.DBWHS_SHIPMENT_NBR ,WHS.DBWHS_DOC_NBR ,WHS.DBWHS_VENDOR ,WHS.DBWHS_CARRIER ,COR.DBCOR_ORDER_NBR ,COR.DBCOR_ORDER_TYPE ,WHL.DBWHL_DESCRIPTION ---,' ' AS sggroup ---,' ' AS sold_to-pt ,COR.DBCOR_CUST_PO_NBR ---, ' ' as fol-doc ,WHS.DBWHS_BOL_NBR ,WHS.DBWHS_WEIGHT ,WHS.DBWHS_CARTONS ,WHS.DBWHS_SHIP_DATE ,WHS.DBWHS_TRANS_ID ---,' ' as proc-id ,WHS.DBWHS_METHOD ,SHP.DBSHP_CUSTOMER ,SHP.DBSHP_NAME ,SHP.DBSHP_NAME ,SHP.DBSHP_ADDR1 ,SHP.DBSHP_ADDR2 ,SHP.DBSHP_CITY ,SHP.DBSHP_STATE ,SHP.DBSHP_ZIP ---,DBBLSHP.''COST CENTER ,SPC.DBSPC_COMMENT ---,'' as whn ,WDH.DBWDH_DESTINATION ,WDH.DBWDH_DEST_NAME ,SHP.DBSHP_TAX_CODE ---,' ' as tax_juris ,WDH.DBWDH_DEST_STATE ,WDH.DBWDH_DEST_CNTRY ,WDH.DBWDH_DEST_ZIP ,WHS.DBWHS_CARRIER ,VEN.DBVEN_VENDOR_VNAME---,'v' as carrier name ,WHS.DBWHS_FREIGHT_CODE---', as incoterm code ---,' ' as incoterm description ,WHS.DBWHS_FRT_CHG_CURR ,ARH.DBARH_TRAN_AMT ---,' ' as acct ,ACM.DBACM_CREDIT_LIM ---,' ' as pint ,WHS.DBWHS_COMPANY ,COR.DBCOR_ORDER_DATE ---,WDM.DBWDM_UPDATE_DATE ,WHS.DBWHS_STATUS ,WHS.DBWHS_SHIP_DATE ---,'LAW' as ERP-IDENTIFIER
FROM LAWDBF7B.DBWHWHS WHS
LEFT JOIN LAWDBF7B.DBWHWHL WHL ON WHS.DBWHS_COMPANY=WHL.DBWHL_COMPANY AND WHS.DBWHS_DOC_NBR=WHL.DBWHL_DOC_NBR LEFT JOIN LAWDBF7B.DBWHWDH WDH ON WHS.DBWHS_COMPANY=WDH.DBWDH_COMPANY AND WHS.DBWHS_DOC_NBR=WDH.DBWDH_DOC_NBR LEFT JOIN LAWDBF7B.DBBLSHP SHP ON WHS.DBWHS_COMPANY=SHP.DBSHP_COMPANY AND WHS.DBWHS_CUST_SHIP_TO=SHP.DBSHP_SHIP_TO
LEFT JOIN LAWDBF7B.DBBLSPC SPC ON SHP.DBSHP_CUSTOMER=SPC.DBSPC_CUSTOMER AND SHP.DBSHP_SHIP_TO=SPC.DBSPC_SHIP_TO LEFT JOIN LAWDBF7B.DBARARH ARH ON ARH.DBARH_COMPANY = SHP.DBSHP_COMPANY AND ARH.DBARH_CUSTOMER=SHP.DBSHP_CUSTOMER LEFT JOIN LAWDBF7B.DBARACM ACM ON ACM.DBACM_COMPANY=SHP.DBSHP_COMPANY AND ACM.DBACM_CUSTOMER=SHP.DBSHP_CUSTOMER LEFT JOIN LAWDBF7B.DBBLCOR COR ON COR.DBCOR_COMPANY=SHP.DBSHP_COMPANY AND COR.DBCOR_CUSTOMER=SHP.DBSHP_CUSTOMER LEFT JOIN LAWDBF7B.DBBLCOL COL ON COL.DBCOL_COMPANY = COR.DBCOR_COMPANY AND COL.DBCOL_ORDER_NBR = COR.DBCOR_ORDER_NBR AND COL.DBCOL_LINE_NBR = WHL.DBWHL_LINE_NBR
LEFT JOIN LAWDBF7B.DBAPVEN VEN ON VEN.DBVEN_VENDOR = COL.DBCOL_VENDOR
WHERE WHS.DBWHS_SHIP_DATE >='20210901' AND WHS.DBWHS_STATUS = '3' AND VEN.DBVEN_VEN_CLASS = 'FRT' This runs for a long time and does not produce the desired results. Can anyone guide me as to what I am doing wrong?
Thanks much!
Rich