Conversion failed with UNION

Question

I'm using SQL Server Management Studio in order to build a query that reports issues.

Alone these queries work fine but as soon as they are added via using a union I end up with the following error message:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'OFC' to data type smallint

I've attempted to use Cast and Convert on the w.wpage and p.ppage columns. I've attempted to use them directly on the case statements for both sub-queries which varying results but always a:

conversion failed when converting...

error.

Any help or advice would be greatly appreciated.

SELECT 
    a.Article,
    a.ARTICLENAME,
    a.Offer,
    a.EBD,
    q.Excl,
    a.OfferYear,
    b.SeasonID,
    CASE 
       WHEN CONCAT(catid,packnum) <> CONCAT(a.offer, a.article) 
          THEN 'Add' 
       WHEN a.EBD = 'B' AND q.Excl = 'BE' 
          THEN 'True' 
       ELSE 'Exclusive'
    END AS Action,
    CASE 
       WHEN CONCAT(a.offer, a.article) = CONCAT(d.ToCat, d.article) 
          THEN 'Y' 
          ELSE 'N'
    END AS [On CN]
FROM 
    Lago_CID_Master a 
INNER JOIN 
    CatalogInfo b ON (a.Offer = b.Catalog) AND (a.OfferYear = b.MailYear)
LEFT JOIN 
    ChangeNoticeCopyFile d ON (a.ARTICLE = d.article) 
                           AND (a.Offer = d.ToCat) AND (a.OfferYear = d.ToYear)
LEFT JOIN
    (SELECT 
         c.Packnum, c.CatID, c.Excl
     FROM 
         PIC704Current c 
     INNER JOIN 
         cataloginfo d ON (c.CatID = d.catalog) AND (c.Year = d.MailYear)
     WHERE 
         d.MailYear = '2020' 
         AND d.SeasonID = 'F20' 
         AND d.MediaId IN ('CAT Catalog', 'SCAT Sale Catalog', 'SSTF Sale Statement Stuff', 'STUF Statement Stuffer', 'PKG Package Insert', 'SPKG Sale Pkg Insert') 
         AND d.Brand = 'Seventh Avenue') q ON CONCAT(q.catid,q.packnum) = CONCAT(a.offer, a.article)
WHERE 
    q.CatID IS NULL
    AND (CASE WHEN Retail < '1' THEN 'Yes' ELSE 'No' END) = 'No'
    AND b.MailYear = '2020' 
    AND b.SeasonID = 'F20' 
    AND b.MediaId = 'Cat Catalog' 
    AND b.Brand = 'Seventh Avenue'

--Pages
UNION

SELECT DISTINCT 
    a.PackNum, a.Description, a.CatID,
    c.SeasonID, a.Page as MFPage, w.WPage, p.Ppage,
    CASE 
       WHEN a.page <> w.wpage 
          THEN 'Update Page' 
          ELSE '' 
    END AS action,
    CASE 
       WHEN CONCAT(a.CatID, a.PackNum) = CONCAT(d.offer, d.article) 
          THEN 'Y' 
          ELSE 'N'
    END AS [On CN]
FROM 
    pic704current a 
INNER JOIN 
    Lago_CID_Master b ON (a.CatID = b.Offer)  
                      AND (a.year = b.OfferYear) AND (a.PackNum = b.ARTICLE)
JOIN 
    CatalogInfo c ON (a.catid = c.catalog) AND (a.year = c.mailyear)
FULL OUTER JOIN 
    ChangeNoticeChanges d ON (b.ARTICLE = d.article) 
                          AND (a.CatID = d.offer) AND (a.Year = d.offeryear)
LEFT JOIN
    --Working Page
    (SELECT 
         e.packnum, e.catid,
         CASE 
            WHEN CAST(f.wpfrom AS int) >= 7000 THEN 'EOC'
            WHEN CAST(f.wpfrom AS int) BETWEEN 700 AND 800 
                 AND f.spreadname LIKE '%CHECK%' THEN 'CHK'
            WHEN f.spreadname LIKE '%ORDER FORM%' 
                 OR f.spreadname LIKE '%CTOB%' THEN 'COF'
            WHEN f.spreadname LIKE '%OFC%' THEN 'OFC'
            WHEN f.spreadname LIKE '%EXOBC%' THEN 'WR' +  
                 CASE WHEN SUBSTRING(f.spreadname, CHARINDEX(e.catid, f.spreadname) + 2, 1) = ' ' 
                           OR SUBSTRING(f.spreadname, CHARINDEX(f.offer, f.spreadname) + 2, 1) = '' THEN '1' 
                           ELSE SUBSTRING(f.spreadname, CHARINDEX(f.offer, f.spreadname) + 2, 1) 
                 END
            WHEN f.spreadname LIKE '%IBC%' THEN 'IB' + 
                 CASE WHEN substring(f.spreadname,charindex(f.offer, f.spreadname)+2,1) = ' ' or substring(f.spreadname,charindex(f.offer, f.spreadname)+2,1) = '' then '1' 
ELSE substring(spreadname,charindex(f.offer, f.spreadname)+2,1) End
WHEN Cast(f.wpfrom as int) = 2 then 'IF' + case when charindex(f.offer, f.spreadname) = 0  then '1' when substring(f.spreadname,charindex(f.offer, f.spreadname)+2,1) = '' then '1' 
ELSE substring(f.spreadname,charindex(f.offer, f.spreadname)+2,1) End   
WHEN f.spreadname like '%OBC%' then 'OB' +  case when substring(f.spreadname,charindex(f.offer, f.spreadname)+2,1) = ' ' or substring(f.spreadname,charindex(f.offer, f.spreadname)+2,1) = '' then '1' 
ELSE substring(f.spreadname,charindex(f.offer, f.spreadname)+2,1) End
ELSE right('00' + f.wpfrom,3) END AS WPage

FROM pic704current e INNER JOIN Lago_CID_Master f ON (e.CatID = f.Offer) and (e.year = f.OfferYear) and (e.PackNum = f.ARTICLE)
JOIN CatalogInfo g on (e.catid = g.catalog) and (e.year = g.mailyear)

WHERE
e.year = '2020'
and g.SeasonID = 'F20' 
and g.brand = 'Seventh Avenue'
and g.MediaId in('CAT Catalog','SCAT Sale Catalog','SSTF Sale Statement Stuff','STUF Statement Stuffer','PKG Package Insert','SPKG Sale Pkg Insert')) w
ON CONCAT(a.catid,a.packnum) = CONCAT(w.CatID, w.PackNum)

