Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
ORDER BY and non native columns |
Wed, Aug 16 2017 5:17 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Sorry for the long preamble. I'm also not sure if this is just a moan, a bug, or a suggestion (Tim you can decide)
I have a nice generated script in my app. One line is (SELECT MAX(_Date) FROM "TfRData".CallStats WHERE CallStats._fkContacts = PCT._fkContacts) AS _LastDialed, The end result of running the script is to create an in-memory table. The user can select from several indexing options one of which is _LastDialed. Playing around in EDBManager just to make sure I was getting it right I copied the sql the script generated and ran it with an ORDER BY _LastDialed. This, naturally, put the null dates at the top so I tried ORDER BY COALESCE(_LastDialed, DATE '3000-01-01') and got ElevateDB Error #700 An error was found in the statement at line 63 and column 19 (ElevateDB Error #401 The column _LastDialed does not exist in the table Calls) When I tried ORDER BY COALESCE(_LastResult,'XXXX') that worked. _LastResult is a column in Calls I then tried ORDER BY (SELECT MAX(_Date) FROM "TfRData".CallStats WHERE CallStats._fkContacts = PCT._fkContacts) and get ElevateDB Error #700 An error was found in the statement at line 63 and column 43 (Invalid expression . found, schema qualifier not allowed) but ORDER BY (SELECT MAX(_Date) FROM CallStats WHERE CallStats._fkContacts = PCT._fkContacts) does work, as does ORDER BY COALESCE( (SELECT MAX(_Date) FROM CallStats WHERE CallStats._fkContacts = PCT._fkContacts), DATE '3000-01-01') The latter puts the nulls at the bottom as well (which is where I wanted them) I have no idea if this is in accordance with the standard or not, but I'd like to be able to use COALESCE etc and schema qualifiers for non-native columns as well as native ones (but not very often). Roy Lambert |
Wed, Aug 16 2017 10:37 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< I have no idea if this is in accordance with the standard or not, but I'd like to be able to use COALESCE etc and schema qualifiers for non-native columns as well as native ones (but not very often). >> I'll have to check to see if there's any issues with using qualifiers in the ORDER BY. If there isn't, then I'll go ahead and enable them in the next build. Your use of the correlation name from the SELECT expressions in the ORDER BY will work, provided that you get rid of the COALESCE expression and move it into an additional SELECT expression itself: (SELECT COALESCE(MAX(_Date) FROM "TfRData".CallStats WHERE CallStats._fkContacts = PCT._fkContacts), DATE '3000-01-01') AS _LastDialedNonNull, ... As for allowing SELECT expression correlation names in *other* expressions in the ORDER BY, I'll have to also double-check on that to see if it's possible. Tim Young Elevate Software www.elevatesoft.com |
Wed, Aug 16 2017 10:59 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
><< I have no idea if this is in accordance with the standard or not, but I'd like to be able to use COALESCE etc and schema qualifiers for non-native columns as well as native ones (but not very often). >> >I'll have to check to see if there's any issues with using qualifiers in the ORDER BY. If there isn't, then I'll go ahead and enable them in the next build. Thank you >Your use of the correlation name from the SELECT expressions in the ORDER BY will work, provided that you get rid of the COALESCE expression and move it into an additional SELECT expression itself: > >(SELECT COALESCE(MAX(_Date) FROM "TfRData".CallStats WHERE CallStats._fkContacts = PCT._fkContacts), DATE '3000-01-01') AS _LastDialedNonNull, I knew that - I was just trying to be clever and playing silly buggers >As for allowing SELECT expression correlation names in *other* expressions in the ORDER BY, I'll have to also double-check on that to see if it's possible. Thanks again It came about because two new options are to display items in the project in oldest called first and most recent called first. Part of me said NULL = never called which just has to be older and hence at the top and part said NULL = never called so must be at the bottom and since I'd never tried using a function in the ORDER BY decided to give it a go. If its of any interest I'm still not sure wether putting the nulls at the top or bottom is "correct" Roy |
Thu, Aug 17 2017 1:42 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< If its of any interest I'm still not sure wether putting the nulls at the top or bottom is "correct" >> NULLs are a tough nut to crack in terms of concepts like ordering. From a software standpoint, they naturally sort at the top of an ascending list (and vice-versa for descending), but that's an EDB implementation detail leaking out. I'll have to check the SQL standard and see what they say about the ordering of NULLs. They might spell it out, and I just forgot. I haven't touched the standard PDFs since I was first developing ElevateDB. Tim Young Elevate Software www.elevatesoft.com |
Fri, Aug 18 2017 3:20 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
><< If its of any interest I'm still not sure wether putting the nulls at the top or bottom is "correct" >> > >NULLs are a tough nut to crack in terms of concepts like ordering. From a software standpoint, they naturally sort at the top of an ascending list (and vice-versa for descending), but that's an EDB implementation detail leaking out. > I didn't mean technically correct - I meant from a user viewpoint >I'll have to check the SQL standard and see what they say about the ordering of NULLs. They might spell it out, and I just forgot. I haven't touched the standard PDFs since I was first developing ElevateDB. Personally I'd preserve you sanity and leave them gathering digital dust Roy |
Tue, Aug 22 2017 11:26 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< I didn't mean technically correct - I meant from a user viewpoint >> From a user's perspective, I would expect NULLs to sort at the beginning for ASC and the opposite for DESC. Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Monday, April 29, 2024 at 05:23 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |