SELECT COMMUNITYID, VARIABLEID, DESCRIPTION, DOMAINID, DEFINITIONSETID,UNIVERSALVALUE, CIRCULARITY, REPETITIVE, PROVFG, COMPOSED, TRASC, UTILCOE, UTILBIL, UTILBILIAS, UTILANAC, ISCODED, UTILCR, UTILANAG 
FROM VARIABLE 
WHERE COMMUNITYID = @COMMUNITYID 
--AND VARIABLEID = @VARIABLEID 
AND UTILBILIAS <> 0
;

SELECT * FROM VARIABLE WHERE COMMUNITYID='PUMABAN' AND PROVFG NOT IN ('NULL', '0');

SELECT COMMUNITYID, SUBSTR(SETID,2) AS VARIABLEID, DOMAINID, SETID AS DEFINITIONSETID, CRITERIONPARAM AS DEFINITIONSET FROM DOMAINSET WHERE COMMUNITYID = @COMMUNITYID AND SETID LIKE @VARIABLEID;

SELECT COMMUNITYID, DOMAINID, DESCRIPTION, ALTERNATEID, ISCODED, PLATFORMDEPDTYPE, UNIVERSALVALUE
FROM DOMAIN
WHERE COMMUNITYID='PUMABAN' 
--AND DESCRIPTION like '%IMPORTO%'
;

SELECT V.VARIABLEID, V.DESCRIPTION, V.DOMAINID as DOMINIO , DS.SETID, DS.CRITERIONPARAM 
FROM VARIABLE V , DOMAIN D , DOMAINSET DS
WHERE V.COMMUNITYID=D.COMMUNITYID
AND V.COMMUNITYID=DS.COMMUNITYID
AND V.DOMAINID=D.DOMAINID
AND DS.SETID LIKE V.DEFINITIONSETID||'%'
AND V.COMMUNITYID = 'PUMABAN'
AND V.VARIABLEID like '%35%'
ORDER BY V.VARIABLEID , DS.SETID; 

SELECT COMMUNITYID, COMBINATIONGROUPID, DESCRIPTION, STARTDATE, ENDDATE, VARIABLEID, SETID, ISBYCRITERION, CRITERIONTYPE, CRITERIONPARAM, ISALLOWED
FROM COMBINATIONGROUP
WHERE COMMUNITYID='PUMABAN' AND VARIABLEID = '05310';

SELECT V.VARIABLEID, 
GROUP_CONCAT(C.CRITERIONPARAM,',') as CONTROLLI_AUTOMATICI
FROM VARIABLE V , COMBINATIONGROUP C 
WHERE V.COMMUNITYID=C.COMMUNITYID
AND V.VARIABLEID=C.VARIABLEID
AND V.COMMUNITYID = 'PUMABAN'
--AND V.VARIABLEID= '00031'
GROUP BY V.VARIABLEID 
ORDER BY V.VARIABLEID; 

SELECT COMMUNITYID, CONTEXTID, LOCALID, OBJECTTYPE, PROPERTYID, PURPOSE, LABEL, LOCALE,OBJSURVEYID
FROM LABEL
WHERE COMMUNITYID='PUMABAN' AND OBJECTTYPE='VARIABLE' AND LOCALID = '05311';

SELECT V.VARIABLEID, L.LABEL,
GROUP_CONCAT(L.PURPOSE,',') as CUBI 
FROM VARIABLE V , LABEL L
WHERE V.COMMUNITYID=L.COMMUNITYID
AND V.VARIABLEID=L.LOCALID
AND V.COMMUNITYID = 'PUMABAN'
GROUP BY V.VARIABLEID 
ORDER BY V.VARIABLEID; 

SELECT COMMUNITYID, EXPRESSIONID,TEMPLATEID, EXPRESSIONTYPE, DESCRIPTION, EXPRESSIONSTRING 
FROM EXPRESSION 
WHERE COMMUNITYID = @COMMUNITYID AND EXPRESSIONID LIKE @EXPRESSIONID ;

