AiDOPWarehouse/tools/sql/test_workorder_query.sql

-- 测试修复后的工单查询
USE aidopdev;

SELECT
  wm.WorkOrd,
  wm.Typed,
  defaultLine.Workshop,
  wm.OrdDate,
  wm.DueDate,
  wm.ItemNum,
  im.Descr,
  im.Descr1,
  IFNULL(wm.QtyOrded, 0)   AS QtyOrded,
  IFNULL(wm.QtyCompleted, 0) AS QtyCompleted,
  IFNULL(wm.QtyOrded, 0) - IFNULL(wm.QtyCompleted, 0) AS NoCompleted,
  mo.MaterialSituation,
  wm.Status
FROM WorkOrdMaster wm
LEFT JOIN ItemMaster im
  ON wm.ItemNum = im.ItemNum
  AND wm.`Domain` = im.`Domain`
LEFT JOIN mes_morder mo
  ON wm.WorkOrd = mo.morder_no
  AND wm.`Domain` = mo.factory_id
LEFT JOIN LATERAL (
  SELECT `Line`
  FROM PeriodSequenceDet
  WHERE ItemNum = wm.ItemNum
    AND `Domain` = wm.`Domain`
  LIMIT 1
) AS psd ON TRUE
LEFT JOIN LATERAL (
  SELECT Workshop
  FROM LineMaster
  WHERE `Line` = psd.`Line`
    AND `Domain` = wm.`Domain`
  LIMIT 1
) AS defaultLine ON TRUE
WHERE IFNULL(wm.Status, '') <> ''
LIMIT 5;