Re: ERROR: invalid input syntax for integer: "" - Mailing list pgsql-general

Adrian,

On 2013-02-06, at 13:33 , Adrian Klaver <adrian.klaver@gmail.com> wrote:

> Dim bulb moment.
>=20
> What happens if you run a simplified version of the query?
>=20
> One that just LEFT JOINS reports to results ON reportid=3Drep.id.


A fair question - it only makes it more confusing :

{{{
SELECT rep.id, res8.reportid
FROM reports rep=20
LEFT JOIN results res8 ON res8.reportid =3D rep.id AND res8.resulttypeid =
=3D 108 AND res8.del =3D false
LEFT JOIN resultlookup rlu8 ON rlu8.resulttypesid =3D 108 AND rlu8.id =3D =
res8.resultvalue::int
WHERE rep.del IS false=20
AND rep.projectid =3D 51=20
AND rlu8.id =3D '935';
}}}

works perfectly well - so does:

{{{
SELECT rep.id,  =
round(st_distance_sphere('0101000020BB1000008716D9CEF7A36240643BDF4F8DA741=
C0', post.the_point::geometry)/1000) as dist=20
FROM reports rep=20
LEFT JOIN users u ON rep.link =3D u.id=20
LEFT JOIN postcodes post ON u.postcode::integer =3D post.postcode =20
LEFT JOIN species spe ON rep.species::text like spe.speccode::text AND =
spe.synonym =3D 0 =20
WHERE rep.del IS false=20
AND rep.projectid =3D 51=20
AND round(st_distance_sphere( =
'0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0', =
post.the_point)/1000) < '150'=20
AND spe.id =3D '9465';
}}}

but the combination only works on the older db=85

{{{
SELECT rep.id, res.reportid,=20
=
round(st_distance_sphere('0101000020BB1000008716D9CEF7A36240643BDF4F8DA741=
C0', post.the_point::geometry)/1000) as dist=20
FROM reports rep=20
LEFT JOIN users u ON rep.link =3D u.id=20
LEFT JOIN postcodes post ON u.postcode::integer =3D post.postcode =20
LEFT JOIN species spe ON rep.species::text like spe.speccode::text AND =
spe.synonym =3D 0 =20
LEFT JOIN results res ON res.reportid =3D rep.id AND res.resulttypeid =3D =
108 AND res.del =3D false
LEFT JOIN resultlookup rlu ON rlu.resulttypesid =3D 108 AND rlu.id =3D =
res.resultvalue::int
WHERE rep.del IS false=20
AND rep.projectid =3D 51=20
AND round(st_distance_sphere( =
'0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0', =
post.the_point)/1000) < '150'=20
AND spe.id =3D '9465'=20
AND rlu.id =3D '935';
ERROR:  invalid input syntax for integer: ""
}}}

cheers

Ben


--=20

Ben Madin

t : +61 8 6102 5535
m : +61 448 887 220
e : ben@ausvet.com.au

AusVet Animal Health Services
P.O. Box 5467
Broome   WA   6725
Australia

AusVet's website:  http://www.ausvet.com.au

This transmission is for the intended addressee only and is confidential =
information. If you have received this transmission in error, please =
delete it and notify the sender. The contents of this email are the =
opinion of the writer only and are not endorsed by AusVet Animal Health =
Services unless expressly stated otherwise. Although AusVet uses virus =
scanning software we do not accept liability for viruses or similar in =
any attachments. Thanks for reading.