I'm trying to use a CTE at the top of my query to be referenced in WHERE clause in a separate CTE further down. For Example:

with ref as (
    null email,
    '("720884","70540")'  cus_id,
     null booking_ref)

This would be used in a different CTE as part of the WHERE filter like:

    where  a.email_address in (coalesce((select email from ref), a.email_address))
    and  a.customer_id in (coalesce((select cus_id from ref), a.customer_id))
    and  c.b_ref in (coalesce((select booking_ref from ref), c.b_ref))

The reason I'm trying to use coalesce is so the user can just search by either email/cus_id/booking_ref and not all three have to be populated, when I run it I get this error

All COALESCE operands must be the same type or coercible to a common type. Cannot find common type between row(varchar(8), varchar(8)) and varchar, all types (without duplicates): [row(varchar(8), varchar(8)), varchar]

This is being ran in Galaxy Starburst Anyone with a solution to this?

I'm trying to use a CTE at the top of my query to be referenced in WHERE clause in a separate CTE further down. For Example:

with ref as (
    null email,
    '("720884","70540")'  cus_id,
     null booking_ref)

This would be used in a different CTE as part of the WHERE filter like:

    where  a.email_address in (coalesce((select email from ref), a.email_address))
    and  a.customer_id in (coalesce((select cus_id from ref), a.customer_id))
    and  c.b_ref in (coalesce((select booking_ref from ref), c.b_ref))

The reason I'm trying to use coalesce is so the user can just search by either email/cus_id/booking_ref and not all three have to be populated, when I run it I get this error

All COALESCE operands must be the same type or coercible to a common type. Cannot find common type between row(varchar(8), varchar(8)) and varchar, all types (without duplicates): [row(varchar(8), varchar(8)), varchar]

This is being ran in Galaxy Starburst Anyone with a solution to this?

• sql
• presto
• trino

• Try explicitly cast to a particular type. – ValNik Commented Mar 7 at 8:47
• I've tried that but still doesn't work – tl1310 Commented Mar 7 at 9:13

1 Answer 1

Assuming that ref is defined something like:

with ref as (select null email, '("720884","70540")' cus_id, null booking_ref)

then you can perform join here:

select d.*
from dataset d
join ref r on
    (r.email is null or r.email = d.email)
    and (r.cus_id is null or r.cus_id = d.cus_id)
    and (r.booking_ref is null or r.booking_ref = d.booking_ref);