db2 case when subselect

Question

I have the following SQL query on IBM DB2.

      SUM(CASE
              WHEN  verzijaplaca.vpl_vrsteplacila = 9150 THEN
                (select sum(verplaca.vpl_bruto) 
                        from pet320.verzijaplaca as verplaca
                       )    
              ELSE 0
            END) AS "brutoplacazaure"

The inner select works, but when I include it in CASE when it reports error.

ERROR: An operand of a column function is invalid.

DB2 SQL Error: SQLCODE=-112, SQLSTATE=42607, SQLERRMC=null, DRIVER=3.57.91 Error Code: -112

Also If I run only

    SUM(CASE
              WHEN  verzijaplaca.vpl_vrsteplacila = 9150 THEN
                (1.0)   
              ELSE 0
            END) AS "brutoplacazaure"

it works

Any suggestions?? It seems that DB 2 doesn't support the inner sql in case when case or smth like that

Thank you

the whole sql query is the following

  SELECT
      zaposleni.za_koda AS "za_koda",
            MAX(enotezpiz.ezp_rsza) AS "ezp_rsza",
            MAX(zaposleni.za_polnoime) AS "za_polnoime",
            MAX(verzije.ve_datnamena) AS "ve_datnamena",
            MAX(verzije.ve_datizp) AS "ve_datizp",
            MAX(opp_telefonodgos) AS "opp_telefonodgos",
            MAX(pod_krajzaizpise ||', ') AS "pod_krajzaizpise",
            MAX(racuni.ra_stracuna) AS "ra_stracuna",
            MAX(racuni.ra_modul) AS "ra_modul",
            MAX(racuni.ra_sklstev) AS "ra_sklstev",
            MAX(verzije.ve_datizp) AS "ve_datizp",
            MAX(verzije.ve_naziv) AS "ve_naziv",
            SUM(CASE

                           WHEN vrsteplacila.vp_skupinevrpl in (1,2,3,4,16) and vrsteplacila.vp_udodatkov = 0 THEN verzijaplaca.vpl_eure
                           ELSE 0
            END) AS "mfure",

            MAX(dmzaposlenih.dmz_enotezpiz) AS "dmz_enotezpiz",

            (select  
                        SUM(olajsave.ozz_znesekolajsave) / 12
                           from 
                            pet320.olajsavedczaposlenih as olajsave
                           INNER JOIN 
                            pet320.verzije as verzija1
                           ON 
                            olajsave.ooz_datumod <= verzija1.ve_datkm AND (olajsave.ooz_datumdo IS NULL OR olajsave.ooz_datumdo >= verzija1.ve_datzm)
                           INNER JOIN 
                            pet320.zaposleni as zapp
                           ON 
                            olajsave.ozz_zaposleni = zapp.za_id_za
                           INNER JOIN
                            pet320.VERZIJAPLACA as vpl
                           ON
                           vpl.vpl_verzije = verzija1.ve_id_ve
                           AND zapp.za_id_za = vpl.vpl_zaposleni

                           where 
                            1=1

                           AND (vpl.vpl_vrsteplacila = 9150 OR vpl.vpl_skupinevrpl = 6)) AS "vz_znesvzddc",




            SUM(CASE
                           WHEN vrsteplacila.vp_skupinevrpl  = 3 AND vrsteplacila.vp_udodatkov = 0 THEN verzijaplaca.vpl_eure
                           WHEN vrsteplacila.vp_skupinevrpl  = 4 AND vrsteplacila.vp_udodatkov = 1 THEN verzijaplaca.vpl_eure
                           ELSE
                           0
            END) AS "bolovalure",


            SUM(CASE
                           WHEN vrsteplacila.vp_skupinevrpl = 4 AND vrsteplacila.vp_udodatkov = 0 THEN verzijaplaca.vpl_eure
                           ELSE
                           0
            END) AS "izostanekzdelaure",


            SUM(CASE
                           WHEN vrsteplacila.vp_skupinevrpl = 3 THEN verzijaplaca.vpl_bruto
                           ELSE
                           0
            END) AS "brutoznesekboleznine",


            SUM(CASE WHEN vrsteplacila.vp_skupinevrpl = 16 THEN verzijaplaca.vpl_bruto
                           ELSE
                           0
            END) AS "brutodopolnega",

            SUM(CASE WHEN vrsteplacila.vp_skupinevrpl = 16 and vrsteplacila.vp_udodatkov = 0 THEN verzijaplaca.vpl_eure
                           ELSE
                           0
            END) AS "uredopolenga",



            SUM(CASE
                           WHEN vrsteplacila.vp_skupinevrpl IN (16) THEN (verzijaplaca.vpl_bruto - verzijaplaca.vpl_neto)
                           ELSE
                           0
            END) AS "prispevkizasocvarnost",


            SUM(CASE
                           WHEN vrsteplacila.vp_skupinevrpl IN (16) THEN verzijaplaca.vpl_akdohod
                           ELSE
                           0
            END) AS "akdohodnine",
            SUM(CASE

WHEN verzijaplaca.vpl_skupinevrpl IN (16) THEN verzijaplaca.vpl_neto - verzijaplaca.vpl_akdohod

                           ELSE
                           0
            END) AS "netonadomestilo",
            SUM(CASE WHEN  verzijaplaca.vpl_vrsteplacila = 9150 THEN
                (select sum(verplaca.vpl_bruto) 
                        from pet320.verzijaplaca as verplaca
                       INNER JOIN
                        pet320.verzije as ver
                        ON
                        ver.ve_id_ve = verplaca.vpl_verzije
                        INNER JOIN
                        pet320.zaposleni as zapo
                        ON
                        zapo.za_id_za = verplaca.vpl_zaposleni
                        AND ver.ve_id_ve = verplaca.vpl_verzije

                         where verplaca.vpl_vrsteplacila in (select vp_id_vp from pet320.vrsteplacila where vp_skupinevrpl in (1,2))
                          and verplaca.vpl_zaposleni = zapo.za_id_za
                          and verplaca.vpl_verzije = ver.ve_id_ve)  
              ELSE 0
            END) AS "brutoplacazaure"

  FROM
            pet320.verzijaplaca AS verzijaplaca

        INNER JOIN
                        pet320.vrsteplacila AS   vrsteplacila
        ON
                        verzijaplaca.vpl_vrsteplacila = vrsteplacila.vp_id_vp

        INNER JOIN
                        pet320.verzije AS verzije
        ON
                        verzijaplaca.vpl_verzije = verzije.ve_id_ve

        INNER JOIN
                        pet320.zaposleni AS zaposleni
        ON
                        verzijaplaca.vpl_zaposleni = zaposleni.za_id_za

        INNER JOIN
                        (SELECT
                                       a.*
                        FROM
                                       pet320.dmzaposlenih AS a
                        INNER JOIN
                                       (SELECT
                                                       dmz_zaposleni,
                                                       MAX(dmz_datumod) AS max_dmz_datumod
                                       FROM
                                                       pet320.dmzaposlenih
                                       GROUP BY
                                                       dmz_zaposleni) AS b
                        ON
                                       a.dmz_zaposleni = b.dmz_zaposleni
                                       AND a.dmz_datumod = b.max_dmz_datumod) as dmzaposlenih
        ON
                        dmzaposlenih.dmz_zaposleni = verzijaplaca.vpl_zaposleni

        INNER JOIN
                        pet320.enotezpiz AS enotezpiz
        ON
                        dmzaposlenih.dmz_enotezpiz = enotezpiz.ezp_id_ezp

        LEFT JOIN
                        pet320.osnovnipodplace AS osnovnipodplace
        ON
                        1=1

        INNER JOIN
                        pet320.racuni AS racuni
        ON
                        osnovnipodplace.opp_racuni = racuni.ra_id_ra

        INNER JOIN
                        pet320.podjetja AS podjetja
        ON
                        osnovnipodplace.opp_podjetja = podjetja.pod_id_pod

        LEFT JOIN
                        pet320.verzijazaposleni AS verzijazaposleni
        ON
                        verzijazaposleni.vz_zaposleni = zaposleni.za_id_za 
                        AND verzijazaposleni.vz_verzije = verzije.ve_id_ve

        INNER JOIN
                        pet320.verzijastrmesta as verzijastrmesta
        ON
                        verzijastrmesta.vs_verzije = verzije.ve_id_ve
                        AND verzijastrmesta.vs_strmesta = dmzaposlenih.dmz_strmesta
        INNER JOIN
                        pet320.verzijaorgenote AS verzijaorgenote
        ON
                        verzijaorgenote.vo_verzije = verzije.ve_id_ve
                        AND verzijaorgenote.vo_orgenote = dmzaposlenih.dmz_orgenote
        INNER JOIN
                        pet320.zaposinvalidi AS zaposinvalidi
        ON
                        zaposinvalidi.zi_zaposleni = verzijaplaca.vpl_zaposleni and zi_datdo is null

        INNER JOIN
                        pet320.verzijasumstavki AS verzijasumstavki
        ON
                  verzijasumstavki.vss_verzije = verzijaplaca.vpl_verzije AND
                  verzijasumstavki.vss_zaposleni = verzijaplaca.vpl_zaposleni AND
                  verzijasumstavki.vss_vrsteplacila = 9301


        WHERE
                        1=1
                        AND vrsteplacila.vp_skupinevrpl  in (1,2,3,4,16)
                        AND (verzijaplaca.vpl_verzije = 215)
                        AND (verzijaplaca.vpl_zaposleni  IS NULL OR 1=1)
                        AND (verzijaplaca.vpl_strm_strmesta  IS NULL OR 1=1)
                        AND (dmzaposlenih.dmz_orgenote  IS NULL OR 1=1)
                        AND (dmzaposlenih.dmz_izplacilnamesta  IS NULL OR 1=1)
                        AND (verzijaplaca.vpl_placilnirazredi  IS NULL OR 1=1)
                        AND (dmzaposlenih.dmz_vrstapog  IN (1,0))

            AND verzijaplaca.vpl_zaposleni in (select distinct vpl_zaposleni from pet320.verzijaplaca  where vpl_skupinevrpl = 16 AND vpl_verzije = 215)

        group by dmzaposlenih.dmz_enotezpiz,
                 zaposleni.za_koda


        ORDER BY
                        dmzaposlenih.dmz_enotezpiz,
                 zaposleni.za_koda

           INNER JOIN
            (SELECT
                           a.*
            FROM
                           pet320.dmzaposlenih AS a
            INNER JOIN
                           (SELECT
                                           dmz_zaposleni,
                                           MAX(dmz_datumod) AS max_dmz_datumod
                           FROM
                                           pet320.dmzaposlenih
                           GROUP BY
                                           dmz_zaposleni) AS b
            ON
                           a.dmz_zaposleni = b.dmz_zaposleni
                           AND a.dmz_datumod = b.max_dmz_datumod) as dmzaposlenih

