Adrian,
On 2013-02-06, at 13:33 , Adrian Klaver <adrian.klaver@xxxxxxxxx> wrote:
> Dim bulb moment.
>
> What happens if you run a simplified version of the query?
>
> One that just LEFT JOINS reports to results ON reportid=rep.id.
A fair question - it only makes it more confusing :
{{{
SELECT rep.id, res8.reportid
FROM reports rep
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
WHERE rep.del IS false
AND rep.projectid = 51
AND rlu8.id = '935';
}}}
works perfectly well - so does:
{{{
SELECT rep.id, round(st_distance_sphere('0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0', post.the_point::geometry)/1000) as dist
FROM reports rep
LEFT JOIN users u ON rep.link = u.id
LEFT JOIN postcodes post ON u.postcode::integer = post.postcode
LEFT JOIN species spe ON rep.species::text like spe.speccode::text AND spe.synonym = 0
WHERE rep.del IS false
AND rep.projectid = 51
AND round(st_distance_sphere( '0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0', post.the_point)/1000) < '150'
AND spe.id = '9465';
}}}
but the combination only works on the older db…
{{{
SELECT rep.id, res.reportid,
round(st_distance_sphere('0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0', post.the_point::geometry)/1000) as dist
FROM reports rep
LEFT JOIN users u ON rep.link = u.id
LEFT JOIN postcodes post ON u.postcode::integer = post.postcode
LEFT JOIN species spe ON rep.species::text like spe.speccode::text AND spe.synonym = 0
LEFT JOIN results res ON res.reportid = rep.id AND res.resulttypeid = 108 AND res.del = false
LEFT JOIN resultlookup rlu ON rlu.resulttypesid = 108 AND rlu.id = res.resultvalue::int
WHERE rep.del IS false
AND rep.projectid = 51
AND round(st_distance_sphere( '0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0', post.the_point)/1000) < '150'
AND spe.id = '9465'
AND rlu.id = '935';
ERROR: invalid input syntax for integer: ""
}}}
cheers
Ben
--
Ben Madin
t : +61 8 6102 5535
m : +61 448 887 220
e : ben@xxxxxxxxxxxxx
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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