SELECT EXPRESSIONID, DESCRIPTION , EXPRESSIONSTRING FROM EXPRESSION 
WHERE COMMUNITYID = 'PUMABAN'
AND EXPRESSIONTYPE IN ('C','G','C_G')
ORDER BY EXPRESSIONID;

SELECT COMMUNITYID, EXPRESSIONID, EXPRESSIONTYPE, DESCRIPTION, EXPRESSIONSTRING 
FROM EXPRESSION 
WHERE COMMUNITYID = @COMMUNITYID AND EXPRESSIONTYPE IN ('C','G','C_G') AND EXPRESSIONSTRING LIKE '%TCOR006%' ;

SELECT COMMUNITYID, EXPRESSIONID, VARIABLEID, ROLE
FROM EXPRSTRUCTURE
WHERE COMMUNITYID='PUMABAN' AND EXPRESSIONID='05347EC' ;


SELECT E.EXPRESSIONID,E.EXPRESSIONTYPE,
GROUP_CONCAT(EX.VARIABLEID,',') as CAMPI_DER_RAC,
E.DESCRIPTION , E.EXPRESSIONSTRING 
FROM EXPRESSION E , EXPRSTRUCTURE EX
WHERE E.COMMUNITYID=EX.COMMUNITYID
AND E.EXPRESSIONID=EX.EXPRESSIONID
and E.COMMUNITYID = 'PUMABAN'
and E.EXPRESSIONTYPE IN ('C','G','C_G')
and EX.ROLE IN ('DER', 'RAC')
GROUP BY E.EXPRESSIONID
ORDER BY E.EXPRESSIONID ;

SELECT E.EXPRESSIONID,E.EXPRESSIONTYPE,
GROUP_CONCAT(EX.VARIABLEID,',') as CAMPI_DER,  
E.DESCRIPTION , E.EXPRESSIONSTRING 
FROM EXPRESSION E , EXPRSTRUCTURE EX
WHERE E.COMMUNITYID=EX.COMMUNITYID
AND E.EXPRESSIONID=EX.EXPRESSIONID
and E.COMMUNITYID = 'PUMABAN'
and E.EXPRESSIONTYPE IN ('C','G','C_G')
and EX.ROLE IN ('RAC')
and EX.VARIABLEID IN ('05312')
GROUP BY E.EXPRESSIONID
ORDER BY E.EXPRESSIONID;

SELECT COMMUNITYID, SURVEYID, STARTDATE, ENDDATE, DESCRIPTION, SURVEYTYPE, DEF_CIRCULARITY
FROM SURVEY
WHERE COMMUNITYID='PUMABAN' AND SURVEYID='INPUT';

SELECT COMMUNITYID, CUBEID, DESCRIPTION, CUBESTATTYPE, PROCESSCUBESETID, SURVEYID, USAGELEVEL, 
ISPROCESSCUBESET, FREQUENCY, CIRCULARITY 
FROM CUBE WHERE COMMUNITYID = @COMMUNITYID AND CUBEID LIKE @CUBEID AND CUBESTATTYPE = @CUBESTATTYPE;

SELECT COMMUNITYID, CUBEID, CUBECONTEXTID, CUBESETID, SURVEYID, ISCOLLECTED, ISCUBESET, CUBESTATTYPE
FROM CUBESETCOMP
WHERE COMMUNITYID='PUMABAN' AND CUBESETID='0112302';

SELECT COMMUNITYID, CUBEID, VARIABLEID, ROLE, SURVEYID, DOMAINID, SETID, UNIQUEVALUEID, PROPERTY, CUBESTATTYPE 
FROM STRUCTUREITEM 
WHERE COMMUNITYID = @COMMUNITYID AND CUBEID = @CUBEID AND CUBESTATTYPE = @CUBESTATTYPE;

SELECT COMMUNITYID, CUBEID, VARIABLEID, ROLE, SURVEYID, DOMAINID, SETID, UNIQUEVALUEID, PROPERTY, CUBESTATTYPE 
FROM STRUCTUREITEM WHERE COMMUNITYID = @COMMUNITYID AND CUBESTATTYPE = @CUBESTATTYPE 
AND VARIABLEID = @VARIABLEID;

