title: SQL Error Converting Data to Int When I am Not Asking It To
body: <p>I have the following SQL statement. In it I need to convert some numbers stored as varchar to decimal in order to sum them. </p>
  
  <p>When I run this SQL against my constraints I get this message:</p>
  
  <blockquote>
    <p>Msg 245, Level 16, State 1, Line 1
    Conversion failed when converting the varchar value '1635.34' to data
    type int.</p>
  </blockquote>
  
  <p>Which baffles me because I am casting my data as decimal. It also baffles me because when I use different constraints that have the same type of data in the field (<code>1234.56</code> type of format) it works. That data is in the <code>TotalPremium</code> field.</p>
  
  <p>(My logic is a bit complex so that is why SQL statement is complex. I am posting all of it for clarity sake. Also, redesigning database table field type is not an option at this point.)</p>
  
  <pre><code>SELECT Account_No, version_num, LineOfBus, ProductNo, QuoteNo, Sum(Cast(TotalPremium as Decimal(16,2))) TotalPremium
      FROM 
      (SELECT t.Account_No, t.version_num, 
          CASE 
              WHEN t.PackageIndicator = '1' THEN 'Package' Else t.Lob 
          END AS LineOfBus,
          t.ProductNo, t.QuoteNo, Cast(COALESCE(t.TotalPremium,0) as decimal(16,2)) TotalPremium 
          FROM uAccountProductInfo as T
          WHERE t.version_num IN
              (SELECT sqVersionNumber.version_num
                  FROM
                  /* this captures unique package product records (or just stand alone records as well) */
                  (SELECT DISTINCT sqUnique.version_num, Count(sqUnique.version_num) VersionCount 
                      FROM
                      /* grab list of all uniquer version, product, quote combinations (use distinct to combine package */
                          (SELECT DISTINCT version_num, productNo, quoteNo
                              FROM uAccountProductInfo
                              WHERE Account_No = '1172014' /* pass as parameter */
                                  AND ProductNo IN ('6472930', '6474927') /* pass as parameter */
                                  AND QuoteNo IN ('724185-01', '881957-08') /* pass as parameter */
                          ) AS sqUnique
                      GROUP BY version_num
                      HAVING Count(version_num) = 2 /* pass as variable based on number of products, quotes */
                  ) as sqVersionNumber
              )
          AND t.Account_no = '1172014' /* pass as parameter */
          AND t.ProductNo IN ('6472930', '6474927') /* pass as parameter */
          AND t.QuoteNo IN ('724185-01', '881957-08') /* pass as parameter */) as sqLOB
      GROUP BY Account_No, version_num, LineOfBus, ProductNo, QuoteNo
  </code></pre>