DML Postgres

DML Postgres

-- ************************************************************************************
-- DEISTER WebStudio XSQL-SELECT Mon Oct 10 18:09:19 CEST 2011 Engine: postgres
-- ************************************************************************************
CREATE TEMP TABLE @tmp_resultado AS
SELECT
            CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel
                 WHEN '$NIVEL1' = 'GRPALM' THEN grpalm
                 WHEN '$NIVEL1' = 'CODALM' THEN codalm
                 WHEN '$NIVEL1' = 'DELEGA' THEN delega
                 WHEN '$NIVEL1' = 'DEPART' THEN depart
                 WHEN '$NIVEL1' = 'CLADOC' THEN cladoc
                 WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc
                 WHEN '$NIVEL1' = 'SECCIO' THEN seccio
                 WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM)
                 WHEN '$NIVEL1' = 'CODART' THEN codart
                 WHEN '$NIVEL1' = 'AUXAL0' THEN auxal0
                 WHEN '$NIVEL1' = 'AUXAL1' THEN auxal1
                 WHEN '$NIVEL1' = 'AUXAL2' THEN auxal2
                 WHEN '$NIVEL1' = 'AUXAL3' THEN auxal3
                 WHEN '$NIVEL1' = 'AUXAL4' THEN auxal4
            END   AS nivel1,
            SUM(CASE WHEN anyo = $EJERCI
                     THEN CASE WHEN $TIPVAL = 'IMPNET' THEN impnet_tot
                               WHEN $TIPVAL = 'CANMOV' THEN canmov_tot
                               WHEN $TIPVAL = 'IMPDTO' THEN impdto_tot
                               WHEN $TIPVAL = 'IMPCOS' THEN impcos_tot
                               WHEN $TIPVAL = 'IMPBRD' THEN impnet_tot - impdto_tot
                               WHEN $TIPVAL = 'IMPBRT' THEN impbru_tot
                               WHEN $TIPVAL = 'AUXNU0' THEN auxnu0_tot
                               WHEN $TIPVAL = 'AUXNU1' THEN auxnu1_tot
                               WHEN $TIPVAL = 'AUXNU2' THEN auxnu2_tot
                               WHEN $TIPVAL = 'AUXNU3' THEN auxnu3_tot
                               WHEN $TIPVAL = 'AUXNU4' THEN auxnu4_tot
                               WHEN $TIPVAL = 'AUXNU5' THEN auxnu5_tot
                               WHEN $TIPVAL = 'AUXNU6' THEN auxnu6_tot
                               WHEN $TIPVAL = 'AUXNU7' THEN auxnu7_tot
                               WHEN $TIPVAL = 'AUXNU8' THEN auxnu8_tot
                               WHEN $TIPVAL = 'AUXNU9' THEN auxnu9_tot
                           END
                END)  AS valact,
            0  AS preact,
            SUM(CASE WHEN anyo = $EJERCI - 1
                     THEN CASE WHEN $TIPVAL = 'IMPNET' THEN impnet_tot
                               WHEN $TIPVAL = 'CANMOV' THEN canmov_tot
                               WHEN $TIPVAL = 'IMPDTO' THEN impdto_tot
                               WHEN $TIPVAL = 'IMPCOS' THEN impcos_tot
                               WHEN $TIPVAL = 'IMPBRD' THEN impnet_tot - impdto_tot
                               WHEN $TIPVAL = 'IMPBRT' THEN impbru_tot
                               WHEN $TIPVAL = 'AUXNU0' THEN auxnu0_tot
                               WHEN $TIPVAL = 'AUXNU1' THEN auxnu1_tot
                               WHEN $TIPVAL = 'AUXNU2' THEN auxnu2_tot
                               WHEN $TIPVAL = 'AUXNU3' THEN auxnu3_tot
                               WHEN $TIPVAL = 'AUXNU4' THEN auxnu4_tot
                               WHEN $TIPVAL = 'AUXNU5' THEN auxnu5_tot
                               WHEN $TIPVAL = 'AUXNU6' THEN auxnu6_tot
                               WHEN $TIPVAL = 'AUXNU7' THEN auxnu7_tot
                               WHEN $TIPVAL = 'AUXNU8' THEN auxnu8_tot
                               WHEN $TIPVAL = 'AUXNU9' THEN auxnu9_tot
                           END
                END)  AS valant

  FROM gdwh_cdelarta
       INNER JOIN gdelgrpl ON gdwh_cdelarta.delega = gdelgrpl.delgrp
       INNER JOIN galmgrpl ON gdwh_cdelarta.codalm = galmgrpl.almgrp
 WHERE

            gdwh_cdelarta.anyo BETWEEN $EJERCI - 1
                                   AND $EJERCI AND
            $0

