Michael Fuhr (m...@fuhr.org)

I still don't believe that order of UPPER and SUBSTR is relevant
to the syntax error.  Please post two complete queries, one with
the order that works and one with the order that doesn't.

[Please copy the mailing list on replies so others can contribute
to and learn from the discussion.]

On Thu, Feb 14, 2008 at 09:56:36AM +0100, Hermann Muster wrote:
> The statement I'm using is the following:
> SELECT
> "FIRMEN"."Firma","FIRMEN"."Firma2","FIRMEN"."Firma3","FIRMEN"."Such","FIRMEN"."Land","FIRMEN"."PLZZ","FIRMEN"."Ort","FIRMEN"."Strasse","FIRMEN"."PLZP","FIRMEN"."Postfach","FIRMEN"."Telefon","FIRMEN"."Telefax","FIRMEN"."eMail","FIRMEN"."Internet","FIRMEN"."KundenNr","FIRMEN"."UST_ID","FIRMEN"."ABC","FIRMEN"."Zusatz1","FIRMEN"."Zusatz2","FIRMEN"."Zusatz3","FIRMEN"."Zusatz4","FIRMEN"."Zusatz5","FIRMEN"."BLZ","FIRMEN"."KtoNr","FIRMEN"."Bank","FIRMEN"."IBAN","FIRMEN"."Kreditkart","FIRMEN"."KreditkNr","FIRMEN"."AdressTyp","FIRMEN"."VKGebiet","FIRMEN"."Zahlungart","FIRMEN"."UmsatzSoll","FIRMEN"."BonAnfrDat","FIRMEN"."BonInfoDat","FIRMEN"."BonIndex","FIRMEN"."BonLimit","FIRMEN"."BonOK","FIRMEN"."BonInfo","FIRMEN"."BonKapital","FIRMEN"."BonUmsJahr","FIRMEN"."BonMAZahl","FIRMEN"."BonZahlung","FIRMEN"."Betreuer","FIRMEN"."Com_Wahl","FIRMEN"."Symbol","FIRMEN"."ErfDat","FIRMEN"."ErfUser","FIRMEN"."L_Dat","FIRMEN"."L_User","FIRMEN"."RecordID","FIRMEN"."Z_Import_PK","FIRMEN"."Z_Import_FK","FIRMEN"."KreditkInh","FIRMEN"."Br
> anchenTyp1","FIRMEN"."BranchenTyp2","FIRMEN"."KK_Exp_J","FIRMEN"."KK_Exp_M","FIRMEN"."Kategorie"
> FROM "FIRMEN"
> WHERE "FIRMEN"."RecordID" IN (SELECT DISTINCT "X"."RecordID" FROM "FIRMEN"
> "X" INNER JOIN "FIRMEN" "Y" ON
> COALESCE(UPPER(SUBSTR("X"."Firma",1,7)) =

I haven't examined the entire query but the above line appears to
be the problem.  Did you mean to write the following?

COALESCE(UPPER(SUBSTR("X"."Firma",1,7)),'') =

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").

You could convert the empty strings to NULL:

USING cast(nullif(amount, '') AS numeric)

The PostGIS function compute_geometry_stats() logs such a message.
I'd guess you're using PostGIS and those tables have NULL in all
rows' geometry columns.