search

apsmir / custom_field_sql

redmine sql custom field
14 stars 9 forks source link

Exception during filtering #3

Open KrzysztofI opened 3 years ago

KrzysztofI commented 3 years ago

Query: select subject ||' ('||coalesce(c1.value,'')||')' from issues LEFT JOIN custom_values c1 ON c1.customized_id=issues.id AND c1.custom_field_id=21 where tracker_id = 9 and status_id = 2 select '---- User beafore ----' union all union all select distinct c2.value from custom_values c2 where c2.custom_field_id=31 and c2.value not in ( select subject ||' ('||coalesce(c1.value,'')||')' from issues LEFT JOIN custom_values c1 ON c1.customized_id=issues.id AND c1.custom_field_id=21 where tracker_id = 9 and status_id = 2) ;

Give exception:

cannot be blank. No results are displayed.
apsmir commented 3 years ago

This is not a valid sql query

KrzysztofI commented 3 years ago

yes, please find correct below: select subject ||' ('||coalesce(c1.value,'')||')' from issues LEFT JOIN custom_values c1 ON c1.customized_id=issues.id AND c1.custom_field_id=21 where tracker_id = 9 and status_id = 2 union all select '---- Used before----' union all select distinct c2.value from custom_values c2 where c2.custom_field_id=31 and c2.value not in ( select subject ||' ('||coalesce(c1.value,'')||')' from issues LEFT JOIN custom_values c1 ON c1.customized_id=issues.id AND c1.custom_field_id=21 where tracker_id = 9 and status_id = 2) ;