I would like a function which returns 0 if the result of the calculation in the function is 0 (or NULL). If the result is larger than 0 and below 0.1 then function should return 0.1 - otherwise the result should just be the result of the calculation. Help is very much appreciated. I have this function:
DECLARE output REAL;
BEGIN
select
case
when (select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'TRUE') > 0 and p.peer_review = 'TRUE' then /* Samarbejde */
case
when p.type_classification_id in (57360320, 57360322, 57360324, 57360326) /* Tidsskriftsartikel, Konferenceartikel i tidsskrift, Letter, Review */ then 1*1.25*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
when p.type_classification_id in (57360348, 57360352, 57360350) /* Bidrag til bog, Bidrag til rapport, Konferenceartikel i proceeding */
then
case
when bs.id is null then 0.5*1.25*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
when bs.id is not null then 1*1.25*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
end
when p.type_classification_id in (57360336, 57360340) /* Bog, Rapport */ then 5*1.25*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
when p.type_classification_id in (57360338, 57360330) /*Antologi, Leder*/ then 0.5*1.25*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
end
when (select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'TRUE') > 0 and p.peer_review is null then /* Samarbejde */
case
when p.type_classification_id in (57360342) /* Doktorafhandling */ then 5*1.25*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
when p.type_classification_id in (57360390) /* Patent */ then 2*1.25*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
when p.type_classification_id in (415542856) /* Preprint */ then 0.5*1.25*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
end
when (select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'TRUE') = 0 and p.peer_review = 'TRUE' then /*Ikke Samarbejde */
case
when p.type_classification_id in (57360320, 57360322, 57360324, 57360326) /* Tidsskriftsartikel, Konferenceartikel i tidsskrift, Letter, Review */ then 1*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
when p.type_classification_id in (57360348, 57360352, 57360350) /* Bidrag til bog, Bidrag til rapport, Konferenceartikel i proceeding */
then
case
when bs.id is null then 0.5*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
when bs.id is not null then 1*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
end
when p.type_classification_id in (57360336, 57360340) /* Bog, Rapport */ then 5*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
when p.type_classification_id in (57360338, 57360330) /*Antologi, Leder*/ then 0.5*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
end
when (select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'TRUE') = 0 and p.peer_review is null then /*Ikke Samarbejde */
case
when p.type_classification_id in (57360342) /* Doktorafhandling */ then 5*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
when p.type_classification_id in (57360390) /* Patent */ then 2*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
when p.type_classification_id in (415542856) /* Preprint */ then 0.5*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
end
else
0
end
from
publication p join publication_status ps on (p.id = ps.publication_id) left join book_series bs on (bs.publication_id = p.id)
where p.id = $1
INTO output;
RETURN output;
END;
Unless I misunderstand you, you just need to change your final line that says RETURN output; to this:
return case
when output is null or output = 0 then 0
when output > 0 and output < 0.1 then 0.1
else output end;
I did, however notice you did not account for negative values. In the statement above, it will return any negative as-is.