On 02/06/2013 01:28 AM, Ben Madin wrote:
> Thanks Tom,
>
> On 2013-02-06, at 13:42 , Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> The only part of this query that looks like it could possibly produce
>> that error is the res8.resultvalue-to-int cast:
>
>>> LEFT JOIN results res8 ON res8.reportid = rep.id AND res8.resulttypeid = 108 AND res8.del = false
>>> LEFT JOIN resultlookup rlu8 ON rlu8.resulttypesid = 108 AND rlu8.id = res8.resultvalue::int
>> ^^^^^^^^^^^^^^^^^^^^^
>> Presumably, there are some empty strings in results.resultvalue, and if
>> the query happens to try to compare one of them to rlu8.id, kaboom.
>
>
> Yes - this would be the case if it tried to match it against the resultvalue only - some of the values in the table
areNULL, but not for this resulttypeid.
>
> I'm really not sure what to do here.
When I run into issues like this I start over from scratch and build the
query up a layer at a time using the minimum information necessary. Once
I get the 'simple' case working then I start adding in more information.
>
> cheers
>
> Ben
>
>
>
>
--
Adrian Klaver
adrian.klaver@gmail.com