title: Join Alias Columns SQL
body: <p>I am trying struggling with joining alias named columns. Overall, I want an output with the with date, hour, and the actual and forecasted (most recent before 10am on the previous day) windspeeds.</p>
  
  <p>With the below code I get:   </p>
  
  <blockquote>
    <p>ERROR:  column "date" does not exist<br>
    LINE xx: ...ast_prep.lat AND meso.lon = forecast_prep.lon AND Date ...</p>
  </blockquote>
  
  <p>I cannot figure out how to get SQL to join these named columns.<br>
  Thanks. And yes, I am a SQL newbie.</p>
  
  <pre><code>with forecast_prep as (
    SELECT
      date_trunc('day', foretime)::date AS Foredate,
      extract(hour from foretime)+1 AS foreHE, 
      lat,
      lon,
      windspeed,
      max(as_of) AS as_of
    FROM weather.forecast
    WHERE date_trunc('day', foretime)::date-as_of&gt;= interval '16 hours'
    GROUP BY Foredate, foreHE, lat, lon, windspeed)
  SELECT
    meso.station,
    date_trunc('day', meso.timestmp)::date AS Date,
    extract(hour from meso.timestmp)+1 AS HE, 
    CAST(AVG(meso.windspd) as numeric(19,2)) As Actual,
    forecast_prep.windspeed, 
    forecast_prep.as_of
  FROM weather.meso
    INNER JOIN forecast_prep ON (
      meso.lat = forecast_prep.lat AND
      meso.lon = forecast_prep.lon AND
      Date = Foredate AND ----&lt;&lt;&lt;&lt; Error here
      HE = foreHE)
  WHERE
    (meso.timestmp Between '2016-02-01' And '2016-02-02') AND
    (meso.station='KSBN')
  GROUP BY meso.station, Date, HE, forecast_prep.windspeed, forecast_prep.as_of
  ORDER BY Date, HE ASC
  </code></pre>
  
  <p>Here are the table structures:  </p>
  
  <pre><code>-- Table: weather.forecast
  
  -- DROP TABLE weather.forecast;
  
  CREATE TABLE weather.forecast
  (
    foretime timestamp without time zone NOT NULL,
    as_of timestamp without time zone NOT NULL, -- in UTC
    summary text,
    precipintensity numeric(8,4),
    precipprob numeric(2,2),
    temperature numeric(5,2),
    apptemp numeric(5,2),
    dewpoint numeric(5,2),
    humidity numeric(2,2),
    windspeed numeric(5,2),
    windbearing numeric(4,1),
    visibility numeric(5,2),
    cloudcover numeric(4,2),
    pressure numeric(6,2),
    ozone numeric(5,2),
    preciptype text,
    lat numeric(8,6) NOT NULL,
    lon numeric(9,6) NOT NULL,
    CONSTRAINT forecast_pkey PRIMARY KEY (foretime, as_of, lat, lon)
  
  
  -- Table: weather.meso
  
  -- DROP TABLE weather.meso;
  
  CREATE TABLE weather.meso
  (
    timestmp timestamp without time zone NOT NULL,
    station text NOT NULL,
    lat numeric NOT NULL,
    lon numeric NOT NULL,
    tmp numeric,
    hum numeric,
    windspd numeric,
    winddir integer,
    dew numeric,
    CONSTRAINT meso_pkey PRIMARY KEY (timestmp, station, lat, lon)
  </code></pre>