ON dmzaposlenih.dmz_zaposleni = verzijaplaca.vpl_zaposleni

INNER JOIN pet320.enotezpiz AS enotezpiz ON dmzaposlenih.dmz_enotezpiz = enotezpiz.ezp_id_ezp

LEFT JOIN pet320.osnovnipodplace AS osnovnipodplace ON 1=1

INNER JOIN pet320.racuni AS racuni ON osnovnipodplace.opp_racuni = racuni.ra_id_ra

INNER JOIN pet320.podjetja AS podjetja ON osnovnipodplace.opp_podjetja = podjetja.pod_id_pod

LEFT JOIN pet320.verzijazaposleni AS verzijazaposleni ON verzijazaposleni.vz_zaposleni = zaposleni.za_id_za AND verzijazaposleni.vz_verzije = verzije.ve_id_ve

INNER JOIN pet320.verzijastrmesta as verzijastrmesta ON verzijastrmesta.vs_verzije = verzije.ve_id_ve AND verzijastrmesta.vs_strmesta = dmzaposlenih.dmz_strmesta INNER JOIN pet320.verzijaorgenote AS verzijaorgenote ON verzijaorgenote.vo_verzije = verzije.ve_id_ve AND verzijaorgenote.vo_orgenote = dmzaposlenih.dmz_orgenote INNER JOIN pet320.zaposinvalidi AS zaposinvalidi ON zaposinvalidi.zi_zaposleni = verzijaplaca.vpl_zaposleni and zi_datdo is null

