代码之家 › 专栏 › 技术社区 › Faye D.

和左联接表的和值

inner-join left-join sum tsql sql-server

Faye D. · 技术社区 · 3 年前

我有一个复杂的查询 INNER JOIN 一张桌子和许多 LEFT JOIN 将表添加到主表。其中一个表有多行对应于主表的每一行,我想包括一个 SUM() 在我的 SELECT

不包含在聚合函数或GROUP BY子句中

,所以我开始在 GROUP BY 子句,直到我遇到一个文本字段。。。在这种情况下,我得到的错误是

无法对文本、ntext和图像数据类型进行比较或排序,

那么,在这种情况下,我如何克服这个障碍呢?

此外,今后还有更复杂的案件 选择

下面我将包括整个查询,如果需要,我可以提供一个过于简化的DB fiddle,至少在主表和这个表之间 左连接 一对多的桌子。。。

SELECT sub.SUBSTITUTECODE AS SKU,
       prod.CODE,
       prod.DESCRIPTION AS TITLE,
       prod.REMARKS AS DESCRIPTION, -- <---- This is the text field...
       prod.DESCR2 AS SHORTDESCRIPTION,
       manuf.DESCR AS MANUFACTURER,
       CONCAT(UPPER(LTRIM(RTRIM(cat1.DESCR))), '>', UPPER(LTRIM(RTRIM(cat2.DESCR))), '>', UPPER(LTRIM(RTRIM(cat3.DESCR)))) AS CATEGORIES,
       CONVERT(DECIMAL(10,2), CONVERT(DECIMAL(10,3), CASE WHEN ISNULL(prod.FWHSPRICE, 0) = 0 THEN ISNULL(prod.RTLPRICE, 0) ELSE ISNULL(prod.FWHSPRICE, 0) END)) AS REGULARPRICE,
       CONVERT(DECIMAL(10,2), CONVERT(DECIMAL(10,3), ISNULL(prod.FLDFLOAT3, 0))) AS SALEPRICE,
       CHOOSE(sub.SIZEPOS, szlist.SIZE1, szlist.SIZE2, szlist.SIZE3, szlist.SIZE4, szlist.SIZE5, szlist.SIZE6, szlist.SIZE7, szlist.SIZE8, szlist.SIZE9, szlist.SIZE10, szlist.SIZE11, szlist.SIZE12, szlist.SIZE13, szlist.SIZE14, szlist.SIZE15, szlist.SIZE16, szlist.SIZE17, szlist.SIZE18, szlist.SIZE19, szlist.SIZE20, szlist.SIZE21, szlist.SIZE22, szlist.SIZE23, szlist.SIZE24, szlist.SIZE25) AS SIZE,
       CHOOSE(sub.SIZEPOS, SUM(CASE WHEN ISNULL(qtys.SIZE1, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE1, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE2, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE2, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE3, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE3, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE4, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE4, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE5, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE5, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE6, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE6, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE7, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE7, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE8, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE8, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE9, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE9, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE10, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE10, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE11, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE11, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE12, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE12, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE13, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE13, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE14, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE14, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE15, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE15, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE16, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE16, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE17, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE17, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE18, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE18, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE19, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE19, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE20, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE20, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE21, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE21, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE22, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE22, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE23, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE23, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE24, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE24, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE25, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE25, 0) END)) AS QUANTITIES,
       col.COLORDESCR AS COLOR,
       prod.FLTID3 AS SIZEGUIDE,
       ISNULL(prod.FLDSTRING1, 0) AS SLIDERNEWPRODUCT,
       ISNULL(prod.FLDFLOAT5, 0) AS SLIDERCUSTOM1,
       ISNULL(prod.FLDFLOAT6, 0) AS SLIDERCUSTOM2,
       prod.zWebLISTSEO AS SEOTITLE,
       prod.zWEBDESCRSEO AS SEODESCRIPTION,
       prod.FLDSTRING2 AS SKROUTZWEIGHT