SELECT COMMUNITYID, CUBEID, CUBESTATTYPE, VARIABLEID, PROPERTY
FROM STRUCTUREITEM 
WHERE TRIM(PROPERTY) <>'' AND CUBESTATTYPE IN ('FTO','FTA') 
--AND CUBEID='0112302_1'
AND VARIABLEID= '00010'; 

select * from expression where expressionid like '%W_C_0112302%';

/*Esiste una query preimmpostat che permette di vedere tuttie le variabili  con i relatici controlli  con il descrittivo dei controlli similare ai  vecchi fogli di analisi ? */



select 
SUBSTR(RFE.expressionid,-9) FTO,
--substr(RFE.routineid, 1, 5) VARIABLEID,
V.VARIABLEID VARIABLEID,
V.description DESCR_VAR,
E.expressionid ROUTINE_CONTROLLO,
E.expressionstring DESCR_ROUT_CONTR
from ROUTINEFOREXPR RFE, expression E, variable V 
where RFE.routineid=E.expressionid 
and substr(RFE.routineid, 1, 5) = V.variableid 
and RFE.expressionid in ('W_C_0112302_1', 'W_CA_0112302_1')
order by VARIABLEID;


select 
SUBSTR(RFE.expressionid,-9) FTO,
v.variableid,
V.description DESCR_VAR,
E.expressionid ROUTINE_CONTROLLO,
case 
when substr(RFE.routineid,6,1) = 'G' then
(select description from expression g where g.expressionid = '00000' || substr(RFE.routineid,6,2))
else E.expressionstring
end DESCR_ROUT_CONTR
from ROUTINEFOREXPR RFE, expression E, variable V 
where RFE.routineid=E.expressionid 
and substr(RFE.routineid, 1, 5) = V.variableid 
and RFE.expressionid in ('W_C_0112302_1', 'W_CA_0112302_1')
union
select
s.cubeid,
s.variableid,
v.description,
' ',
' '
from
structureitem s, variable v
where 
s.variableid = v.variableid
and cubeid = '0112302_1'
except
select
SUBSTR(RFE.expressionid,-9) FTO,
substr(RFE.routineid, 1, 5) VARIABLEID,
V.description DESCR_VAR,
' ',
' '
from
ROUTINEFOREXPR RFE, variable V 
where
substr(RFE.routineid, 1, 5) = V.variableid 
and RFE.expressionid in ('W_C_0112302_1', 'W_CA_0112302_1')
order by VARIABLEID
;

SELECT COMMUNITYID, SURVEYID, STARTDATE, ENDDATE, DESCRIPTION, SURVEYTYPE, DEF_CIRCULARITY
FROM SURVEY
WHERE COMMUNITYID='PUMABAN' AND SURVEYID = 'A1';


SELECT COMMUNITYID, CUBEID, DESCRIPTION, CUBESTATTYPE, PROCESSCUBESETID, SURVEYID, USAGELEVEL, 
ISPROCESSCUBESET, FREQUENCY, CIRCULARITY 
FROM CUBE WHERE COMMUNITYID = @COMMUNITYID AND CUBEID LIKE @CUBEID AND CUBESTATTYPE = @CUBESTATTYPE;

SELECT COMMUNITYID, CUBEID, CUBECONTEXTID, CUBESETID, SURVEYID, ISCOLLECTED, ISCUBESET, CUBESTATTYPE
FROM CUBESETCOMP
WHERE COMMUNITYID='PUMABAN' AND CUBESETID='A1_5800526';

SELECT COMMUNITYID, CUBEID, VARIABLEID, ROLE, SURVEYID, DOMAINID, SETID, UNIQUEVALUEID, PROPERTY, CUBESTATTYPE 
FROM STRUCTUREITEM 
WHERE COMMUNITYID = @COMMUNITYID AND CUBEID = @CUBEID AND CUBESTATTYPE = @CUBESTATTYPE;

