1 Reply by brianmiller

  • brianmiller
  • Veteran
  • Offline
  • Registered: 10-05-2006
  • Posts: 113

Re: Location Name sort - Site Summary Report

I want to sort the results of the Site Summary Report (All Sites Listed) V06-12.xml by Location_Name.Item_Name when I select to show all sites (*). I thought this would be easy by just adding
Order By LN.Item_Name to the bottom of the /**/FROM/**/ section at the end of the <Where keytype="Default"> section.

This does not work. It says the Column prefix LN does not match with a table name or alias... and yet it is listed as Location_Name LN at the top of that section.

Can anyone help please?

Thanks in anticipation

Brian

Brian Miller
(Conservation Officer (Buckinghamshire), BBOWT)

brianmiller's Website

2 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Location Name sort - Site Summary Report

That query is using a derived table, so the LN.Item_Name isn't visible to the outer select. You need to sort on the alias for LN.Item_Name that is generated in the inner query, i.e., Location_Name. Here's what the final code would look like:

GROUP BY L.Location_Key, LN.Item_Name, LT.Short_Name, LH.Hierarchy
) AS D INNER JOIN Location L ON L.Location_Key = D.Location_Key
ORDER BY Location_Name /* Here's the new order by */
DROP TABLE #Location_Hierarchy
DROP TABLE #Location_Children
</Where>
Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

3 Reply by brianmiller

  • brianmiller
  • Veteran
  • Offline
  • Registered: 10-05-2006
  • Posts: 113

Re: Location Name sort - Site Summary Report

Hi Charles,

Thanks for that, works great. Not sure I understand it yet but will look in more detail tomorrow.

Cheers

Brian

Brian Miller
(Conservation Officer (Buckinghamshire), BBOWT)

brianmiller's Website

4 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Location Name sort - Site Summary Report

There is also an <Orderby> tag one can use, but I've not been able to get it working. As far as I can tell, you would add something like this after the <Where> tags:

<Orderby name="Description" />

If you wanted to order by the description column. Unfortunately, I can't get it to work. If I use this tag, I get the following exception:

Exception path:
EVariantTypeCastError : Could not convert variant of type (Null) into type (String)

Perhaps this is something John could shed some light on?

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website