 |  LoginProducts  SalesSupportDownloadsAbout |
| Home » Technical Support » ElevateDB Technical Support » Incident Reports » Incident Reports Addressed for Version 2.25 » View Incident Report |
View Incident Report Serious |
| Reported By: Adam Brett Reported On: 10/25/2016 For: Version 2.24 Build 3 |
I have spent a bit of time fighting with a SQL statement to get it to work.
I am not sure what is going on & wanted some clarification. What I am showing is only a small part of the whole statement, which contains multiple SELECTs, but this segment demonstrates the error.
The first SQL statement works, but the second one gives an error.
The only difference is the ommission of the "DateWanted" field.
Works:
SELECT
'1. Late Sales' as "Heading",
NiceDate(SI.DateWanted) +
'<a HREF = ' + COALESCE(CAST(SI.ID AS VARCHAR), '') + '>'
+ ' FMID:' + COALESCE(CAST(SI.FMID as VARCHAR), '') + ' ' + COALESCE(O.FullName, '-no customer-') + '</a> '
+ COALESCE(REPLACE(',' WITH ', ' IN CAST(LIST(DISTINCT ORDERED P.FullName) as VARCHAR(500))), '')
as "Content",
DateWanted
FROM
SalesInvoices SI
LEFT JOIN Organisations O ON O.ID = SI.CustomerPayingID
LEFT JOIN SalesInvoiceItems SII ON SII.SalesInvoicesID = SI.ID
LEFT JOIN Products P ON P.ID = SII.ProductsID
WHERE StatusID IN (43, 44, 45)
AND CAST(DateWanted as Date) BETWEEN Current_Date - INTERVAL '14' DAY AND Current_Date - INTERVAL '1' DAY
GROUP BY SI.ID
ORDER BY Heading, Content, DateWanted
Doesn't work:
SELECT
'1. Late Sales' as "Heading",
NiceDate(SI.DateWanted) +
'<a HREF = ' + COALESCE(CAST(SI.ID AS VARCHAR), '') + '>'
+ ' FMID:' + COALESCE(CAST(SI.FMID as VARCHAR), '') + ' ' + COALESCE(O.FullName, '-no customer-') + '</a> '
+ COALESCE(REPLACE(',' WITH ', ' IN CAST(LIST(DISTINCT ORDERED P.FullName) as VARCHAR(500))), '')
as "Content"
FROM
SalesInvoices SI
LEFT JOIN Organisations O ON O.ID = SI.CustomerPayingID
LEFT JOIN SalesInvoiceItems SII ON SII.SalesInvoicesID = SI.ID
LEFT JOIN Products P ON P.ID = SII.ProductsID
WHERE StatusID IN (43, 44, 45)
AND CAST(DateWanted as Date) BETWEEN Current_Date - INTERVAL '14' DAY AND Current_Date - INTERVAL '1' DAY
GROUP BY SI.ID
ORDER BY Heading, Content
CommentsThis was caused by the engine being overly-aggressive in the query compilation phase. It was making sure that you don't try to use an ORDER BY in a situation where you're not selecting any columns, only expressions and aggregates. However, it was doing it wrong. It needed to also look to see if a GROUP BY was specified and, if so, let it go.
ResolutionFixed Problem on 11/8/2016 in version 2.25 build 1
Products AffectedElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB PHP Standard
ElevateDB PHP Standard with Source
ElevateDB PHP Trial
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial
This web page was last updated on Wednesday, March 20, 2024 at 07:22 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ?  E-mail us at info@elevatesoft.com |
