How do I join with a column I modified in the SELECT statement?-CodePudding

I'm trying to join a table with zip codes and population sizes with a table of demographic information for clients. The issue is that the zip code column in the demographic table is not formatted in a number so I had to reformat it. It was originally formatted as 1111-_ and I reformatted it to be 1111. My issue is that I can't figure out how to refer to the modified column in the join statement.

Here is what I have:

SELECT R.ID, R.program, R.Status, R.Street, R.City, 
dbo.fnGetNumericOnly(R.PostalCode) as Zip, 
R.County, R.Name, Z.[Total Population]
FROM RegistrationInfo AS R
LEFT JOIN ZipCensus AS Z
ON R.Zip=Z.Zip; --R.Zip is not recognized

The R.Zip is not recognized because the column was 'created' in the SELECT statement. I could really use any help as I am relatively new to SQL and have scoured the internet for answers without luck.

CodePudding user response：

Another option (just for fun) is to use a CROSS APPLY to get the derived value ZIP

One should note: Scalar-Valued Functions can be a performance drain

SELECT R.ID
     , R.program
     , R.Status
     , R.Street
     , R.City
     , C.Zip
     , R.County
     , R.Name
     , Z.[Total Population]
FROM RegistrationInfo AS R
Cross Apply ( values ( dbo.fnGetNumericOnly(R.PostalCode) )  ) C(Zip)
LEFT JOIN ZipCensus AS Z
ON C.Zip=Z.Zip;

CodePudding user response：

The way that I learned, is to wrap the modification inside a subquery, then query that subquery just like it was a table.

This is your modification logic

SELECT R.ID, R.program, R.Status, R.Street, R.City, 
dbo.fnGetNumericOnly(R.PostalCode) as Zip, 
R.County, R.Name
FROM RegistrationInfo AS R

Now define that as a subquery

SELECT *
FROM
(
SELECT R.ID, R.program, R.Status, R.Street, R.City, 
dbo.fnGetNumericOnly(R.PostalCode) as Zip, 
R.County, R.Name
FROM RegistrationInfo AS R
) AS subquery

Then you can join it using your new zip column, like this:

SELECT subquery.*, Z.[Total Population]
FROM
(
  SELECT R.ID, R.program, R.Status, R.Street, R.City, 
  dbo.fnGetNumericOnly(R.PostalCode) as Zip, 
  R.County, R.Name
  FROM RegistrationInfo AS R
) AS subquery
LEFT JOIN ZipCensus AS Z
ON subquery.Zip = Z.Zip

There is another way, which I learned later in my career, called Common Table Expressions (CTEs). I find these much more readable personally, but everyone is different.

WITH subquery AS (
  SELECT R.ID, R.program, R.Status, R.Street, R.City, 
  dbo.fnGetNumericOnly(R.PostalCode) as Zip, 
  R.County, R.Name
  FROM RegistrationInfo AS R
)
SELECT S.*, Z.[Total Population]
FROM subquery AS S
LEFT JOIN ZipCensus AS Z
ON S.Zip = Z.Zip

CodePudding user response：

R.zip doesn't exist, so you need to repeat the call to te function, if you need the value

SELECT R.ID, R.program, R.Status, R.Street, R.City, 
dbo.fnGetNumericOnly(R.PostalCode) as Zip, 
R.County, R.Name, Z.[Total Population]
FROM RegistrationInfo AS R
LEFT JOIN ZipCensus AS Z
ON dbo.fnGetNumericOnly(R.PostalCode)=Z.Zip;