<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
</head>
<body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
Bob,</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
If you want to keep * in the select, you have to alias the table name and add that to the *. </div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
Eg, </div>
<div><span style="color: rgb(0, 0, 0); font-family: 'Courier New', serif; font-size: 10pt;">DATA "the_geom from (<br>
<br>
select<br>
distinct on (</span><span style="color: red; font-family: 'Courier New', serif; font-size: 10pt;">vname</span><span style="color: rgb(0, 0, 0); font-family: 'Courier New', serif; font-size: 10pt;">) </span><span style="color: red; font-family: 'Courier New', serif; font-size: 10pt;">vname</span><font face="Courier New,serif"><span style="font-size: 10pt;">,</span></font><br>
<font face="Courier New,serif"><span style="font-size: 10pt;"> st_length(st_transform(the_line, 26993)) as feet,</span></font><br>
<font face="Courier New,serif"><span style="font-size: 10pt;"> (DATE_PART('day', now() - acqtime) * 24 +</span></font><br>
<font face="Courier New,serif"><span style="font-size: 10pt;"> DATE_PART('hour', now() - acqtime) * 60 +</span></font><br>
<font face="Courier New,serif"><span style="font-size: 10pt;"> DATE_PART('minute', now() - acqtime)</span></font><br>
<font face="Courier New,serif"><span style="font-size: 10pt;"> ) as elapsed_min, -- Compiled minutes since last major move of asset.</span></font><br>
<font face="Courier New,serif"><span style="font-size: 10pt;"> </span><span style="font-size: 13px;">L</span><span style="font-size: 10pt;">.*</span></font><br>
<font face="Courier New,serif"><span style="font-size: 10pt;"> from</span></font><br>
<font face="Courier New,serif"><span style="font-size: 10pt;"> </span></font><b><font face="Courier New,serif"><span style="font-size: 13px;">L</span><span style="font-size: 10pt;">oc l</span></font></b><br>
<font face="Courier New,serif"><span style="font-size: 10pt;"> where</span></font><br>
<font face="Courier New,serif"><span style="font-size: 10pt;"> st_length(st_transform(the_line, 26993)) > 30 -- GPS error</span></font><br>
<font face="Courier New,serif"><span style="font-size: 10pt;"> order by </span></font><br>
<font face="Courier New,serif"><span style="font-size: 10pt;"> </span></font><span style="color: red; font-family: 'Courier New', serif; font-size: 10pt;">vname</span><span style="color: rgb(0, 0, 0); font-family: 'Courier New', serif; font-size: 10pt;"> desc <br>
<br>
) as subquery <b>using unique </b></span><b style="color: rgb(0, 0, 0); font-family: 'Times New Roman', serif; font-size: 16px;"><span style="font-size: 10pt; font-family: 'Courier New', serif; color: red;">vname</span></b><span style="color: rgb(0, 0, 0); font-family: 'Courier New', serif; font-size: 10pt;"> using
srid=200068"</span></div>
<div style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<br>
</div>
<span id="OLK_SRC_BODY_SECTION" style="color: rgb(0, 0, 0); font-family: Calibri, sans-serif; font-size: 14px;">
<div style="font-family:Calibri; font-size:11pt; text-align:left; color:black; BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BORDER-TOP: #b5c4df 1pt solid; BORDER-RIGHT: medium none; PADDING-TOP: 3pt">
<span style="font-weight:bold">From: </span><Basques>, "Bob (CI-StPaul)" <<a href="mailto:bob.basques@ci.stpaul.mn.us">bob.basques@ci.stpaul.mn.us</a>><br>
<span style="font-weight:bold">Date: </span>Monday, December 15, 2014 at 5:07 PM<br>
<span style="font-weight:bold">To: </span>"Rahkonen Jukka (Tike)" <<a href="mailto:jukka.rahkonen@mmmtike.fi">jukka.rahkonen@mmmtike.fi</a>>, "<a href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a>" <<a href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a>><br>
<span style="font-weight:bold">Subject: </span>[EXTERNAL] Re: [mapserver-users] Postgres SQL oddity . . .<br>
<span style="font-weight:bold">Resent-From: </span>Michael Smith <<a href="mailto:michael.smith@usace.army.mil">michael.smith@usace.army.mil</a>><br>
</div>
<div><br>
</div>
<div 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">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><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;}
@font-face
{font-family:"Segoe UI";
panose-1:2 11 5 2 4 2 4 2 2 3;}
/* 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";}
p.ms-cui-menu, li.ms-cui-menu, div.ms-cui-menu
{mso-style-name:ms-cui-menu;
margin:0in;
margin-bottom:.0001pt;
background:white;
border:none;
padding:0in;
font-size:10.0pt;
font-family:"Segoe UI","sans-serif";
color:#333333;}
p.ms-cui-menusection-title, li.ms-cui-menusection-title, div.ms-cui-menusection-title
{mso-style-name:ms-cui-menusection-title;
margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";
display:none;}
p.ms-cui-ctl, li.ms-cui-ctl, div.ms-cui-ctl
{mso-style-name:ms-cui-ctl;
margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";
color:#333333;}
p.ms-cui-ctl-on, li.ms-cui-ctl-on, div.ms-cui-ctl-on
{mso-style-name:ms-cui-ctl-on;
margin:0in;
margin-bottom:.0001pt;
background:#DFEDFA;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
p.ms-cui-img-cont-float, li.ms-cui-img-cont-float, div.ms-cui-img-cont-float
{mso-style-name:ms-cui-img-cont-float;
margin-top:1.5pt;
margin-right:0in;
margin-bottom:0in;
margin-left:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
p.ms-cui-smenu-inner, li.ms-cui-smenu-inner, div.ms-cui-smenu-inner
{mso-style-name:ms-cui-smenu-inner;
margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
p.ms-owa-paste-option-icon, li.ms-owa-paste-option-icon, div.ms-owa-paste-option-icon
{mso-style-name:ms-owa-paste-option-icon;
margin-top:1.5pt;
margin-right:3.0pt;
margin-bottom:0in;
margin-left:3.0pt;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";
vertical-align:sub;}
p.ms-rtepasteflyout-option, li.ms-rtepasteflyout-option, div.ms-rtepasteflyout-option
{mso-style-name:ms-rtepasteflyout-option;
margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
p.ms-cui-menusection, li.ms-cui-menusection, div.ms-cui-menusection
{mso-style-name:ms-cui-menusection;
margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
span.EmailStyle27
{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]-->
<div lang="EN-US" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size: 8pt; font-family: 'Comic Sans MS', serif;">Vname is NOT distinct in the data table.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size: 8pt; font-family: 'Comic Sans MS', serif;"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size: 8pt; font-family: 'Comic Sans MS', serif;">I got the SQL to pass by dropping the “ * “ from the SQL, but ran into a different problem, how to run a logical expression against a POSTGRES call, looks like it only works
with SHP files. Crap!!<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size: 8pt; font-family: 'Comic Sans MS', serif;"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size: 8pt; font-family: 'Comic Sans MS', serif;">Bobb<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size: 8pt; font-family: 'Comic Sans MS', serif;"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size: 8pt; font-family: 'Comic Sans MS', serif;"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size: 8pt; 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: 10pt; font-family: Tahoma, sans-serif;">From:</span></b><span style="font-size: 10pt; font-family: Tahoma, sans-serif;"> Rahkonen Jukka (Tike) [<a href="mailto:jukka.rahkonen@mmmtike.fi">mailto:jukka.rahkonen@mmmtike.fi</a>]
<br>
<b>Sent:</b> Monday, December 15, 2014 4:01 PM<br>
<b>To:</b> Basques, Bob (CI-StPaul); <a href="mailto:mapserver-users@osgeo.org">mapserver-users@osgeo.org</a><br>
<b>Subject:</b> Re: Postgres SQL oddity . . .<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal" style="margin-left:.5in"><o:p> </o:p></p>
<div>
<div>
<p style="margin-left:.5in;background:white"><span style="font-family: Calibri, sans-serif; color: black;">Hi,<o:p></o:p></span></p>
<p style="margin-left:.5in;background:white"><span style="font-family: Calibri, sans-serif; color: black;"><o:p> </o:p></span></p>
<p style="margin-left:.5in;background:white"><span style="font-family: Calibri, sans-serif; color: black;">If vname is unique in your table, why do you need to do "select distinct" on it?<o:p></o:p></span></p>
<p style="margin-left:.5in;background:white"><span style="font-family: Calibri, sans-serif; color: black;">I would make a blind guess with an alias name </span><span style="font-family: 'Courier New', serif; color: black;">select distinct on (</span><span style="font-family: 'Courier New', serif; color: red;">vname</span><span style="font-family: 'Courier New', serif; color: black;">)
</span><span style="font-family: 'Courier New', serif; color: red;">vname_distinct</span><span style="font-family: Calibri, sans-serif; color: black;"><o:p></o:p></span></p>
<p style="margin-left:.5in;background:white"><span style="font-family: Calibri, sans-serif; color: black;"><o:p> </o:p></span></p>
<p style="margin-left:.5in;background:white"><span style="font-family: Calibri, sans-serif; color: black;">Usually it is not as easy, though<o:p></o:p></span></p>
<p style="margin-left:.5in;background:white"><span style="font-family: Calibri, sans-serif; color: black;"><o:p> </o:p></span></p>
<p style="margin-left:.5in;background:white"><span style="font-family: Calibri, sans-serif; color: black;">-Jukka Rahkonen-<o:p></o:p></span></p>
<p style="margin-left:.5in;background:white"><span style="font-family: Calibri, sans-serif; color: black;"><o:p> </o:p></span></p>
<p style="margin-left:.5in;background:white"><span style="font-family: Calibri, sans-serif; color: black;"> <o:p></o:p></span></p>
<div>
<div class="MsoNormal" align="center" style="margin-left:.5in;text-align:center;background:white">
<span style="font-family: Calibri, sans-serif; color: rgb(40, 40, 40);">
<hr size="2" width="98%" align="center">
</span></div>
<div id="divRplyFwdMsg">
<p class="MsoNormal" style="margin-left:.5in;background:white"><span style="font-size: 11pt; font-family: Calibri, sans-serif; color: black;">Basques, Bob wrote:</span><span style="font-family: Calibri, sans-serif; color: rgb(40, 40, 40);"><o:p></o:p></span></p>
<div>
<p class="MsoNormal" style="margin-left:.5in;background:white"><span style="font-family: Calibri, sans-serif; color: rgb(40, 40, 40);"> <o:p></o:p></span></p>
</div>
</div>
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:12.0pt;margin-left:.5in;background:white">
<span style="font-size: 10pt; 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: 10pt; 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: 10pt; font-family: Tahoma, sans-serif; color: black;"><br>
<br>
from this Call:<br>
<br>
</span><span style="font-size: 10pt; font-family: 'Courier New', serif; color: black;">DATA "the_geom from (<br>
<br>
select<br>
distinct on (</span><span style="font-size: 10pt; font-family: 'Courier New', serif; color: red;">vname</span><span style="font-size: 10pt; font-family: 'Courier New', serif; color: black;">)
</span><span style="font-size: 10pt; font-family: 'Courier New', serif; color: red;">vname</span><span style="font-size: 10pt; 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: 10pt; font-family: 'Courier New', serif; color: red;">vname</span><span style="font-size: 10pt; font-family: 'Courier New', serif; color: black;"> desc
<br>
<br>
) as subquery <b>using unique </b></span><b><span style="font-size: 10pt; font-family: 'Courier New', serif; color: red;">vname</span></b><span style="font-size: 10pt; font-family: 'Courier New', serif; color: black;">
using srid=200068"</span><span style="font-size: 10pt; 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>
</div>
</div>
</div>
</div>
</div>
</div>
</span>
</body>
</html>