Refined Search in a Grid Problem

Scriptcase 9 General
#1

I have successfully created a GRID application and I used the refined search function successfully. This is my query.

SELECT public.final_data.completed_data_id as completed_data_id, public.final_data.parcel_id as parcel_id,
public.completed_data.collection_date as collection_date,
public.final_data.parcel_group_id, public.parcels.plot_name as plot_name,
avg(public.final_data.ch1_value) as ch1_value_avg, stddev(public.final_data.ch1_value) as ch1_value_std,
avg(public.final_data.ch2_value) as ch2_value_avg, stddev(public.final_data.ch2_value) as ch2_value_std,
avg(public.final_data.ch3_value) as ch3_value_avg, stddev(public.final_data.ch3_value) as ch3_value_std
FROM public.final_data
LEFT JOIN public.parcels ON public.final_data.parcel_id = public.parcels.id
LEFT JOIN public.parcel_group ON public.parcels.parcel_group_id = public.parcel_group.parcel_group_id
LEFT JOIN public.parcel_cover ON public.parcel_cover.parcel_cover_id = public.parcels.parcel_cover_id
GROUP BY public.final_data.parcel_group_id, public.final_data.completed_data_id, public.completed_data.collection_date, public.final_data.parcel_id
ORDER BY public.completed_data.collection_date, public.parcels.plot_name::int ASC

However, we I add a WHERE CLAUSE TO THE QUERY like this:

SELECT public.final_data.completed_data_id as completed_data_id, public.final_data.parcel_id as parcel_id,
public.completed_data.collection_date as collection_date,
public.final_data.parcel_group_id, public.parcels.plot_name as plot_name,
avg(public.final_data.ch1_value) as ch1_value_avg, stddev(public.final_data.ch1_value) as ch1_value_std,
avg(public.final_data.ch2_value) as ch2_value_avg, stddev(public.final_data.ch2_value) as ch2_value_std,
avg(public.final_data.ch3_value) as ch3_value_avg, stddev(public.final_data.ch3_value) as ch3_value_std
FROM public.final_data
LEFT JOIN public.parcels ON public.final_data.parcel_id = public.parcels.id
LEFT JOIN public.parcel_group ON public.parcels.parcel_group_id = public.parcel_group.parcel_group_id
LEFT JOIN public.parcel_cover ON public.parcel_cover.parcel_cover_id = public.parcels.parcel_cover_id
WHERE public.final_data.completed_data_id = [completed_data_id]
GROUP BY public.final_data.parcel_group_id, public.final_data.completed_data_id, public.completed_data.collection_date, public.final_data.parcel_id
ORDER BY public.completed_data.collection_date, public.parcels.plot_name::int ASC

The application fails and I get the following error message.

Error while accessing the database
ERROR: missing FROM-clause entry for table final_data

This query works fine until I add the refined search. Anyone have any ideas?

#2

I ran tyour sql through a debugger and it came up with this last bit as problematic

public.parcels.plot_name::int ASC

take out the :: int ASC and see if it works

#3

Thanks for trying to help me. I removed the ::int ASC and unfortunately the problem persists. Its only when I add the WHERE CLAUSE that the Refined Search does not work.

#4

do you use some lookup to get information from other tables. I remember me that i had the same problem. I solved it by first deleting the lookup and building back up one by one.

#5

No I do not use any lookups. Thanks for trying to help me.

#6

can you send a screenshot of the grid?