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

[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)),'') =

COALESCE(UPPER(SUBSTR("Y"."Firma",1,7)),'') AND
COALESCE(UPPER("X"."PLZZ"),'') = COALESCE(UPPER("Y"."PLZZ"),'') AND
COALESCE(UPPER("X"."PLZP"),'') = COALESCE(UPPER("Y"."PLZP"),'') AND
"X"."RecordID" <> "Y"."RecordID")

--
Michael Fuhr

Michael Fuhr schrieb:

[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)),'') =

Yes, that's what I wanted to write. However, it only works when I change
the order of UPPER and SUBSTR in the statement.

On Thu, Feb 14, 2008 at 04:48:33PM +0100, Hermann Muster wrote:

Michael Fuhr schrieb:

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)),'') =

Yes, that's what I wanted to write. However, it only works when I change
the order of UPPER and SUBSTR in the statement.

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.

--
Michael Fuhr

Michael Fuhr schrieb:

On Thu, Feb 14, 2008 at 04:48:33PM +0100, Hermann Muster wrote:

Michael Fuhr schrieb:

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)),'') =

Yes, that's what I wanted to write. However, it only works when I change
the order of UPPER and SUBSTR in the statement.

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.

I'm terribly sorry, I found the mistake in my expression. There was one
bracket missing. That's why the one expression worked and the other
didn't. Sorry for any inconvenience.

Regards,
Hermann

On 13 fev, 12:19, Hermann Muster <Hermann.Mus...@gmx.de> wrote:

Hi,

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"

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. Is it possible
that this changed during some PostgreSQL version update? By the way,
right now I'm using 8.2.

Regards,
Hermann

Please post the entire SQL statement and table structure, and even
posting here, did you notice in the first SQL you wrote you're using
"Y"."firma", and on the second you typed "X"."firma". Is there any
difference between "X" and "Y" aliases?

"Hermann Muster" <Hermann.Muster@gmx.de> wrote in message
news:fov1p9$1l93$1@news.hub.org...