+checkTableIndexPatchExists=SELECT 1 FROM USER_TABLES WHERE TABLE_NAME='INDEX_PATCH'
+
createTableIndexPatch=\
- CREATE TABLE IF NOT EXISTS INDEX_PATCH ( \
- IND_ANNEE_GEST VARCHAR(2) not null, \
- IND_TRI_GEST VARCHAR(1) not null, \
- IND_COMPTABLE VARCHAR(6) not null, \
- IND_IMPUTATION VARCHAR(1) not null, \
- IND_MINISTERE VARCHAR(2) not null, \
- IND_COMPTESPE VARCHAR(5) not null, \
- IND_LOCALE VARCHAR(4) not null, \
- IND_ANNEE_TRSP VARCHAR(2) not null, \
- IND_TRI_TRSP VARCHAR(1) not null, \
- IND_SECTION_BUDG VARCHAR(2), \
- IND_CPTESPENAT VARCHAR(5) not null, \
- IND_NATIONALE VARCHAR(6), \
- INDEX IX_INDEX (IND_ANNEE_GEST,IND_TRI_GEST,IND_COMPTABLE,IND_IMPUTATION,IND_MINISTERE,IND_LOCALE) \
- )
-
+ begin \
+ execute immediate 'CREATE TABLE INDEX_PATCH ( \
+ IND_ANNEE_GEST VARCHAR(2) not null, \
+ IND_TRI_GEST VARCHAR(1) not null, \
+ IND_COMPTABLE VARCHAR(6) not null, \
+ IND_IMPUTATION VARCHAR(1) not null, \
+ IND_MINISTERE VARCHAR(2) not null, \
+ IND_COMPTESPE VARCHAR(5) not null, \
+ IND_LOCALE VARCHAR(4) not null, \
+ IND_ANNEE_TRSP VARCHAR(2) not null, \
+ IND_TRI_TRSP VARCHAR(1) not null, \
+ IND_SECTION_BUDG VARCHAR(2), \
+ IND_CPTESPENAT VARCHAR(5) not null, \
+ IND_NATIONALE VARCHAR(6) \
+ )'; \
+ \
+ execute immediate 'CREATE INDEX IX_INDEX ON INDEX_PATCH(IND_ANNEE_GEST,IND_TRI_GEST,IND_COMPTABLE,IND_IMPUTATION,IND_MINISTERE,IND_LOCALE)'; \
+ end;
+
request=\
SELECT edmn.COLLECTIVITE, edmn.BUDGET, edmn.DOC_ID \
FROM EDMN_ETAT edmn \
LEFT OUTER JOIN EDMN_ETAT_LIASSES liasse ON edmn.COLLECTIVITE=liasse.COLLECTIVITE AND edmn.BUDGET=liasse.BUDGET AND edmn.DOC_ID=liasse.DOC_ID \
GROUP BY edmn.COLLECTIVITE, edmn.BUDGET, edmn.DOC_ID \
HAVING count(liasse.DOC_ID) = 0 \
- ) as toto
+ )
truncateTableIndexPatch=TRUNCATE TABLE INDEX_PATCH