[postgis-users] ERROR: missing FROM-clause entry for table

[Andy Colson]

On 12/1/2011 7:29 AM, maduako ikechukwu wrote:
> Hi guys,
>   please could someone help me look at this SQL code, I cant figure out
> what is wrong with it and it gives me this error,
>
> "ERROR:  missing FROM-clause entry for table "i"
> LINE 3: Select I.temp_lst_id,I.temp_value as I_t, R.tv.val as R_t, (..."
>                 ^
> Here is the code;
>
> Select I.temp_value as I_t, R.tv.val as R_t, (I_t - R_t) as D_t
>  From ( Select ST_intersection(R.rast,I.the_geom) AS tv
>  From in_situ_lst I, lst_day R
> Where I.the_geom && R.rast
> AND ST_intersects(R.rast,I.the_geom)
> And I.temp_lst_id = 2
> )foo;

There is an outside statement, and an inside.  The outside:
Select I.temp_value as I_t, R.tv.val as R_t, (I_t - R_t) as D_t

is trying to use a table alias from the inside.  You can't do that.

Or said another way, you are using derived tables.  The inside select 
statement is run first, and the resultset is called foo:

Select ST_intersection(R.rast,I.the_geom) AS tv
 From in_situ_lst I, lst_day R
Where I.the_geom && R.rast
AND ST_intersects(R.rast,I.the_geom)
And I.temp_lst_id = 2

The outside then does "select [stuff] from foo".  The outside cannot see 
any table's named I.  It can only see foo.

-Andy