On 02/06/2013 01:28 AM, Ben Madin wrote:
Thanks Tom,
On 2013-02-06, at 13:42 , Tom Lane <tgl@xxxxxxxxxxxxx> 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.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
