Обсуждение: Order of SUBSTR and UPPER in statement

Поиск
Список
Период
Сортировка

Order of SUBSTR and UPPER in statement

От
Hermann Muster
Дата:
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

Re: Order of SUBSTR and UPPER in statement

От
Michael Fuhr
Дата:
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

Re: Order of SUBSTR and UPPER in statement

От
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

Re: Order of SUBSTR and UPPER in statement

От
Hermann Muster
Дата:
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.
>> 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")
>>
>
>

Re: Order of SUBSTR and UPPER in statement

От
Michael Fuhr
Дата:
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

Re: Order of SUBSTR and UPPER in statement

От
Hermann Muster
Дата:
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

Re: Order of SUBSTR and UPPER in statement

От
"T.J. Adami"
Дата:
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?

Re: Order of SUBSTR and UPPER in statement

От
"Carlo Stonebanks"
Дата:
"Hermann Muster" <Hermann.Muster@gmx.de> wrote in message
news:fov1p9$1l93$1@news.hub.org...
> 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