LEFT JOIN

--Paginated Page
(SELECT I.packnum, I.catid
,CASE WHEN Cast(J.PPFROM as int) >=7000 then 'EOC'
WHEN Cast(J.PPFROM as int) between 700 and 800 and J.spreadname like '%CHECK%' then 'CHK'
WHEN J.spreadname like '%ORDER FORM%'  or J.spreadname like '%CTOB%' then 'COF'
WHEN J.spreadname like '%OFC%' then 'OFC'
WHEN J.spreadname like '%EXOBC%' then 'WR' +  case when substring(J.spreadname,charindex(I.catid, J.spreadname)+2,1) = ' ' or substring(J.spreadname,charindex(J.offer, J.spreadname)+2,1) = '' then '1' 
ELSE substring(J.spreadname,charindex(J.offer, J.spreadname)+2,1) End
WHEN J.spreadname like '%IBC%' then 'IB' + case when substring(J.spreadname,charindex(J.offer, J.spreadname)+2,1) = ' ' or substring(J.spreadname,charindex(J.offer, J.spreadname)+2,1) = '' then '1' 
ELSE substring(spreadname,charindex(J.offer, J.spreadname)+2,1) End
WHEN Cast(J.PPFROM as int) = 2 then 'IF' + case when charindex(J.offer, J.spreadname) = 0  then '1' when substring(J.spreadname,charindex(J.offer, J.spreadname)+2,1) = '' then '1' 
ELSE substring(J.spreadname,charindex(J.offer, J.spreadname)+2,1) End   
WHEN J.spreadname like '%OBC%' then 'OB' +  case when substring(J.spreadname,charindex(J.offer, J.spreadname)+2,1) = ' ' or substring(J.spreadname,charindex(J.offer, J.spreadname)+2,1) = '' then '1' 
ELSE substring(J.spreadname,charindex(J.offer, J.spreadname)+2,1) End
ELSE right('00' + J.PPFROM,3) END AS PPage

FROM pic704current I INNER JOIN Lago_CID_Master J ON (I.CatID = J.Offer) and (I.year = J.OfferYear) and (I.PackNum = J.ARTICLE)
JOIN CatalogInfo K on (I.catid = K.catalog) and (I.year = K.mailyear)

WHERE
I.year = '2020' 
and K.SeasonID = 'F20' 
and K.brand = 'Seventh Avenue'
and K.MediaId in('CAT Catalog','SCAT Sale Catalog','SSTF Sale Statement Stuff','STUF Statement Stuffer','PKG Package Insert','SPKG Sale Pkg Insert'))P
ON CONCAT(a.catid,a.packnum) = CONCAT(p.CatID, p.PackNum)

WHERE
(CASE WHEN wpage <> a.page and ppage = '000' then 'true' else 'false' end) = 'true'
and
(CASE WHEN ppage > '0' and ppage <> a.page then 'true' else 'false' end) = 'true'
and a.year = '2020' 
and c.SeasonID = 'F20' 
and c.brand = 'Seventh Avenue'
and c.MediaId in('CAT Catalog','SCAT Sale Catalog','SSTF Sale Statement Stuff','STUF Statement Stuffer','PKG Package Insert','SPKG Sale Pkg Insert')

ORDER BY Action

Answer 1

Since your problem starts by adding the union operator it seems that the datatypes in your columns do not match up. The string 'OFC' cannot be converted into an integer so that will always fail. Looking at the descriptions of your columns i would hazard a guess that PackNum is an integer and Article is a string.

To find the offending column what you could do is run the query's independently with an 'INTO #temp' statement just before the FROM part. And then check the data type of the the two temporary tables you have created. I bet that you will find an int in one and a string in the other.

Then according to your requirements ammend the query or cast the integer column as a varchar.