FROM SUBSTITUTE AS sub
INNER JOIN MATERIAL AS prod ON prod.ID = sub.ITEID AND prod.FLTID1 = 1 AND prod.COMID = 12 AND prod.CODE NOT IN ('FX8707 ', '749747-010 ', '7111653 ', 'ZY9021-12W ', 'ZY9021-12R ', '033123-08B ', '30VERMDBLU ', '053003-10 ', 'M20-73354-10 ', '935088-05 ', '935088-02 ', '216603-RS053 ', '194342-01 ', '1E03550 ', '000024555 ', '071010-01 ', '071933-01 ', 'W9-66333-10 ', 'W9-66333-26 ', 'W9-66338-10 ', '573571-01 ', 'H087Y ', 'FV3407 ', 'D012L ', '358038-04 ', '358038-02 ', '114449-KK001 ', 'BV3636-010 ', 'RC5051PT-28633W ', '106312-06 ', 'DC4054-068 ', '370488-11 ', 'H68096 ', 'AT1801-406 ', 'AT1801-600 ', 'CD3199-455 ', '000925701 ', 'G71616 ', 'CZ8646 ', '42051-BLUE ', '42051-ORANGE ', '42051-YELLOW ', '42054-BLUE ', 'BA5901-610 ', 'F9505-300')
LEFT JOIN ITECOLOR AS col ON col.COLORCODE = sub.COLORCODE AND col.ITEID = sub.ITEID
LEFT JOIN MANUFACTURER AS manuf ON manuf.CODEID = prod.MNFID
LEFT JOIN ITEMCATEGORY AS cat1 ON cat1.CODEID = prod.ICTID AND cat1.COMID = 12
LEFT JOIN ITEMGROUP2 AS cat2 ON cat2.CODEID = prod.IGSID AND cat2.COMID = 12
LEFT JOIN ITEMGROUP AS cat3 ON cat3.CODEID = prod.IGPID AND cat3.COMID = 12
LEFT JOIN SIZELIST AS szlist ON szlist.CODEID = prod.MAINSZLID AND szlist.COMID = 12
LEFT JOIN COLORSIZEQTYS AS qtys ON qtys.ITEID = sub.ITEID AND qtys.COLORCODE = sub.COLORCODE AND qtys.QTYMODE = 1 -- <---- This is the one-to-many table
GROUP BY sub.SUBSTITUTECODE, prod.CODE, prod.DESCRIPTION, prod.REMARKS;

2 回复 | 直到 3 年前

Stu 3 年前

您可以从查询中删除分组并使用 outer apply

我不会重复你的整个问题,但要点是

select...
Choose(sub.SIZEPOS, qtys.s1, qtys.s2...) as Quantities
from SUBSTITUTE as sub
...
outer apply (
    SUM(case when IsNull(qtys.SIZE1, 0) < 0 then 0 else IsNull(qtys.SIZE1, 0) end) s1,
    SUM(case when IsNull(qtys.SIZE2, 0) < 0 then 0 else IsNull(qtys.SIZE2, 0) end) s2...
    from COLORSIZEQTYS qtys 
    where qtys.ITEID = sub.ITEID and qtys.COLORCODE = sub.COLORCODE and qtys.QTYMODE = 1
)qtys

Faye D. 3 年前

这是最后一个查询。我发布它只是为了完整性。再次感谢@stu!

