Re: ERROR: invalid input syntax for integer: ""

On 02/06/2013 01:28 AM, Ben Madin wrote:
> Thanks Tom,
>
> On 2013-02-06, at 13:42 , Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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 are NULL, 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(dot)klaver(at)gmail(dot)com