Home > Software engineering >  Append a string value to where in (select distinct field from table)
Append a string value to where in (select distinct field from table)

Time:11-17

I would like to append a string value onto an in clause. E.g. this query retruns a field of string values:

with

lut as (
select *
from mytable
)

select distinct "primary goal" from lut

I can also do this:

select distinct "primary goal" from lut union all (select 'trial')

This returns the original data from the first select plus an additional row with value 'trial'.

I want to use this within a where in clause

with

lut as (
select *
from mytable
)

select *
from anothertable
where event_name in (select distinct "primary goal" from lut union all (select 'trial'))

Returns error:

ERROR: Query unsupported due to an internal error. Detail: Unsupported query. Where: Oid: 1101.

CodePudding user response:

Adding the constant trail directly to the column, wound change nothing for the distinct, so ou would get the wanted result

with

lut as (
select *
from mytable
)

select distinct CONCAT('trial',"primary goal") from lut

IN Clause seems not fully be implemented

use JOIN

with

lut as (
select *
from mytable
)

select *
from anothertable a JOIN (select distinct "primary goal" as lut_name from lut union all (select 'trial')) t1
ON a.event_name = t1.lut_name
  • Related