GROUP BY 1
 UNION  ALL
SELECT
            CASE WHEN '$NIVEL1' = 'GRPDEL' THEN grpdel
                 WHEN '$NIVEL1' = 'GRPALM' THEN grpalm
                 WHEN '$NIVEL1' = 'CODALM' THEN codalm
                 WHEN '$NIVEL1' = 'DELEGA' THEN delega
                 WHEN '$NIVEL1' = 'DEPART' THEN depart
                 WHEN '$NIVEL1' = 'CLADOC' THEN cladoc
                 WHEN '$NIVEL1' = 'TIPDOC' THEN tipdoc
                 WHEN '$NIVEL1' = 'SECCIO' THEN seccio
                 WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]' THEN SUBSTR(codfam, 1, $NIVFAM)
                 WHEN '$NIVEL1' = 'CODART' THEN codart
            END  AS nivel1,
            0  AS valact,
            SUM(CASE WHEN $TIPVAL = 'IMPNET' THEN import
                     WHEN $TIPVAL = 'CANMOV' THEN cantid
                     WHEN $TIPVAL = 'IMPDTO' THEN import
                     WHEN $TIPVAL = 'IMPCOS' THEN import
                     WHEN $TIPVAL = 'IMPBRD' THEN import
                     WHEN $TIPVAL = 'IMPBRT' THEN import
                END)  AS preact,
            0  AS valant

  FROM gcom_presupa
       INNER JOIN gdelgrpl ON gcom_presupa.delega = gdelgrpl.delgrp
       INNER JOIN galmgrpl ON gcom_presupa.codalm = galmgrpl.almgrp
 WHERE

            gcom_presupa.anyo   = $EJERCI AND
            gcom_presupa.tippre = $CODPRE AND
            $0

GROUP BY 1
;
SELECT
        nivel1,
        CASE WHEN '$NIVEL1' = 'GRPDEL' THEN gdwh_get_descrip('gdelgrph', nivel1)
             WHEN '$NIVEL1' = 'GRPALM' THEN gdwh_get_descrip('galmgrph', nivel1)
             WHEN '$NIVEL1' = 'CODALM' THEN gdwh_get_descrip('galmacen', nivel1)
             WHEN '$NIVEL1' = 'DELEGA' THEN gdwh_get_descrip('gdelegac', nivel1)
             WHEN '$NIVEL1' = 'DEPART' THEN gdwh_get_descrip('gdeparta', nivel1)
             WHEN '$NIVEL1' = 'CLADOC' THEN 'DEPENDE CLASE DOCUMENTO'
             WHEN '$NIVEL1' = 'TIPDOC' THEN 'DEPENDE TIPO DOCUMENTO'
             WHEN '$NIVEL1' = 'SECCIO' THEN gdwh_get_descrip('gcontipo', nivel1)
             WHEN '$NIVEL1' = 'CODFAM[1,$NIVFAM]'
                                       THEN gdwh_get_descrip('gartfami', nivel1)
             WHEN '$NIVEL1' = 'CODART' THEN gdwh_get_descrip('garticul', nivel1)
             WHEN '$NIVEL1' IN ('AUXAL0','AUXAL1','AUXAL2','AUXAL3','AUXAL4') THEN ''
        END  AS desniv1,
        CAST((($EJERCI*100)+1) AS text)||':'||CAST((($EJERCI*100)+12) AS text)  AS anymesact,
        CAST(((($EJERCI-1)*100)+1) AS text)||':'||CAST(((($EJERCI-1)*100)+12) AS text)  AS anymesant,
        SUM(COALESCE(valact, 0))  AS valact, SUM(COALESCE(preact, 0))  AS preact, ROUND(0,2)   AS varpre,
        SUM(COALESCE(valant, 0))  AS valant, ROUND(0,2)   AS vartot

  FROM @tmp_resultado

GROUP BY 1, 2, 3, 4
HAVING
        SUM(valact) != 0 OR
        SUM(preact) != 0 OR
        SUM(valant) != 0