SELECT COMMUNITYID, CUBEID, VARIABLEID, ROLE, SURVEYID, DOMAINID, SETID, UNIQUEVALUEID, PROPERTY, CUBESTATTYPE 
FROM STRUCTUREITEM WHERE COMMUNITYID = @COMMUNITYID AND CUBESTATTYPE = @CUBESTATTYPE 
AND VARIABLEID = @VARIABLEID AND SURVEYID='W2';

SELECT COMMUNITYID, TRANSFORMATIONID, DESCRIPTION, EXPRESSIONID, RESULTLOCALID,EXPRESSIONTYPE
FROM TRANSFORMATION
WHERE COMMUNITYID='PUMABAN' AND RESULTLOCALID = '0112302_1_E';

SELECT COMMUNITYID, EXPRESSIONID, DESCRIPTION, EXPRESSIONSTRING, EXPRESSIONTYPE, TEMPLATEID, USEDVALUE
FROM EXPRESSION
WHERE COMMUNITYID='PUMABAN' AND EXPRESSIONID like '%ACA_0112302_1%' AND EXPRESSIONTYPE ='ACA';

SELECT EP.EXPRESSIONID, EP.EXPRESSIONTYPE, EP.GENERATEDCUBE, EP.EXPRCOMPONENT 
FROM EXPRFORTRANSFORMATION EP 
WHERE EP.EXPRESSIONID like '%0112302_1%' AND EP.EXPRESSIONTYPE = 'ACA' ORDER BY EP.EXPRCOMPONENT ASC ;

SELECT EP.EXPRESSIONID, EP.EXPRESSIONTYPE, EP.ROUTINEID, ES.VARIABLEID, ES.ROLE, V.DESCRIPTION AS VARDESCR 
FROM ROUTINEFOREXPR EP, EXPRSTRUCTURE ES, VARIABLE V 
WHERE EP.ROUTINEID = ES.EXPRESSIONID AND ES.COMMUNITYID = V.COMMUNITYID AND ES.VARIABLEID = V.VARIABLEID AND EP.EXPRESSIONID like '%W_F_0112302_1%' AND EP.EXPRESSIONTYPE in ('FTO_F','FTO_C','FTO_CA');

select 
SUBSTR(RFE.expressionid,-9) FTO,
--substr(RFE.routineid, 1, 5) VARIABLEID,
V.VARIABLEID VARIABLEID,
V.description DESCR_VAR,
E.expressionid ROUTINE_CONTROLLO,
E.expressionstring DESCR_ROUT_CONTR
from ROUTINEFOREXPR RFE, expression E, variable V 
where RFE.routineid=E.expressionid 
and substr(RFE.routineid, 1, 5) = V.variableid 
and RFE.expressionid in ('W_C_0113126_1', 'W_CA_0113126_1')
order by VARIABLEID;


select 
SUBSTR(RFE.expressionid,-9) FTO,
--substr(RFE.routineid, 1, 5) VARIABLEID,
V.VARIABLEID VARIABLEID,
V.description DESCR_VAR,
E.expressionid ROUTINE,
E.expressionstring DESCR_ROUT_CONTR
from ROUTINEFOREXPR RFE, expression E, variable V 
where RFE.routineid=E.expressionid 
and substr(RFE.routineid, 1, 5) = V.variableid 
and RFE.expressionid in ('W_F_0113126_1','W_C_0113126_1','W_CA_0113126_1')
order by VARIABLEID;

SELECT COMMUNITYID, CUBEID, VARIABLEID, ROLE, SURVEYID, DOMAINID, SETID, UNIQUEVALUEID, PROPERTY, CUBESTATTYPE 
FROM STRUCTUREITEM 
WHERE COMMUNITYID = @COMMUNITYID AND CUBEID = @CUBEID AND CUBESTATTYPE = @CUBESTATTYPE AND VARIABLEID='00701';

SELECT SUBSTR(EP.EXPRESSIONID,7) AS CUBEID, SUBSTR(EP.EXPRESSIONTYPE,1,3) AS CUBESTATTYPE, EP.CUBEID AS RELCUBEID, C.DESCRIPTION AS RELCUBEIDDESCR 
FROM CUBEFOREXPR EP, CUBE C 
WHERE EP.CUBEID = C.CUBEID AND EP.EXPRESSIONID like '%112302_1%'  AND EP.EXPRESSIONTYPE = 'FTO_FTA';

