Software, Software Development and other Atrocities

Once upon a time... I was a systems analyst and project manager. I had designed and implemented client/server applications, written all SQL you could think about in DB2 and Oracle, written Cobol code (arrrrggggggg) and had fights with the people in production and the DBA (everyone hates DBAs!). I was a friend of my colleagues and most of my users, and we used to go out often to drink, to drink or to drink.


A perfect life. I had one dream though. This client/server thingy was getting boring so I looked for others things to do and I found Business Intelligence, Data warehousing and OLAP. I proposed my boss to design a BI system for the university so I could play with all these nice graphic tools (much nicer than microfocus!). See picture: Screenshot of PowerPlay v5.21 by Cognos. (A rather old version but it is the only one I have. Also you may note that I use Windows 98, cos it's the only O.S that my 64Mb RAM Laptop can use :( ... I need a new laptop!!!!!!!!!!!!!!!)

A friend of mine sent me this SQL Select clause.
(The name of DB, fields and parameters have been changed)

Enjoy!

SELECT  A.AA_SEMS,
A.NU_SEMS,
A.NU_SEC_SEMS,
A.CO_DCTE_PRIN,
A.CO_SEC,
(CASE WHEN A.FG_mtr_GPO_SEC = '0'
THEN A. QT_VAC_DISP
ELSE B.QT_VAC - B.QT_ALUM_mtr
END)  AS VAC_DISP,
B.NU_GPO_SEC,
SUBSTR( VALUE(D.NO_PAT_PERS,' ') || ' ' ||
VALUE(D.NO_MAT_PERS,' ') || ' ' ||
VALUE(D.NO_NOM_PERS,' '),1,45)

FROM  (SELECT A.AA_SEMS, A.NU_SEMS, A.NU_SEC_SEMS,
A.CO_DCTE_PRIN,
A.CO_SEC, A.CO_CUR,
A.FG_mtr_GPO_SEC,
A.FG_GPO_SEC,
(B.QT_VAC-B.QT_ALUM_mtr) AS QT_VAC_DISP,
A.FG_CTL_VAC_SEC,
A.QT_VAC_mtr,
A.QT_ALUM_mtr_SEC,
A.CO_DEPC_ADM_SEC
FROM XXXXX.SECC  A
INNER JOIN XXXXX.VACT_SECC B ON
A.AA_SEMS  = B.AA_SEMS  AND
A.NU_SEMS  = B.NU_SEMS  AND
A.NU_SEC_SEMS  = B.NU_SEC_SEMS  AND
A.CO_CUR  = B.CO_CUR  AND
A.CO_SEC  = B.CO_SEC  AND
A.FG_CTL_VAC_SEC = '1'   AND
A.AA_SEMS  = :prm_nAaSEMS  AND
A.NU_SEMS  = :prm_nNuSEMS  AND
A.NU_SEC_SEMS  = :prm_nNuSecSEMS  AND
A.CO_CUR  = :prm_nCoCUR  AND
B.CO_ESPC   = :prm_nCoEspc AND
A.FG_ESTD_SEC  = '0'   AND
A.FG_ESTD_PROG_SEC = '1'  AND
A.FG_SEC_INGR  = '0'  AND
A.FG_BLOQ_mtr = '0'  AND
A.CO_CUR_REF  IS NULL  AND
A.CO_SEC_REF  IS NULL

UNION

SELECT AA_SEMS,  NU_SEMS, NU_SEC_SEMS,
CO_DCTE_PRIN,
CO_SEC,CO_CUR,
FG_mtr_GPO_SEC,
FG_GPO_SEC,
(QT_VAC_mtr-QT_ALUM_mtr) AS QT_VAC_DISP,
FG_CTL_VAC_SEC,
QT_VAC_mtr,
QT_ALUM_mtr_SEC,
CO_DEPC_ADM_SEC
FROM XXXXX.SECC
WHERE FG_CTL_VAC_SEC = '0'  AND
AA_SEMS   = :prm_nAaSEMS  AND
NU_SEMS   = :prm_nNuSEMS   AND
NU_SEC_SEMS  = :prm_nNuSecSEMS  AND
CO_CUR  = :prm_nCoCUR  AND
FG_ESTD_SEC  = '0'   AND
FG_ESTD_PROG_SEC = '1'   AND
FG_SEC_INGR  = '0'  AND
FG_BLOQ_mtr  = '0'  AND
CO_CUR_REF   IS NULL  AND
CO_SEC_REF   IS NULL   )  A


inner join XXXXX.ESPC e on
e.co_espc  = :prm_nCoEspc

inner join XXXXX.CURRI e0 on
e0.co_espc  = :prm_nCoEspc and
e0.co_CUR  = a.co_CUR and
(e0.AA_SEMS_INI_VIG*10+e0.NU_SEMS_INI_VIG
<= :prm_nAaSEMS * 10 + :prm_nNuSEMS ) AND  ((e0.AA_SEMS_FIN_VIG*10+e0.NU_SEMS_FIN_VIG  >= :prm_nAaSEMS * 10 + :prm_nNuSEMS ) OR
(e0.AA_SEMS_FIN_VIG is NULL AND
e0.NU_SEMS_FIN_VIG is NULL))   and
e0.nu_niv_CUR  = a.co_SEC/100

inner join XXXXX.DEPD e1 on
e1.co_depc  = a.co_depc_adm_SECn and
(e1.co_depc_sup  = e.co_depc or
a.co_depc_adm_SEC = XXXXX )


LEFT OUTER JOIN XXXXX.GRUPO_SECC B ON
A.AA_SEMS  = B.AA_SEMS AND
A.NU_SEMS  = B.NU_SEMS  AND
A.NU_SEC_SEMS  = B.NU_SEC_SEMS  AND
A.CO_CUR  = B.CO_CUR  AND
A.CO_SEC  = B.CO_SEC  AND
A.FG_mtr_GPO_SEC = '1'   AND
B.FG_ESTD_GPO_SEC  = '1'  AND
A.FG_GPO_SEC  = '1'

LEFT OUTER JOIN XXXXX.TRAB C ON
A.CO_DCTE_PRIN  = C.CO_TRAB

LEFT OUTER JOIN XXXXX.PERS D ON
C.CO_PERS  = D.CO_PERS


UNION

SELECT A.AA_SEMS,
A.NU_SEMS,
A.NU_SEC_SEMS,
A.CO_DCTE_PRIN,
A.CO_SEC,
(CASE WHEN A.FG_mtr_GPO_SEC = '0'
THEN A. QT_VAC_DISP
ELSE B.QT_VAC - B.QT_ALUM_mtr
END)  AS VAC_DISP,
B.NU_GPO_SEC,
SUBSTR( VALUE(D.NO_PAT_PERS,' ') || ' ' ||
VALUE(D.NO_MAT_PERS,' ') || ' ' ||
VALUE(D.NO_NOM_PERS,' '),1,45)

FROM  (SELECT  B.AA_SEMS,B.NU_SEMS, B.NU_SEC_SEMS,
B.CO_DCTE_PRIN,
A.CO_SEC, A.CO_CUR,
B.CO_SEC AS CO_SEC_REF,
B.CO_CUR AS CO_CUR_REF,
B.FG_mtr_GPO_SEC,
B.FG_GPO_SEC,
(C.QT_VAC - C.QT_ALUM_mtr) AS QT_VAC_DISP,
B.FG_CTL_VAC_SEC,
C.QT_VAC,
C.QT_ALUM_mtr  AS QT_ALUM_mtr_SEC,
A.CO_DEPC_ADM_SEC
FROM XXXXX.SECC  A

INNER JOIN XXXXX.SECC  B ON
A.AA_SEMS  = :prm_nAaSEMS  AND
A.NU_SEMS  = :prm_nNuSEMS  AND
A.NU_SEC_SEMS  = :prm_nNuSecSEMS  AND
A.CO_CUR  = :prm_nCoCUR  AND
A.CO_CUR_REF   IS NOT NULL  AND
A.CO_SEC_REF   IS NOT NULL  AND
A.AA_SEMS   = B.AA_SEMS  AND
A.NU_SEMS   = B.NU_SEMS  AND
A.NU_SEC_SEMS   = B.NU_SEC_SEMS  AND
B.CO_CUR   = A.CO_CUR_REF AND
B.CO_SEC   = A.CO_SEC_REF   AND
B.FG_ESTD_SEC  ='0'    AND
B.FG_ESTD_PROG_SEC  = '1'   AND
B.FG_SEC_INGR   = '0'   AND
B.FG_BLOQ_mtr  = '0'   AND
B.FG_CTL_VAC_SEC = '1'

INNER JOIN XXXXX.VACT_SECC C ON
A.AA_SEMS  = C.AA_SEMS  AND
A.NU_SEMS  = C.NU_SEMS  AND
A.NU_SEC_SEMS  = C.NU_SEC_SEMS AND
b.CO_CUR  = C.CO_CUR  AND
b.CO_SEC  = C.CO_SEC  AND
C.CO_ESPC  = :prm_nCoEspc

UNION

SELECT   B.AA_SEMS,B.NU_SEMS,B.NU_SEC_SEMS,
B.CO_DCTE_PRIN,
A.CO_SEC,A.CO_CUR,
B.CO_SEC AS CO_SEC_REF,
B.CO_CUR AS CO_CUR_REF,
B.FG_mtr_GPO_SEC,
B.FG_GPO_SEC,
(B.QT_VAC_mtr - B.QT_ALUM_mtr) AS QT_VAC_DISP,
B.FG_CTL_VAC_SEC,
B.QT_VAC_mtr,
B.QT_ALUM_mtr AS QT_ALUM_mtr_SEC,
A.CO_DEPC_ADM_SEC
FROM XXXXX.SECC A
INNER JOIN XXXXX.SECC B ON
A.AA_SEMS  = :prm_nAaSEMS AND
A.NU_SEMS  = :prm_nNuSEMS AND
A.NU_SEC_SEMS  = :prm_nNuSecSEMS AND
A.CO_CUR  = :prm_nCoCUR AND
A.CO_CUR_REF   IS NOT NULL AND
A.CO_SEC_REF   IS NOT NULL AND
A.AA_SEMS  = B.AA_SEMS AND
A.NU_SEMS  = B.NU_SEMS AND
A.NU_SEC_SEMS  = B.NU_SEC_SEMS AND
B.CO_CUR  = A.CO_CUR_REF AND
B.CO_SEC  = A.CO_SEC_REF AND
B.FG_ESTD_SEC  = '0'   AND
B.FG_ESTD_PROG_SEC = '1'  AND
B.FG_SEC_INGR  = '0'  AND
B.FG_BLOQ_mtr = '0'  AND
B.FG_CTL_VAC_SEC = '0'    ) A


inner join XXXXX.ESPC e on
e.co_espc = :prm_nCoEspc

inner join XXXXX.CURRI e0 on
e0.co_espc = :prm_nCoEspc  and
e0.co_CUR = a.co_CUR  and
(e0.AA_SEMS_INI_VIG*10+e0.NU_SEMS_INI_VIG
<= :prm_nAaSEMS * 10 + :prm_nNuSEMS ) AND  ((e0.AA_SEMS_FIN_VIG*10+e0.NU_SEMS_FIN_VIG  >= :prm_nAaSEMS * 10 + :prm_nNuSEMS ) OR
(e0.AA_SEMS_FIN_VIG is NULL AND
e0.NU_SEMS_FIN_VIG is NULL))   and
e0.nu_niv_CUR = a.co_SEC/100

inner join XXXXX.DEPD e1 on
e1.co_depc = a.co_depc_adm_SEC and
(e1.co_depc_sup  = e.co_depc or
a.co_depc_adm_SEC = XXXXX )


LEFT OUTER JOIN XXXXX.GRUPO_SECC B ON
A.AA_SEMS  = B.AA_SEMS  AND
A.NU_SEMS  = B.NU_SEMS  AND
A.NU_SEC_SEMS  = B.NU_SEC_SEMS  AND
A.CO_CUR_REF  = B.CO_CUR  AND
A.CO_SEC_REF  = B.CO_SEC  AND
A.FG_mtr_GPO_SEC  = '1'   AND
B.Fg_Estd_Gpo_SEC  = '1'  AND
A.FG_GPO_SEC  = '1'

LEFT OUTER JOIN XXXXX.TRAB C ON
A.CO_DCTE_PRIN = C.CO_TRAB

LEFT OUTER JOIN XXXXX.PERS D ON
C.CO_PERS = D.CO_PERS

Now, after having read this,
don't you think this is much better than writing boring essays???