scusate questo è il codice corretto e mi ritorna ....,....---il nulla
CREATE OR REPLACE FUNCTION
pck_gestione_tracce_sistema.p_get_rotta_traccia_sistema6( IN
in_id_traccia_sistema numeric, IN in_id_versione numeric, IN
in_limite_inferiore numeric, IN in_limite_superiore numeric, IN
id_limite_inferiore_data date, IN id_limite_superiore_data date, IN
in_id_viaggio numeric, OUT on_out_total_count numeric, OUT
oc_out_recordset character varying) RETURNS record AS$BODY$DECLARE
LD_LIMITE_INFERIORE_DATA Date ; LD_LIMITE_SUPERIORE_DATA Date ;
LK_PACKAGE_NAME varchar(1000); LK_FUNCTION_NAME varchar(1000);
LN_DEBUG_INFO Integer; --ref refcursor; --ref1 refcursor; ref
refcursor; ref1 refcursor;
Begin
LN_DEBUG_INFO := 1; LK_PACKAGE_NAME := 'PCK_GESTIONE_DATI';
LK_FUNCTION_NAME := 'P_GET_ROTTA_TRACCIA_SISTEMA'; LD_LIMITE_INFERIORE_DATA
:= TO_DATE(TO_CHAR(ID_LIMITE_INFERIORE_DATA,'DD/MM/YYYY
HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS'); LD_LIMITE_SUPERIORE_DATA :=
TO_DATE(TO_CHAR(ID_LIMITE_SUPERIORE_DATA,'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY
HH24:MI:SS');
If (LN_DEBUG_INFO = 1) Then PERFORM nereus.p_logging(IN_TIPO =>
'I'::character varying, IN_CODICE => 'INFO'::character
varying, IN_DESCRIZIONE => 'Input call'::character varying,
IN_MODULO => LK_PACKAGE_NAME::character varying,
--LK_PACKAGE_NAME, IN_FUNZIONE => LK_FUNCTION_NAME::character
varying, --LK_FUNCTION_NAME, IN_NOTE => 'ID_TRACK: ' ||
IN_ID_TRACCIA_SISTEMA || ' VERS: ' || IN_ID_VERSIONE || ' LIM_INF: ' ||
IN_LIMITE_INFERIORE || ' LIM_SUP: ' || IN_LIMITE_SUPERIORE || ' DATA_INF:' ||
TO_CHAR(ID_LIMITE_INFERIORE_DATA,'DD/MM/YYYY HH24:MI:SS') || ' DATA_SUP: ' ||
TO_CHAR(ID_LIMITE_SUPERIORE_DATA, 'DD/MM/YYYY HH24:MI:SS') || ' ID_VIAGGIO: '
|| IN_ID_VIAGGIO, IN_USER_ID => '0'); End If;
IF (IN_ID_VIAGGIO Is Not Null) Then Open ref FOR Select
Count(*) into on_out_total_count
From (Select T.*, LAG(T.LATITUDINE)
OVER(Order By T.VERSIONE Asc) LAT_PREV,
LAG(T.LONGITUDINE) OVER(Order By T.VERSIONE Asc) LON_PREV
From nereus.STORICO_TRACCE_SISTEMA T
Where T.ID_TRACCIA_SISTEMA = IN_ID_TRACCIA_SISTEMA
And T.VERSIONE < IN_ID_VERSIONE
And T.LATITUDINE Is Not Null And
T.LONGITUDINE Is Not Null And
T.ID_VIAGGIO_IN_CORSO = IN_ID_VIAGGIO And
T.DATA_AGGIORNAMENTO Between LD_LIMITE_INFERIORE_DATA And
LD_LIMITE_SUPERIORE_DATA) Z Where ((LAT_PREV Is Not
Null And Z.LATITUDINE <> LAT_PREV) Or (LON_PREV Is Not Null And Z.LONGITUDINE
<> LON_PREV)) Or (LAT_PREV Is Null And LON_PREV Is
Null); on_out_total_count:=ref::numeric;
RETURN;--on_out_total_count;
Open ref1 FOR SELECT * into oc_out_recordset
FROM (Select *
From (Select Q.*, row_number() OVER (ORDER BY Q.VERSIONE) AS RNUM
From (Select Z.* From (
Select T.*,
LAG(T.LATITUDINE) OVER(Order By T.VERSIONE Asc) LAT_PREV,
LAG(T.LONGITUDINE) OVER(Order By T.VERSIONE
Asc) LON_PREV From
nereus.STORICO_TRACCE_SISTEMA T
Where T.ID_TRACCIA_SISTEMA = IN_ID_TRACCIA_SISTEMA --30001--
And T.VERSIONE < IN_ID_VERSIONE --3--3--4--5 --
And T.LATITUDINE Is Not Null
And T.LONGITUDINE Is Not Null
And T.ID_VIAGGIO_IN_CORSO = IN_ID_VIAGGIO --2 --
And T.DATA_AGGIORNAMENTO Between
LD_LIMITE_INFERIORE_DATA And LD_LIMITE_SUPERIORE_DATA
Order By T.VERSIONE Desc) Z
Where ((LAT_PREV Is Not Null And Z.LATITUDINE <> LAT_PREV) Or
(LON_PREV Is Not Null And Z.LONGITUDINE <> LON_PREV))
Or (LAT_PREV Is Null And LON_PREV Is Null)) Q
) X Where RNUM <= IN_LIMITE_SUPERIORE +
ID_LIMITE_SUPERIORE_DATA ) Y Where RNUM >
IN_LIMITE_SUPERIORE; RETURN ;--1; --oc_out_recordset ;
End If; Exception When Others Then PERFORM
nereus.P_LOGGING(IN_TIPO => 'E'::character varying,
IN_CODICE => Sqlstate::character varying, IN_DESCRIZIONE =>
Sqlerrm::character varying, IN_MODULO =>
LK_PACKAGE_NAME::character varying,--LK_PACKAGE_NAME,
IN_FUNZIONE => LK_FUNCTION_NAME::character varying,--LK_FUNCTION_NAME,
IN_NOTE => 'ID_TRACK: ' || IN_ID_TRACCIA_SISTEMA || ' VERS: '
|| IN_ID_VERSIONE || ' LIM_INF: ' || IN_LIMITE_INFERIORE || ' LIM_SUP: ' ||
IN_LIMITE_SUPERIORE || ' DATA_INF:' ||
TO_CHAR(ID_LIMITE_INFERIORE_DATA, 'DD/MM/YYYY HH24:MI:SS') || ' DATA_SUP: ' ||
TO_CHAR(ID_LIMITE_SUPERIORE_DATA,'DD/MM/YYYY HH24:MI:SS') || ' ID_VIAGGIO: ' ||
IN_ID_VIAGGIO::character varying, IN_USER_ID => '0'::character
varying); --ON_OUT_TOTAL_COUNT := -1; End $BODY$ LANGUAGE plpgsql
VOLATILE COST 100;ALTER FUNCTION
pck_gestione_tracce_sistema.p_get_rotta_traccia_sistema6(numeric, numeric,
numeric, numeric, date, date, numeric) OWNER TO postgres;
