Qry HELP

Transact-SQL
1

I am new at this. I have a query that when I run it.. works great i get the results i wanted but when I expect no records.. I get a Null result..
SELECT
SUM(AD_PRICE) AS PRICES
FROM
QUOTE Q
JOIN QUOTE_ITEM QI on Q.QUOTE_ID = QI.QUOTE_ID AND Q.QUOTE_VERS = QI.QUOTE_VERS_STOP
JOIN
(
SELECT QI.QUOTE_ID, QI.QTE_POS, sum(QI.NETT_SELL_PRICE) AS AD_PRICE
FROM
QUOTE Q join QUOTE_ITEM QI on Q.QUOTE_ID = QI.QUOTE_ID AND Q.QUOTE_VERS = QI.QUOTE_VERS_STOP
WHERE
QI.QTE_POS = QI.QTE_POS
QI.QUOTE_ID = Q.QUOTE_ID

        GROUP BY  QI.QUOTE_ID, QI.QTE_POS
    )sub ON
                  Q.QUOTE_ID = SUB.QUOTE_ID
                  AND QI.QTE_POS = SUB.QTE_POS
WHERE
     Q.QUOTE_NUM = 181305 and Q.QUOTE_NUM_SUFF = '' and QUOTE_NUM_PREF = 'AGW'
     AND QUOTE_VERS = (select max(QQ.QUOTE_VERS) from QUOTE QQ where Q.QUOTE_ID = QQ.QUOTE_ID)
    AND QI.ITEM_TYPE = 3
2

This is an obvious problem:

1 Like
3

I suspect you want this:

SELECT SUM(AD_PRICE) AS PRICES
FROM QUOTE Q
    JOIN QUOTE_ITEM QI
        ON Q.QUOTE_ID = QI.QUOTE_ID
           AND Q.QUOTE_VERS = QI.QUOTE_VERS_STOP
    JOIN
    (
        SELECT QI.QUOTE_ID,
               QI.QTE_POS,
               SUM(QI.NETT_SELL_PRICE) AS AD_PRICE
        FROM QUOTE Q
            JOIN QUOTE_ITEM QI
                ON Q.QUOTE_ID = QI.QUOTE_ID
                   AND Q.QUOTE_VERS = QI.QUOTE_VERS_STOP
        WHERE QI.QTE_POS = Q.QTE_POS
              AND QI.QUOTE_ID = Q.QUOTE_ID
        GROUP BY QI.QUOTE_ID,
                 QI.QTE_POS
    ) sub
        ON Q.QUOTE_ID = sub.QUOTE_ID
           AND QI.QTE_POS = sub.QTE_POS
WHERE Q.QUOTE_NUM = 181305
      AND Q.QUOTE_NUM_SUFF = ''
      AND QUOTE_NUM_PREF = 'AGW'
      AND QUOTE_VERS =
      (
          SELECT MAX(QQ.QUOTE_VERS) FROM QUOTE QQ WHERE Q.QUOTE_ID = QQ.QUOTE_ID
      )
      AND QI.ITEM_TYPE = 3;
4

Thanks I am going to try this.