Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
Unexpected issue with UPPER, LEFT and COLLATE |
Mon, Aug 19 2019 7:41 AM | Permanent Link |
Malcolm Taylor | I just stumbled upon an error with a rarely used SQL statement in my
app. The statement included: UPPER(LEFT("Representing" FOR 3) COLLATE ENG) AS Representing That threw an error saying Expected ) but found COLLATE I fixed it by moving the LEFT function outside the UPPER one as follows: LEFT(UPPER("Representing" COLLATE ENG) FOR 3) AS Representing This version runs fine. But looking at the Help, I can see no reason why one works and the other does not. I suspect that the 'error' was introduced when I did a bulk addition/modification of COLLATE clauses some time ago and failed to test this particular statement. It looks a little like a bug to me. Anyone else have an explanation? Malcolm |
Mon, Aug 19 2019 8:13 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Malcolm
I don't think I'd describe it as a bug. My take is that COLLATE ... is a qualifier for a column name not something that can be applied to generic text. In your first example UPPER(LEFT("Representing" FOR 3) COLLATE ENG) AS Representing you're trying to apply COLLATE to the output of LEFT which is a piece of text. In the second example LEFT(UPPER("Representing" COLLATE ENG) FOR 3) AS Representing you're applying it to the column "Representing" and then operating with LEFT on the result Try UPPER(LEFT("Representing" COLLATE ENG FOR 3) ) AS Representing, and UPPER(LEFT('qwertyuiop' COLLATE ENG FOR 3) ) AS Representing, Roy Lambert |
Mon, Aug 19 2019 11:16 AM | Permanent Link |
Malcolm Taylor | Hi Roy
It would seem that, not for the first time, you are correct. LEFT and UPPER will happily operate on text or a column name. But not so COLLATE which I now read can only be applied to a column name (in various contexts). That explains it, thanks. |
Tue, Aug 20 2019 2:38 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Malcolm
>It would seem that, not for the first time, you are correct. Please refrain - my head is big enough already Roy |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |