I extract the some value by this script...
SELECT r.numero as ID
,sr.id || '_' || rownum IDs
,nvl(EXTRACTVALUE(value(Modulo),'//child::*[@modelCodeScheme="prova" and @modelCodeValue="1"]/text()'),'campo vuoto') PROVA1_DATA
,nvl(EXTRACTVALUE(value(Modulo),'//child::*[@modelCodeScheme="prova" and @modelCodeValue="1.1"]/text()'),'campo vuoto') PROVA1_ORA
,nvl(EXTRACTVALUE(value(Modulo),'//child::*[@modelCodeScheme="prova" and @modelCodeValue="2"]/text()'),'campo vuoto') PROVA2_DATA
,nvl(EXTRACTVALUE(value(Modulo),'//child::*[@modelCodeScheme="prova" and @modelCodeValue="2.1"]/text()'),'campo vuoto') PROVA2_ORA
FROM TBREF c
join REFER r on c.REFER = r.NUMERO
join SR sr on sr.SI_ID = r.SI_ID
, TABLE(XMLSEQUENCE(EXTRACT(XMLType(REPLACE(sr.xmldata, 'xmlns="http://www.PROVA.it/2008/FMSchema"', '')),'//modulo'))) Modulo
WHERE sr.XMLDATA is not null;
And after with the formula
= interval(Timestamp#( PROVA1_DATA &' '&
PROVA1_ORA,'YYYY-MM-DD hh:mm:ss') - Timestamp#(PROVA2_DATA &' '&
PROVA2_ORA,'YYYY-MM-DD hh:mm:ss'),'mm') i've calculated the interval in
each row...
but i don't know how i can utilize this formula to create a specific field load directly in the script...the objective is to use this field in other dashboard without applicate the formula.
Solutions?
