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

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

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.

Malcolm

>It would seem that, not for the first time, you are correct.

Please refrain - my head is big enough already

Roy