SELECT sub.SUBSTITUTECODE AS SKU,
    prod.CODE,
    prod.DESCRIPTION AS TITLE,
    prod.REMARKS AS DESCRIPTION,
    prod.DESCR2 AS SHORTDESCRIPTION,
    manuf.DESCR AS MANUFACTURER,
    CONCAT(UPPER(LTRIM(RTRIM(cat1.DESCR))), '>', UPPER(LTRIM(RTRIM(cat2.DESCR))), '>', UPPER(LTRIM(RTRIM(cat3.DESCR)))) AS CATEGORIES,
    CONVERT(DECIMAL(10,2), CONVERT(DECIMAL(10,3), CASE WHEN ISNULL(prod.FWHSPRICE, 0) = 0 THEN ISNULL(prod.RTLPRICE, 0) ELSE ISNULL(prod.FWHSPRICE, 0) END)) AS REGULARPRICE,
    CONVERT(DECIMAL(10,2), CONVERT(DECIMAL(10,3), ISNULL(prod.FLDFLOAT3, 0))) AS SALEPRICE,
    CHOOSE(sub.SIZEPOS, szlist.SIZE1, szlist.SIZE2, szlist.SIZE3, szlist.SIZE4, szlist.SIZE5, szlist.SIZE6, szlist.SIZE7, szlist.SIZE8, szlist.SIZE9, szlist.SIZE10, szlist.SIZE11, szlist.SIZE12, szlist.SIZE13, szlist.SIZE14, szlist.SIZE15, szlist.SIZE16, szlist.SIZE17, szlist.SIZE18, szlist.SIZE19, szlist.SIZE20, szlist.SIZE21, szlist.SIZE22, szlist.SIZE23, szlist.SIZE24, szlist.SIZE25) AS SIZE,
    CHOOSE(sub.SIZEPOS, qtys.TOTAL1, qtys.TOTAL2, qtys.TOTAL3, qtys.TOTAL4, qtys.TOTAL5, qtys.TOTAL6, qtys.TOTAL7, qtys.TOTAL8, qtys.TOTAL9, qtys.TOTAL10, qtys.TOTAL11, qtys.TOTAL12, qtys.TOTAL13, qtys.TOTAL14, qtys.TOTAL15, qtys.TOTAL16, qtys.TOTAL17, qtys.TOTAL18, qtys.TOTAL19, qtys.TOTAL20, qtys.TOTAL21, qtys.TOTAL22, qtys.TOTAL23, qtys.TOTAL24, qtys.TOTAL25) AS QUANTITY,
    col.COLORDESCR AS COLOR,
    prod.FLTID3 AS SIZEGUIDE,
    ISNULL(prod.FLDSTRING1, 0) AS SLIDERNEWPRODUCT,
    ISNULL(prod.FLDFLOAT5, 0) AS SLIDERCUSTOM1,
    ISNULL(prod.FLDFLOAT6, 0) AS SLIDERCUSTOM2,
    prod.zWebLISTSEO AS SEOTITLE,
    prod.zWEBDESCRSEO AS SEODESCRIPTION,
    prod.FLDSTRING2 AS SKROUTZWEIGHT
FROM SUBSTITUTE AS sub
    INNER JOIN MATERIAL AS prod ON prod.ID = sub.ITEID AND prod.FLTID1 = 1 AND prod.COMID = 12 AND prod.CODE NOT IN ('FX8707 ', '749747-010 ', '7111653 ', 'ZY9021-12W ', 'ZY9021-12R ', '033123-08B ', '30VERMDBLU ', '053003-10 ', 'M20-73354-10 ', '935088-05 ', '935088-02 ', '216603-RS053 ', '194342-01 ', '1E03550 ', '000024555 ', '071010-01 ', '071933-01 ', 'W9-66333-10 ', 'W9-66333-26 ', 'W9-66338-10 ', '573571-01 ', 'H087Y ', 'FV3407 ', 'D012L ', '358038-04 ', '358038-02 ', '114449-KK001 ', 'BV3636-010 ', 'RC5051PT-28633W ', '106312-06 ', 'DC4054-068 ', '370488-11 ', 'H68096 ', 'AT1801-406 ', 'AT1801-600 ', 'CD3199-455 ', '000925701 ', 'G71616 ', 'CZ8646 ', '42051-BLUE ', '42051-ORANGE ', '42051-YELLOW ', '42054-BLUE ', 'BA5901-610 ', 'F9505-300')
    LEFT JOIN ITECOLOR AS col ON col.COLORCODE = sub.COLORCODE AND col.ITEID = sub.ITEID
    LEFT JOIN MANUFACTURER AS manuf ON manuf.CODEID = prod.MNFID
    LEFT JOIN ITEMCATEGORY AS cat1 ON cat1.CODEID = prod.ICTID AND cat1.COMID = 12
    LEFT JOIN ITEMGROUP2 AS cat2 ON cat2.CODEID = prod.IGSID AND cat2.COMID = 12
    LEFT JOIN ITEMGROUP AS cat3 ON cat3.CODEID = prod.IGPID AND cat3.COMID = 12
    LEFT JOIN SIZELIST AS szlist ON szlist.CODEID = prod.MAINSZLID AND szlist.COMID = 12