INNER JOIN pet320.verzijasumstavki AS verzijasumstavki ON verzijasumstavki.vss_verzije = verzijaplaca.vpl_verzije AND verzijasumstavki.vss_zaposleni = verzijaplaca.vpl_zaposleni AND verzijasumstavki.vss_vrsteplacila = 9301

WHERE 1=1 AND vrsteplacila.vp_skupinevrpl in (1,2,3,4,16) AND (verzijaplaca.vpl_verzije = 215) AND (verzijaplaca.vpl_zaposleni IS NULL OR 1=1) AND (verzijaplaca.vpl_strm_strmesta IS NULL OR 1=1) AND (dmzaposlenih.dmz_orgenote IS NULL OR 1=1) AND (dmzaposlenih.dmz_izplacilnamesta IS NULL OR 1=1) AND (verzijaplaca.vpl_placilnirazredi IS NULL OR 1=1) AND (dmzaposlenih.dmz_vrstapog IN (1,0))

AND verzijaplaca.vpl_zaposleni in (select distinct vpl_zaposleni from pet320.verzijaplaca  where vpl_skupinevrpl = 16 AND vpl_verzije = 215)

group by dmzaposlenih.dmz_enotezpiz, zaposleni.za_koda

ORDER BY dmzaposlenih.dmz_enotezpiz, zaposleni.za_koda

