From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Hermann Muster <Hermann(dot)Muster(at)gmx(dot)de> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Order of SUBSTR and UPPER in statement |
Date: | 2008-02-14 14:57:08 |
Message-ID: | 20080214145707.GA4150@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
[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
From | Date | Subject | |
---|---|---|---|
Next Message | Michiel Holtkamp | 2008-02-14 14:57:16 | How to cope with low disk space |
Previous Message | Alvaro Herrera | 2008-02-14 14:56:24 | Re: dynamic crosstab |