OUTER APPLY (
SELECT CASE WHEN (ISNULL(stk.QTY1, 0) - ISNULL(rsv.QTY1, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY1, 0) - ISNULL(rsv.QTY1, 0)) END AS TOTAL1, CASE WHEN (ISNULL(stk.QTY2, 0) - ISNULL(rsv.QTY2, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY2, 0) - ISNULL(rsv.QTY2, 0)) END AS TOTAL2, CASE WHEN (ISNULL(stk.QTY3, 0) - ISNULL(rsv.QTY3, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY3, 0) - ISNULL(rsv.QTY3, 0)) END AS TOTAL3, CASE WHEN (ISNULL(stk.QTY4, 0) - ISNULL(rsv.QTY4, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY4, 0) - ISNULL(rsv.QTY4, 0)) END AS TOTAL4, CASE WHEN (ISNULL(stk.QTY5, 0) - ISNULL(rsv.QTY5, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY5, 0) - ISNULL(rsv.QTY5, 0)) END AS TOTAL5, CASE WHEN (ISNULL(stk.QTY6, 0) - ISNULL(rsv.QTY6, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY6, 0) - ISNULL(rsv.QTY6, 0)) END AS TOTAL6, CASE WHEN (ISNULL(stk.QTY7, 0) - ISNULL(rsv.QTY7, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY7, 0) - ISNULL(rsv.QTY7, 0)) END AS TOTAL7, CASE WHEN (ISNULL(stk.QTY8, 0) - ISNULL(rsv.QTY8, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY8, 0) - ISNULL(rsv.QTY8, 0)) END AS TOTAL8, CASE WHEN (ISNULL(stk.QTY9, 0) - ISNULL(rsv.QTY9, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY9, 0) - ISNULL(rsv.QTY9, 0)) END AS TOTAL9, CASE WHEN (ISNULL(stk.QTY10, 0) - ISNULL(rsv.QTY10, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY10, 0) - ISNULL(rsv.QTY10, 0)) END AS TOTAL10, CASE WHEN (ISNULL(stk.QTY11, 0) - ISNULL(rsv.QTY11, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY11, 0) - ISNULL(rsv.QTY11, 0)) END AS TOTAL11, CASE WHEN (ISNULL(stk.QTY12, 0) - ISNULL(rsv.QTY12, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY12, 0) - ISNULL(rsv.QTY12, 0)) END AS TOTAL12, CASE WHEN (ISNULL(stk.QTY13, 0) - ISNULL(rsv.QTY13, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY13, 0) - ISNULL(rsv.QTY13, 0)) END AS TOTAL13, CASE WHEN (ISNULL(stk.QTY14, 0) - ISNULL(rsv.QTY14, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY14, 0) - ISNULL(rsv.QTY14, 0)) END AS TOTAL14, CASE WHEN (ISNULL(stk.QTY15, 0) - ISNULL(rsv.QTY15, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY15, 0) - ISNULL(rsv.QTY15, 0)) END AS TOTAL15, CASE WHEN (ISNULL(stk.QTY16, 0) - ISNULL(rsv.QTY16, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY16, 0) - ISNULL(rsv.QTY16, 0)) END AS TOTAL16, CASE WHEN (ISNULL(stk.QTY17, 0) - ISNULL(rsv.QTY17, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY17, 0) - ISNULL(rsv.QTY17, 0)) END AS TOTAL17, CASE WHEN (ISNULL(stk.QTY18, 0) - ISNULL(rsv.QTY18, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY18, 0) - ISNULL(rsv.QTY18, 0)) END AS TOTAL18, CASE WHEN (ISNULL(stk.QTY19, 0) - ISNULL(rsv.QTY19, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY19, 0) - ISNULL(rsv.QTY19, 0)) END AS TOTAL19, CASE WHEN (ISNULL(stk.QTY20, 0) - ISNULL(rsv.QTY20, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY20, 0) - ISNULL(rsv.QTY20, 0)) END AS TOTAL20, CASE WHEN (ISNULL(stk.QTY21, 0) - ISNULL(rsv.QTY21, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY21, 0) - ISNULL(rsv.QTY21, 0)) END AS TOTAL21, CASE WHEN (ISNULL(stk.QTY22, 0) - ISNULL(rsv.QTY22, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY22, 0) - ISNULL(rsv.QTY22, 0)) END AS TOTAL22, CASE WHEN (ISNULL(stk.QTY23, 0) - ISNULL(rsv.QTY23, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY23, 0) - ISNULL(rsv.QTY23, 0)) END AS TOTAL23, CASE WHEN (ISNULL(stk.QTY24, 0) - ISNULL(rsv.QTY24, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY24, 0) - ISNULL(rsv.QTY24, 0)) END AS TOTAL24, CASE WHEN (ISNULL(stk.QTY25, 0) - ISNULL(rsv.QTY25, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY25, 0) - ISNULL(rsv.QTY25, 0)) END AS TOTAL25
    FROM (SELECT SUM(CASE WHEN ISNULL(SIZE1, 0) < 0 THEN 0 ELSE ISNULL(SIZE1, 0) END) AS QTY1, SUM(CASE WHEN ISNULL(SIZE2, 0) < 0 THEN 0 ELSE ISNULL(SIZE2, 0) END) AS QTY2, SUM(CASE WHEN ISNULL(SIZE3, 0) < 0 THEN 0 ELSE ISNULL(SIZE3, 0) END) AS QTY3, SUM(CASE WHEN ISNULL(SIZE4, 0) < 0 THEN 0 ELSE ISNULL(SIZE4, 0) END) AS QTY4, SUM(CASE WHEN ISNULL(SIZE5, 0) < 0 THEN 0 ELSE ISNULL(SIZE5, 0) END) AS QTY5, SUM(CASE WHEN ISNULL(SIZE6, 0) < 0 THEN 0 ELSE ISNULL(SIZE6, 0) END) AS QTY6, SUM(CASE WHEN ISNULL(SIZE7, 0) < 0 THEN 0 ELSE ISNULL(SIZE7, 0) END) AS QTY7, SUM(CASE WHEN ISNULL(SIZE8, 0) < 0 THEN 0 ELSE ISNULL(SIZE8, 0) END) AS QTY8, SUM(CASE WHEN ISNULL(SIZE9, 0) < 0 THEN 0 ELSE ISNULL(SIZE9, 0) END) AS QTY9, SUM(CASE WHEN ISNULL(SIZE10, 0) < 0 THEN 0 ELSE ISNULL(SIZE10, 0) END) AS QTY10, SUM(CASE WHEN ISNULL(SIZE11, 0) < 0 THEN 0 ELSE ISNULL(SIZE11, 0) END) AS QTY11, SUM(CASE WHEN ISNULL(SIZE12, 0) < 0 THEN 0 ELSE ISNULL(SIZE12, 0) END) AS QTY12, SUM(CASE WHEN ISNULL(SIZE13, 0) < 0 THEN 0 ELSE ISNULL(SIZE13, 0) END) AS QTY13, SUM(CASE WHEN ISNULL(SIZE14, 0) < 0 THEN 0 ELSE ISNULL(SIZE14, 0) END) AS QTY14, SUM(CASE WHEN ISNULL(SIZE15, 0) < 0 THEN 0 ELSE ISNULL(SIZE15, 0) END) AS QTY15, SUM(CASE WHEN ISNULL(SIZE16, 0) < 0 THEN 0 ELSE ISNULL(SIZE16, 0) END) AS QTY16, SUM(CASE WHEN ISNULL(SIZE17, 0) < 0 THEN 0 ELSE ISNULL(SIZE17, 0) END) AS QTY17, SUM(CASE WHEN ISNULL(SIZE18, 0) < 0 THEN 0 ELSE ISNULL(SIZE18, 0) END) AS QTY18, SUM(CASE WHEN ISNULL(SIZE19, 0) < 0 THEN 0 ELSE ISNULL(SIZE19, 0) END) AS QTY19, SUM(CASE WHEN ISNULL(SIZE20, 0) < 0 THEN 0 ELSE ISNULL(SIZE20, 0) END) AS QTY20, SUM(CASE WHEN ISNULL(SIZE21, 0) < 0 THEN 0 ELSE ISNULL(SIZE21, 0) END) AS QTY21, SUM(CASE WHEN ISNULL(SIZE22, 0) < 0 THEN 0 ELSE ISNULL(SIZE22, 0) END) AS QTY22, SUM(CASE WHEN ISNULL(SIZE23, 0) < 0 THEN 0 ELSE ISNULL(SIZE23, 0) END) AS QTY23, SUM(CASE WHEN ISNULL(SIZE24, 0) < 0 THEN 0 ELSE ISNULL(SIZE24, 0) END) AS QTY24, SUM(CASE WHEN ISNULL(SIZE25, 0) < 0 THEN 0 ELSE ISNULL(SIZE25, 0) END) AS QTY25
        FROM COLORSIZEQTYS
        WHERE ITEID = sub.ITEID
            AND COLORCODE = sub.COLORCODE
            AND QTYMODE = 1
        ) AS stk
        CROSS JOIN (SELECT SUM(ISNULL(SIZE1, 0)) AS QTY1, SUM(ISNULL(SIZE2, 0)) AS QTY2, SUM(ISNULL(SIZE3, 0)) AS QTY3, SUM(ISNULL(SIZE4, 0)) AS QTY4, SUM(ISNULL(SIZE5, 0)) AS QTY5, SUM(ISNULL(SIZE6, 0)) AS QTY6, SUM(ISNULL(SIZE7, 0)) AS QTY7, SUM(ISNULL(SIZE8, 0)) AS QTY8, SUM(ISNULL(SIZE9, 0)) AS QTY9, SUM(ISNULL(SIZE10, 0)) AS QTY10, SUM(ISNULL(SIZE11, 0)) AS QTY11, SUM(ISNULL(SIZE12, 0)) AS QTY12, SUM(ISNULL(SIZE13, 0)) AS QTY13, SUM(ISNULL(SIZE14, 0)) AS QTY14, SUM(ISNULL(SIZE15, 0)) AS QTY15, SUM(ISNULL(SIZE16, 0)) AS QTY16, SUM(ISNULL(SIZE17, 0)) AS QTY17, SUM(ISNULL(SIZE18, 0)) AS QTY18, SUM(ISNULL(SIZE19, 0)) AS QTY19, SUM(ISNULL(SIZE20, 0)) AS QTY20, SUM(ISNULL(SIZE21, 0)) AS QTY21, SUM(ISNULL(SIZE22, 0)) AS QTY22, SUM(ISNULL(SIZE23, 0)) AS QTY23, SUM(ISNULL(SIZE24, 0)) AS QTY24, SUM(ISNULL(SIZE25, 0)) AS QTY25
        FROM STORECOLORSIZEEST
        WHERE ITEID = sub.ITEID
            AND COLORCODE = sub.COLORCODE
        ) AS rsv
) AS qtys;