Answer 1

The query as you tried to write it would run

select sum(verplaca.vpl_bruto) 
    from pet320.verzijaplaca as verplaca

and produce the exact same result every time the case statement was true. Even if you can do it that way, you shouldn't, because it's a huge waste of time to run that query over and over. Instead, run that statement once and store the value. Then refer to the stored value whenever you need it. Here are a couple of options:

with vpl_bruto_sum as (
     select sum(verplaca.vpl_bruto) as total
        from pet320.verzijaplaca as verplaca
)
select sum(case when verzijaplaca.vpl_vrsteplacila = 9150 
               then vpl_bruto_sum.total else 0 
           end
       )
    from pet320.verzijaplaca
    inner join vpl_bruto_sum on 1=1;

Or you could make thinks simpler by using the join condition instead of the inner case statement:

with vpl_bruto_sum as (
     select sum(verplaca.vpl_bruto) as total
        from pet320.verzijaplaca as verplaca
)
select sum(vpl_bruto_sum.total)
    from pet320.verzijaplaca
    left outer join vpl_bruto_sum on verzijaplaca.vpl_vrsteplacila = 9150;

If you want to calculate a value, then use it in multiple different queries, you could use a variable:

create or replace variable my_sum integer;
set my_sum = (select sum(vpl_bruto) from pet320.verzijaplaca);

select sum(case when verzijaplaca.vpl_vrsteplacila = 9150 
               then my_sum else 0 
           end
       )
    from pet320.verzijaplaca;

Hopefully that will help you get started.

It looks like there are probably other problems with your query as well. For example, where 1=1 and... is not a useful construct. It might be worth seeking help about how to design a better query--I think it could be a lot simpler, though it's hard to say without knowing what you are doing.