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

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

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

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

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

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