SELECT COMMUNITYID, CUBEID, DESCRIPTION, CUBESTATTYPE, PROCESSCUBESETID, SURVEYID, USAGELEVEL, 
ISPROCESSCUBESET, FREQUENCY, CIRCULARITY 
FROM CUBE WHERE COMMUNITYID = @COMMUNITYID AND CUBEID LIKE @CUBEID AND CUBESTATTYPE = @CUBESTATTYPE;

SELECT COMMUNITYID, CUBEID, VARIABLEID, ROLE, SURVEYID, DOMAINID, SETID, UNIQUEVALUEID, PROPERTY, CUBESTATTYPE 
FROM STRUCTUREITEM 
WHERE COMMUNITYID = @COMMUNITYID AND CUBEID = @CUBEID AND CUBESTATTYPE = @CUBESTATTYPE;

SELECT COMMUNITYID, PROPERTY AS ROUTINEID, VARIABLEID, CUBEID, CUBESTATTYPE 
FROM DB_PUMABAN_R20210930_V20210924.STRUCTUREITEM 
WHERE CUBESTATTYPE IN ('FTA_E') AND COMMUNITYID = 'PUMABAN'
EXCEPT
SELECT COMMUNITYID, PROPERTY AS ROUTINEID, VARIABLEID, CUBEID, CUBESTATTYPE 
FROM DB_PUMABAN_R20210630_V20210625.STRUCTUREITEM 
WHERE CUBESTATTYPE IN ('FTA_E') AND COMMUNITYID = 'PUMABAN';

SELECT COMMUNITYID, TRANSFORMATIONID, DESCRIPTION, EXPRESSIONID, RESULTLOCALID,EXPRESSIONTYPE
FROM TRANSFORMATION
WHERE COMMUNITYID='PUMABAN' AND EXPRESSIONTYPE ='GEN' AND RESULTLOCALID = 'A1_5800526_1100';

SELECT COMMUNITYID, EXPRESSIONID, DESCRIPTION, EXPRESSIONSTRING, EXPRESSIONTYPE, TEMPLATEID, USEDVALUE
FROM EXPRESSION
WHERE COMMUNITYID='PUMABAN' AND EXPRESSIONID like '%GEN_A1_5800526_1100%' AND EXPRESSIONTYPE ='GEN';

SELECT EP.EXPRESSIONID, EP.EXPRESSIONTYPE, EP.GENERATEDCUBE, EP.EXPRCOMPONENT 
FROM EXPRFORTRANSFORMATION EP 
WHERE EP.EXPRESSIONID like '%A1_5800526_0011%' AND EP.EXPRESSIONTYPE = 'GEN' ORDER BY EP.EXPRCOMPONENT ASC ;

SELECT EP.EXPRESSIONID, EP.EXPRESSIONTYPE, EP.ROUTINEID, ES.VARIABLEID, ES.ROLE, V.DESCRIPTION AS VARDESCR 
FROM ROUTINEFOREXPR EP, EXPRSTRUCTURE ES, VARIABLE V 
WHERE EP.ROUTINEID = ES.EXPRESSIONID AND ES.COMMUNITYID = V.COMMUNITYID AND ES.VARIABLEID = V.VARIABLEID AND EP.EXPRESSIONID like '%A1_5800526_0011%' AND EP.EXPRESSIONTYPE in ('FTD_FTO_E','FTD_FTA_E');

select 
SUBSTR(RFE.expressionid,-25) FTO,
--substr(RFE.routineid, 1, 5) VARIABLEID,
V.VARIABLEID VARIABLEID,
V.description DESCR_VAR,
E.expressionid ROUTINE,
E.expressionstring DESCR_ROUT_CONTR
from ROUTINEFOREXPR RFE, expression E, variable V 
where RFE.routineid=E.expressionid 
and substr(RFE.routineid, 1, 5) = V.variableid 
and RFE.expressionid in ('W_A1_5800526_0011_0112302_E')
order by VARIABLEID;































