1

I have two tables in my database. One has shipment data with zip code location and the other table has a complete zip code listing of all zips in the US. I just included the relevant columns below to keep it simple.

Table: ZipCodes

  • ZipCode PK char(5)

Table: Shipments

  • ShipToZip varchar(10)
  • AMT decimal(10,2)

So some of the shipment data may be to non-US addresses, and others may include the +4 for US zip codes (XXXXX-XXXX) which is why the shipment table has up to 10. All I need to do is export the query as a CSV, however I need the zip codes converted to integer.

SELECT CAST(ShipToZIP AS INT) AS [target],SUM(cast(AMT as int)) AS [total]
FROM
    (SELECT LEFT(ShiptoZip,5) ShipToZip,Amt
        FROM Shipments
        WHERE LEFT(SHIPTOZIP,5) IN (SELECT zipcode from ZipCodeLocations)
    ) Q
GROUP BY CAST(ShipToZIP AS INT)

When I run that query however, I keep getting conversion errors for the Canadian zip codes. If I run the inner Q query by itself, every single record is a valid US only code.

How can I force the subquery to run first? I tried making it a CTE per this post however it didn't work either.

Community
  • 1
  • 1
JohnB
  • 1,743
  • 5
  • 21
  • 40
  • Treating zip codes as integers is a problem not just for Canadian codes but also US zip codes that have a leading 0. You need to take a really hard look at that requirement because it's so problematic. E.g. spe u all case for converting leading-zero zip codes from int to 5 char string... Why treat a zip code as an int? You can't do math with it. – Paul Sasik Dec 06 '14 at 17:27
  • Well I needed it work with some data between files and Excel, annoyingly, always removes the leading 0 no matter what I do when I save as CSV. So I was trying to save an extra step by just outputting it as integer. – JohnB Dec 06 '14 at 17:55

1 Answers1

1

The problem is the execution plan, and I'm not sure you can force the subquery to run 1st.

You could do this and prevent having to CAST to an INT.

SELECT CASE ShiptoZip WHEN LEFT(ShiptoZip) = '0' THEN RIGHT(ShiptoZip, LEN(ShiptoZip) - 1) ELSE ShiptoZip END AS [target],
SUM(cast(AMT as int)) AS [total]
FROM
  (SELECT LEFT(ShiptoZip,5) ShipToZip,Amt
    FROM Shipments
    WHERE LEFT(SHIPTOZIP,5) IN (SELECT zipcode from ZipCodeLocations)
  ) Q
GROUP BY CASE ShiptoZip WHEN LEFT(ShiptoZip) = '0' THEN RIGHT(ShiptoZip, LEN(ShiptoZip) - 1) ELSE ShiptoZip END

Your other alternative is to CAST to an INT after your aggregation if you must go that route.

SELECT CAST(ShipToZIP AS INT) AS [target],[total]
FROM
(SELECT LEFT(ShiptoZip,5) ShipToZip, SUM(cast(AMT as int)) AS [total]
    FROM Shipments
    WHERE LEFT(SHIPTOZIP,5) IN (SELECT zipcode from ZipCodeLocations)
    GROUP BY ShipToZIP
) Q
Ashley Lee
  • 3,810
  • 1
  • 18
  • 26