From c52768fdc9e74a7533a00dad99891a515c859a7f Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Mon, 10 Feb 2014 15:10:38 -0500 Subject: [PATCH] Ensure we get a name and are duplicate free Signed-off-by: Mike Rylander --- .../sql/Pg/upgrade/XXXX.function.located_uris_act_as_copies.sql | 8 +++++--- 1 file changed, 5 insertions(+), 3 deletions(-) diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.located_uris_act_as_copies.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.located_uris_act_as_copies.sql index 6c1a968a49..ceb2c26c34 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.located_uris_act_as_copies.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.located_uris_act_as_copies.sql @@ -35,7 +35,8 @@ CREATE OR REPLACE FUNCTION evergreen.located_uris ( pref_lib INT DEFAULT NULL ) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank INT) AS $$ WITH all_orgs AS (SELECT COALESCE( enabled, FALSE ) AS flag FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy') - SELECT acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou + SELECT DISTINCT ON (id) * FROM ( + SELECT acn.id, COALESCE(aou.name,aoud.name), acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou FROM asset.call_number acn INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number INNER JOIN asset.uri auri ON auri.id = auricnm.uri @@ -47,7 +48,7 @@ CREATE OR REPLACE FUNCTION evergreen.located_uris ( AND auri.active IS TRUE AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR COALESCE(aou.id,aoud.id) IS NOT NULL) UNION - SELECT acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou + SELECT acn.id, COALESCE(aou.name,aoud.name) AS name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou FROM asset.call_number acn INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number INNER JOIN asset.uri auri ON auri.id = auricnm.uri @@ -57,7 +58,8 @@ CREATE OR REPLACE FUNCTION evergreen.located_uris ( WHERE acn.record = $1 AND acn.deleted IS FALSE AND auri.active IS TRUE - AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR COALESCE(aou.id,aoud.id) IS NOT NULL) + AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR COALESCE(aou.id,aoud.id) IS NOT NULL))x + ORDER BY id, pref_ou DESC; $$ LANGUAGE SQL STABLE; -- 2.11.0