Производство в Супермаге, давайте делиться опытом : Супермаг Плюс (Супермаг 2000)

SELECT   tr, trn, art, artn, (SUM (pr1) + SUM (pr2) - SUM (rh1) - SUM (rh2)), max (mb)
    FROM (
          SELECT ca1.tree tr, ca1.NAME trn, c.article art, c.NAME artn,
                 0 pr1, 0 pr2, 0 rh1, 0 rh2, c.MESABBREV mb
            FROM supermag.smcard c,
                 supermag.sacardclass ca1,
                 supermag.sacardclass ca2
           WHERE ca1.tree LIKE :smrgroup
             AND c.idclass = ca1.ID
             AND ca2.tree = SUBSTR (ca1.tree, 1, INSTR (ca1.tree, '.'))
          UNION ALL
          SELECT   ca1.tree tr, ca1.NAME trn, c1.article art, c1.NAME artn,
                   SUM (psp.altquantity) pr1, 0 pr2, 0 rh1, 0 rh2, '' mb
              FROM supermag.smprodexpspec psp,
                   supermag.smdocuments d,
                   supermag.smcard c1,
                   supermag.sacardclass ca1,
                   supermag.sacardclass ca2
             WHERE psp.docid = d.ID
               AND psp.doctype = d.doctype
               AND d.doctype = 'PE'
               AND d.createdat <= :smrdatestart
               AND d.docstate = 3
               AND d.locationfrom = :smrstoreloc
               AND c1.article = psp.artingredient
               AND c1.idclass = ca1.ID
               AND ca1.tree LIKE :smrgroup
               AND ca2.tree = SUBSTR (ca1.tree, 1, INSTR (ca1.tree, '.'))
          GROUP BY ca1.tree, ca1.NAME, c1.article, c1.NAME
          UNION ALL
          SELECT   ca1.tree, ca1.NAME, c.article, c.NAME, 0,
                   SUM (sp.quantity), 0, 0, ''
              FROM supermag.smdocuments d,
                   supermag.smspec sp,
                   supermag.smcard c,
                   supermag.sacardclass ca1,
                   supermag.sacardclass ca2
             WHERE d.ID = sp.docid
               AND d.doctype = sp.doctype
               AND d.docstate > 1
               AND d.doctype = 'PD'
               AND d.createdat <= :smrdatestart
               AND d.LOCATION = :smrstoreloc
               AND sp.article = c.article
               AND c.idclass = ca1.ID
               AND ca1.tree LIKE :smrgroup
               AND ca2.tree = SUBSTR (ca1.tree, 1, INSTR (ca1.tree, '.'))
          GROUP BY ca1.tree, ca1.NAME, c.article, c.NAME
          UNION ALL
          SELECT   ca1.tree, ca1.NAME, c.article, c.NAME, 0, 0,
                   SUM (sp.quantity), 0, ''
              FROM supermag.smdocuments d,
                   supermag.smspec sp,
                   supermag.smcard c,
                   supermag.sacardclass ca1,
                   supermag.sacardclass ca2
             WHERE d.ID = sp.docid
               AND d.doctype = sp.doctype
               AND d.docstate = 3
               AND (d.doctype = 'PN' OR d.doctype = 'PO')
               AND d.createdat <= :smrdatestart
               AND d.locationto = :smrstoreloc
               AND sp.article = c.article
               AND c.idclass = ca1.ID
               AND ca1.tree LIKE :smrgroup
               AND ca2.tree = SUBSTR (ca1.tree, 1, INSTR (ca1.tree, '.'))
          GROUP BY ca1.tree, ca1.NAME, c.article, c.NAME
          UNION ALL
          SELECT   ca1.tree, ca1.NAME, c.article, c.NAME, 0, 0, 0,
                   SUM (psp.quantity), ''
              FROM supermag.smdocuments d,
                   supermag.smprodactspecin psp,
                   supermag.smcard c,
                   supermag.sacardclass ca1,
                   supermag.sacardclass ca2
             WHERE d.ID = psp.docid
               AND d.doctype = psp.doctype
               AND d.docstate > 1
               AND d.doctype = 'PD'
               AND d.createdat <= :smrdatestart
               AND d.LOCATION = :smrstoreloc
               AND psp.article = c.article
               AND c.idclass = ca1.ID
               AND ca1.tree LIKE :smrgroup
               AND ca2.tree = SUBSTR (ca1.tree, 1, INSTR (ca1.tree, '.'))
          GROUP BY ca1.tree, ca1.NAME, c.article, c.NAME)
GROUP BY tr, trn, art, artn