1234567890 ab
我试图从发货通知中提取信息,这些通知在订单发货时自动加载到我们的系统中。我希望能拿到BILL_OF_LADING
(BOL)和出货数量(SHQ &AUDIT_TRAIL_VERBAGE
中的数字)。当一行中有BOL时,系统自动将其填充到BOL列中。
既然您标记了post terradata,我假设这是您正在使用的sql风格。子字符串函数是特定于风格的。你的WHERE子句几乎是正确的,你所需要的只是一个括号。
SELECT PO_NUMBER,
BILL_OF_LADING,
AUDIT_TRAIL_VERBAGE,
CASE WHEN AUDIT_TRAIL_VERBAGE LIKE 'SHQ%'
THEN SUBSTRING(AUDIT_TRAIL_VERBAGE 5 FOR 5)
ELSE NULL
END AS SHIPPED_QTY
FROM MY.TABLE
WHERE
PO_NUMBER IN '1234567'
AND (BILL_OF_LADING IS NOT NULL
OR AUDIT_TRAIL_VERBAGE LIKE 'SHQ%')
现在,如果您想在一行中获取一个PO的所有信息,您可以这样做:
SELECT SHQ.PO_NUMBER,
BOL.BILL_OF_LADING
SUBSTRING(SHQ.AUDIT_TRAIL_VERBAGE 5 FOR 5) AS SHIPPED_QTY
FROM MY.TABLE AS SHQ
LEFT JOIN MY.TABLE AS BOL ON SHQ.PO_NUMBER=BOL.PO_NUMBER
AND BOL.BILL_OF_LADING IS NOT NULL
AND SHQ.AUDIT_TRAIL_VERBAGE LIKE 'SHQ%'
WHERE
PO_NUMBER IN '1234567'