需要删除包含值“APPOINTMENT_NEEDED”的行

Question

我编写了一个查询来查找某些发货人中的所有工作项，但我只需要在wt.name列中不包含值'ShipmentStop‘的工作项，或者不包含wi.entity_type列中的’ShipmentStop‘值的工作项。

我试过了，而不是IN，你可以从评论中看到，但它根本不起作用。由于某种原因，我实际上不能在任何条件语句中使用这些列。

'''select wi.entity_type, IFF (wi.entity_type = 'EmailThread', concat ('https://ops.convoy.com/email-threads/', wi.entity_id), ' ') as "Email Link", wi.created_at, wi.updated_at, wt.name, j.short_id, s.friendly_prefix, ('https://ops.convoy.com/shipments/') || j.short_id as "Shipment Link"
from core.work_items WI
    left join src.work_types WT on WI.type_ID = WT.id
    left join core.jobs j on WI.job_id = j.id
    left join bi.shippers s on j.shipper_id = s.id
where 1=1
    and wi.completed_at is null
    --and wi.entity_type != 'ShipmentStop'
    --and 'APPOINTMENT_NEEDED' not in (wt.name)
    and contains (WI.evaluation_data, 'ADE')
    or contains (WI.evaluation_data, 'KLG')
    or contains (WI.evaluation_data, 'SBX')
    or contains (WI.evaluation_data, 'NES')
    or contains (WI.evaluation_data, 'KFT')
    or contains (WI.evaluation_data, 'MDZ')
    and date_trunc({{lookback}},CONVERT_TIMEZONE('America/Los_Angeles', wi.created_at)::timestamp)::date = date_trunc({{lookback}},[[ {{date}}) --]] dateadd({{lookback}},-1,CONVERT_TIMEZONE('America/Los_Angeles',current_timestamp())))::date
    order by wi.created_at desc
limit 10
-- lookback:
    -- qtr = quarter
    -- week = week
    -- day = day
    -- month = month
    -- year = year'''

Answer 1

这一节可以重写：

(    contains (WI.evaluation_data, 'ADE')
or contains (WI.evaluation_data, 'KLG')
or contains (WI.evaluation_data, 'SBX')
or contains (WI.evaluation_data, 'NES')
or contains (WI.evaluation_data, 'KFT')
or contains (WI.evaluation_data, 'MDZ')
)

偏执增加以强制执行条件as，并优先于OR。

上述部分只是：

WI.evaluation_data ILIKE ANY ('%ADE%','%KLG%', '%SBX%', '%NES%', '%KFT%', '%MDZ%')

第二，FROM部分使用左联接到wt

from core.work_items WI
left join src.work_types WT on WI.type_ID = WT.id
left join core.jobs j on WI.job_id = j.id
left join bi.shippers s on j.shipper_id = s.id

因此，以下用途：

where ...
'APPOINTMENT_NEEDED' not in (wt.name)

将有效地将查询作为内部连接。

 from core.work_items WI
 INNER join src.work_types WT on WI.type_ID = WT.id

应该将此条件移动到ON

 FROM core.work_items WI
 LEFT join src.work_types WT 
   ON WI.type_ID = WT.id
  AND wt.name  <> 'APPOINTMENT_NEEDED'

或者使用空安全操作符：IS DISTINCT FROM：

where 1=1
and wi.completed_at is null
and wi.evaluation_data ILIKE ANY ('%ADE%','%KLG%', '%SBX%', '%NES%', '%KFT%', '%MDZ%')
and wt.name IS DISTINCT FROM 'APPOINTMENT_NEDDED'

Answer 2

NOT CONTAINS (wt.name, 'APPOINTMENT_NEEDED')

或

wt.name NOT LIKE '%APPOINTMENT_NEEDED%'