Habe eine Abfrage gebastelt, die aber keinen Wert zurückgibt. Es geht um einen Kartendruck, der seit Umstellung auf ein neues Semester nicht mehr funktioniert. Werte sind alle hinterlegt in den abgefragten Feldern...
-- FUNCTION: public.pr_ehf_validierung(character varying)
-- DROP FUNCTION public.pr_ehf_validierung(character varying);
CREATE OR REPLACE FUNCTION public.pr_ehf_validierung(
akartennr character varying)
RETURNS TABLE(kartennr character varying, mtknr character varying, stg character varying, sem character varying, gueltigkeit text, zusatztext text, flag boolean, aufdruck1 text, aufdruck2 character varying, aufdruck text)
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000
AS $BODY$
DECLARE
BEGIN
RETURN query
WITH sm AS (
SELECT pr_getaktsemester() AS aktsm
),
sub AS (
SELECT sg.kartennr_stg AS kartennr,
sg.mtknr,
sg.stg,
CASE
WHEN sg.kzrbe::text <> '3'::text AND sg.biasf::text = stnext.sem::text THEN stnext.sem
WHEN sg.kzrbe::text <> '3'::text AND sg.rue='1' THEN sg.rue_sem
ELSE 'NIR'::character varying
END AS sem,
CASE
WHEN sg.kzrbe::text <> '3'::text AND sg.biasf::text = stnext.sem::text THEN to_char(stnext.ende::timestamp with time zone, 'DD.MM.YYYY'::text)
WHEN sg.kzrbe::text <> '3'::text AND sg.rue='1' AND 'now'::text::date > (stakt.ende) THEN 'gültig bis ' || to_char(stnext.ende::timestamp with time zone, 'DD.MM.YYYY'::text)
WHEN sg.kzrbe::text <> '3'::text AND sg.rue='1' AND 'now'::text::date <= (stakt.ende) THEN 'gültig bis ' || to_char(stnext.ende::timestamp with time zone, 'DD.MM.YYYY'::text) || ' + Vorsemester'::text
WHEN sg.kzrbe::text <> '3'::text THEN 'gültig bis ' || to_char(stakt.ende::timestamp with time zone, 'DD.MM.YYYY'::text)
WHEN sg.kzrbe::text = '3'::text AND sg.dtrbes::text = stakt.sem::text AND 'now'::text::date <= sg.dtexm THEN 'gültig bis ' || to_char(sg.dtexm::timestamp with time zone, 'DD.MM.YYYY'::text)
ELSE ''::text
END AS gueltigkeit,
''::text AS zusatztext,
true AS flag,
CASE
WHEN sg.kzrbe::text <> '3'::text AND sg.biasf::text = stnext.sem::text THEN to_char(stnext.ende::timestamp with time zone, 'DD.MM.YYYY'::text)
WHEN sg.kzrbe::text <> '3'::text AND sg.rue='1' AND 'now'::text::date > (stakt.ende) THEN 'gültig bis ' || to_char(stnext.ende::timestamp with time zone, 'DD.MM.YYYY'::text)
WHEN sg.kzrbe::text <> '3'::text AND sg.rue='1' AND 'now'::text::date <= (stakt.ende) THEN 'gültig bis ' || to_char(stnext.ende::timestamp with time zone, 'DD.MM.YYYY'::text) || ' + Vorsemester'::text
WHEN sg.kzrbe::text <> '3'::text THEN 'gültig bis ' || to_char(stakt.ende::timestamp with time zone, 'DD.MM.YYYY'::text)
WHEN sg.kzrbe::text = '3'::text AND sg.dtrbes::text = stakt.sem::text AND 'now'::text::date <= sg.dtexm THEN 'gültig bis ' || to_char(sg.dtexm::timestamp with time zone, 'DD.MM.YYYY'::text)
ELSE ''::text
END AS aufdruck
FROM sos.student_stg sg
LEFT JOIN sm ON true
LEFT JOIN sos.student_stg sg2 ON sg2.stammnr::text = sg.stammnr::text AND (sg2.stg::text || sg2.aaspf::text) <> (sg.stg::text || sg.aaspf::text) AND sg2.dtrbes::text = pr_addsemester(sg.biasf, '-1'::integer)::text AND sg.biasf::text = sm.aktsm::text
LEFT JOIN sos.student_stg sg3 ON sg3.stammnr::text = sg.stammnr::text AND (sg3.stg::text || sg3.aaspf::text) <> (sg.stg::text || sg.aaspf::text) AND sg3.dtrbes::text = pr_addsemester(sg.biasf, '-1'::integer)::text AND sg.biasf::text = pr_addsemester(sm.aktsm, 1)::text
LEFT JOIN keys.fachr f ON f.id = sg.fachr_id
LEFT JOIN keys.semester_termine stakt ON stakt.sem::text = sm.aktsm::text AND stakt.kurz::text = 'semester'::text
LEFT JOIN keys.semester_termine stnext ON stnext.sem::text = pr_addsemester(sm.aktsm, 1)::text AND stnext.kurz::text = 'semester'::text
LEFT JOIN keys.semester_termine stvor ON stvor.sem::text = pr_addsemester(sm.aktsm, '-1'::integer)::text AND stvor.kurz::text = 'semester'::text
WHERE true AND sg.kartennr_stg IS NOT NULL
and substr(sg.kartennr_stg,1,6)=substr(akartennr,1,6)
and (akartennr<>'' and akartennr is not null)
)
SELECT sub.kartennr,
sub.mtknr,
sub.stg,
sub.sem,
sub.gueltigkeit,
sub.zusatztext,
sub.flag,
CASE
WHEN sub.sem::text = ''::text THEN ''::character varying
WHEN "position"(sub.aufdruck::text, '+'::text) > 0 THEN btrim(substr(sub.aufdruck::text, 1, "position"(sub.aufdruck::text, '+'::text) - 1))::character varying
ELSE sub.aufdruck
END AS aufdruck1,
CASE
WHEN sub.sem::text = ''::text THEN ''::text
WHEN "position"(sub.aufdruck::text, '+'::text) > 0 THEN btrim(substr(sub.aufdruck::text, "position"(sub.aufdruck::text, '+'::text) + 1))
ELSE ''::character varying
END AS aufdruck2,
sub.aufdruck --into recBack
FROM sub;
END
$BODY$;
ALTER FUNCTION public.pr_ehf_validierung(character varying)
OWNER TO efhf;
Hoffe jemand erkennt so da evtl. einen Fehler.
