Re: Order of SUBSTR and UPPER in statement

On Wed, Feb 13, 2008 at 04:19:09PM +0100, Hermann Muster wrote:
> I encountered something I can't really explain. I use the following
> statement in my application:
>
> COALESCE(UPPER(SUBSTR("Y"."Firma",1,7)),'')
>
> This returns "ERROR: syntax error at end of input"

Please show a complete statement and not just a portion of it. This
expression works for me:

test=> CREATE TABLE "Y" ("Firma" varchar);
CREATE TABLE
test=> INSERT INTO "Y" ("Firma") VALUES ('abcdefghij');
INSERT 0 1
test=> SELECT COALESCE(UPPER(SUBSTR("Y"."Firma",1,7)),'') FROM "Y";
coalesce
----------
ABCDEFG
(1 row)

> However, using the following statement is fine:
>
> COALESCE(SUBSTR(UPPER("X"."Firma"), 1, 7), '')
>
>
> The fieldtype of "Firma" is character varying.
>
> The only difference is the order of UPPER and SUBSTR.

I doubt that; I suspect the query that's failing has some other
problem that's causing the syntax error. Take a closer look,
especially at the end of the query string ("syntax error at end of
input").

--
Michael Fuhr