<p>Good afternoon,</p>
<p>Hopefully someone can give me a nudge in the right direction to help me with my query...</p>
<p> </p>
<p>I'm trying to retrieve data from the <strong>locationspec</strong> table in maximo. The data I want displayed is in the field <strong>LOCATIONSPEC.ALNVALUE</strong>. Each row is defined by an attribute ID. (see attachment <em>'Maximospec'</em>)</p>
<p> </p>
<p>The attribute ID's are named:</p>
<p> </p>
<p><em>Battery_Type</em></p>
<p><em>Lantern</em></p>
<p><em>Sol_Panel_Type</em></p>
<p> </p>
<p>I would like to display these three attributes as individual columns in the query, against a location; without defining the attribute within the where clause I'm getting four rows per location. (see attachment <em>'Results'</em>).</p>
<p> </p>
<p>Ideally I would like to display</p>
<p> </p>
<p>location 1 | data | data | battery_type | lantern | sol panel type</p>
<p>location 2 | data | data | battery_type | lantern | sol panel type</p>
<p> </p>
<p>It's currently displaying as:</p>
<p> </p>
<p>location 1 | data | data | battery type</p>
<p>location 1 | data | data | lantern</p>
<p>location 1 | data | data | sol panel type</p>
<p>location 2 | data | data | battery type</p>
<p>location 2 | data | data | lantern</p>
<p>location 2 | data | data | sol panel type</p>
<p> </p>
<p><u><strong>This is my query</strong></u></p>
<p> </p>
<div>select locations.location, locations.th_designat, classstructure.description, locations.status, locations.th_area, locations.th_concatlat, </div>
<div>locations.th_datum, locations.th_latm, th_hazsignal.haztype, th_enav.code, TH_LIGHTCHARC.LIGHTCHAR, TH_LIGHTCHARC.RANGE, LOCATIONSPEC.ALNVALUE</div>
<div> </div>
<div>from locations</div>
<div> </div>
<div>LEFT OUTER JOIN classstructure</div>
<div>ON locations.classstructureid=classstructure.classstructureid</div>
<div>LEFT OUTER JOIN th_hazsignal</div>
<div>ON locations.location=th_hazsignal.location</div>
<div>LEFT OUTER JOIN th_enav</div>
<div>ON locations.location=th_enav.location</div>
<div>LEFT OUTER JOIN TH_LIGHTCHARC</div>
<div>ON locations.location=TH_LIGHTCHARC.location</div>
<div>FULL OUTER JOIN LOCATIONSPEC</div>
<div>ON LOCATIONSPEC.LOCATION=locations.location</div>
<div> </div>
<div>where (locations.status = 'OPERATING') and (classstructure.description IN ('TYPE 2','TYPE 1')) </div>
<div> </div>
<div> </div>
<div> </div>
<div>Any help would be greatly appreciated.</div>
<div> </div>
