Search Postgresql Archives

Re: Order of SUBSTR and UPPER in statement

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux