Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
LIST and ORDERED and CAST DATE FORMAT |
Thu, Jul 11 2019 10:31 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | I'm trying to get a list of dates to bung in a drop down and having a bit of a problem with the ordering
SELECT LIST(DISTINCT ORDERED CAST(_DatePaid AS VARCHAR(10)),#13), LIST(DISTINCT ORDERED CAST(_DatePaid AS VARCHAR(10) DATE FORMAT 'DD/MM/YYYY'),#13) FROM Transactions It looks like the ORDERED is being carried out after the CAST and DATE FORMAT is executed I can easily just use the result set and loop through it to build my picklist but I was hoping to just assign the field. Anyone have a way of getting things in date order with LIST. I'm trying a subselect but can't get it to work. Roy Lambert |
Thu, Jul 11 2019 11:54 AM | Permanent Link |
Adam Brett Orixa Systems | -- Untested!
SubSELECT?? SELECT LIST(CAST(_DatePaid AS VARCHAR(10) DATE FORMAT 'DD/MM/YYYY'), #13) FROM ( SELECT DISTINCT _DatePaid FROM Transactions ORDER BY DatePaid) as T |
Fri, Jul 12 2019 2:52 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
Good idea but the result you get is 17/10/2018 05/06/2019 29/10/2018 I have no idea how that is sorted Roy Lambert |
Sat, Jul 13 2019 9:04 AM | Permanent Link |
Adam Brett Orixa Systems | >>17/10/2018
>>05/06/2019 >>29/10/2018 >>I have no idea how that is sorted Weird! I tested it on my own data & I swear it was sorted! (might be that the original data was naturally date-sorted). I feel it should work ... the order of the Sub-select, ought to determine the ordering of the main select, but it seems the LIST statement is following some other logic when it generates the list. Another way to go would be a lot more cumbersome, but might work (please don't laugh!): - In the LIST statement FORMAT YYYY/MM/DD and order the list in reverse order. LIST(ORDERED DESC CAST(_DatePaid AS VARCHAR(10) DATE FORMAT 'YYYY/MM/DD'), #13) That gets the data into the list in the correct order, but not formatted as you would want, and with the "first" item last. Then write a function REVERSEORDER to reverse the WHOLE STRING. It would have to look for separators (either '/' or #13) and not separate the sections inbetween these (the DD MM YYYY) ... it wouldn't be super-easy to write, but it would be doable. REVERSEORDER(LIST(ORDERED DESC CAST(_DatePaid AS VARCHAR(10) DATE FORMAT 'YYYY/MM/DD'), #13)) Sorry if this is just madness ... it would be easier if Tim got the LIST to preserve the ordering of the SUBSELECT. |
Sat, Jul 13 2019 9:16 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
>Weird! I tested it on my own data & I swear it was sorted! (might be that the original data was naturally date-sorted). I feel it should work ... the order of the Sub-select, ought to determine the ordering of the main select, but it seems the LIST statement is following some other logic when it generates the list. Data in the right order would be my guess as well >Another way to go would be a lot more cumbersome, but might work (please don't laugh!): It probable would but I'll go with just using the result set. It just seemed that using LIST would be more elegant - as my wife often says - Woops >it would be easier if Tim got the LIST to preserve the ordering of the SUBSELECT. Yup - I'm sure there will be a good reason why not - either that or its one of those things that never even occurs to you. Thanks for trying Roy |
This web page was last updated on Tuesday, May 7, 2024 at 02:40 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |