I have written a query to find all work items within certain shippers, but I only need the work items that DON'T contain the value 'APPOINTMENT_NEEDED' in the wt.name column OR that DON'T contain the value 'ShipmentStop' in the wi.entity_type column.

I've tried != and NOT IN, which you can see in the comments, but it's not working at all. I can't actually use the columns in any conditional statements for some reason.

'''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'''

CodePudding user response：

NOT CONTAINS (wt.name, 'APPOINTMENT_NEEDED')

or

wt.name NOT LIKE '%APPOINTMENT_NEEDED%'

CodePudding user response：

The section could be rewritten:

(    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')
)

The paranthesis added to enforce the condition as AND has precedence over OR.

The above section is just:

WI.evaluation_data ILIKE ANY ('