<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:"Comic Sans MS";
panose-1:3 15 7 2 3 3 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Comic Sans MS","serif";
color:windowtext;
font-weight:normal;
font-style:normal;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Comic Sans MS","serif"">Ahh, fixed it. Mapserver doesn’t like the ‘ * ‘ in the SQL , at least not on the end . . . hmm, I wonder if it needs (should be) to be escaped in a subquery?? I took that
out and added in a couple of key columns I needed . . .<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Comic Sans MS","serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Comic Sans MS","serif"">This works now:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Comic Sans MS","serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New","serif";color:black">DATA "the_geom from (<br>
<br>
select<br>
distinct on (</span><span style="font-size:10.0pt;font-family:"Courier New","serif";color:red">vname</span><span style="font-size:10.0pt;font-family:"Courier New","serif";color:black">)
</span><span style="font-size:10.0pt;font-family:"Courier New","serif";color:red">vname</span><span style="font-size:10.0pt;font-family:"Courier New","serif";color:black">,<o:p></o:p></span></p>
<p class="MsoNormal" style="text-indent:.5in"><span style="font-size:10.0pt;font-family:"Courier New","serif";color:black"> the_line as the_geom,<br>
st_length(st_transform(the_line, 26993)) as feet,<br>
(DATE_PART('day', now() - acqtime) * 24 +<br>
DATE_PART('hour', now() - acqtime) * 60 +<br>
DATE_PART('minute', now() - acqtime)<br>
) as elapsed_min -- Compiled minutes since last major move of asset.<br>
from<br>
<b>loc</b><br>
where<br>
st_length(st_transform(the_line, 26993)) > 30 -- GPS error<br>
order by <br>
</span><span style="font-size:10.0pt;font-family:"Courier New","serif";color:red">vname</span><span style="font-size:10.0pt;font-family:"Courier New","serif";color:black"> desc
<br>
<br>
) as subquery <b>using unique </b></span><b><span style="font-size:10.0pt;font-family:"Courier New","serif";color:red">vname</span></b><span style="font-size:10.0pt;font-family:"Courier New","serif";color:black"> using
srid=200068"</span><span style="font-size:8.0pt;font-family:"Comic Sans MS","serif""><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Comic Sans MS","serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Comic Sans MS","serif"">Bobb<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Comic Sans MS","serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Comic Sans MS","serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Comic Sans MS","serif""><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal" style="margin-left:.5in"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> mapserver-users-bounces@lists.osgeo.org [mailto:mapserver-users-bounces@lists.osgeo.org]
<b>On Behalf Of </b>Basques, Bob (CI-StPaul)<br>
<b>Sent:</b> Monday, December 15, 2014 3:48 PM<br>
<b>To:</b> mapserver-users@osgeo.org<br>
<b>Subject:</b> [mapserver-users] Postgres SQL oddity . . .<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal" style="margin-left:.5in"><o:p> </o:p></p>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:12.0pt;margin-left:.5in">
<span style="font-size:10.0pt;font-family:"Tahoma","sans-serif";color:black">I'm trying to display a set of GPS points from a layer in Postrgres . . .<br>
<br>
Getting this error however:<br>
<br>
</span><span style="font-size:10.0pt;font-family:"Courier New","serif";color:black"><ServiceExceptionReport version="1.3.0" xsi:schemaLocation="<a href="http://www.opengis.net/ogc%20http:/schemas.opengis.net/wms/1.3.0/exceptions_1_3_0.xsd">http://www.opengis.net/ogc
http://schemas.opengis.net/wms/1.3.0/exceptions_1_3_0.xsd</a>"><ServiceException><br>
msDrawMap(): Image handling error. Failed to draw layer named 'AVL_Plot_00to96'.<br>
msPostGISLayerWhichShapes(): Query error. Error executing query: ERROR: column reference "vname" is ambiguous<br>
LINE 1: select "elapsed_min","vname",encode(ST_AsBinary(ST_Force2D("...<br>
<br>
</ServiceException></ServiceExceptionReport></span><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif";color:black"><br>
<br>
from this Call:<br>
<br>
</span><span style="font-size:10.0pt;font-family:"Courier New","serif";color:black">DATA "the_geom from (<br>
<br>
select<br>
distinct on (</span><span style="font-size:10.0pt;font-family:"Courier New","serif";color:red">vname</span><span style="font-size:10.0pt;font-family:"Courier New","serif";color:black">)
</span><span style="font-size:10.0pt;font-family:"Courier New","serif";color:red">vname</span><span style="font-size:10.0pt;font-family:"Courier New","serif";color:black">,<br>
st_length(st_transform(the_line, 26993)) as feet,<br>
(DATE_PART('day', now() - acqtime) * 24 +<br>
DATE_PART('hour', now() - acqtime) * 60 +<br>
DATE_PART('minute', now() - acqtime)<br>
) as elapsed_min, -- Compiled minutes since last major move of asset.<br>
*<br>
from<br>
<b>loc</b><br>
where<br>
st_length(st_transform(the_line, 26993)) > 30 -- GPS error<br>
order by <br>
</span><span style="font-size:10.0pt;font-family:"Courier New","serif";color:red">vname</span><span style="font-size:10.0pt;font-family:"Courier New","serif";color:black"> desc
<br>
<br>
) as subquery <b>using unique </b></span><b><span style="font-size:10.0pt;font-family:"Courier New","serif";color:red">vname</span></b><span style="font-size:10.0pt;font-family:"Courier New","serif";color:black"> using
srid=200068"</span><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif";color:black"><br>
<br>
What I don't understand is how VNAME can be ambiguous when calling only one table (loc)?? The SQL works fine in PGADMIN. Does the using unique vname have something to do with the problem, which seems to be required.<br>
<br>
Help??<br>
<br>
Thanks.<br>
<br>
bobb<br>
<br>
<o:p></o:p></span></p>
</div>
</div>
</body>
</html>