1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2001
    Rep Power

    Unhappy how would i convert this to an ANSI/Postgres compatible query?

    how do i convert this to ANSI/Postgres usable SQL?
    here is an example of the end of a SQL statement that I need to convert. the SELECT clause is irrelevant (it was just too much to post).

    i am kind of lost on what to do with the outer joins that i have on a table to a particular field for example ---- fax.gctyp(+)='FX'


    gnams gnamsOffices,
    offices offices,
    offmls offmls,
    phones phone,
    phones fax,
    Addresses addresses,
    gnams gnamsMgr,
    phones mgrPhone,
    phones asstMgrPhone
    gnamsOffices.namid=offices.namid AND
    offices.namid=phone.namid(+) AND
    offices.namid=fax.namid(+) AND
    offices.namid=addresses.namid(+) AND
    offices.OffCo > 0 AND
    offices.offco=offmls.offco(+) AND
    offices.offno=offmls.offno(+) AND
    gnamsOffices.NamTyp='IO' AND
    phone.gctyp(+)='PH' AND
    (phone.publish='y' OR phone.publish='Y' or phone.publish is null) AND
    fax.gctyp(+)='FX' AND
    addresses.GCCode='BUS' AND
    mgrPhone.gctyp(+)='PH' AND
    asstMgrPhone.gctyp(+)='PH' AND
    offices.Prod_Admin='P' AND
    offices.status='A' AND
    offices.mngr_nameid=gnamsMgr.namid(+) AND
    offices.mngr_nameid=mgrPhone.namid(+) AND
    offices.asst_mngr_namid=asstMgrPhone.namid(+) AND
    offices.SHOW_ON_ROSTER='Y' AND
    mgrPhone.gccode(+)='201' AND
    asstMgrPhone.gccode(+)='201' AND
    offices.offno IN(SELECT offno FROM offcnty WHERE cnty_id=4)
    ORDER BY officeName,offNo,ph_conseq,fx_conseq,mngr_conseq


    i am trying to write a converter to do this for all of my code, i am just unsure how to do some of this in a processable, efficient way

    thanks... travis
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Outside US
    Rep Power
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2001
    Rep Power
    k, read it...

    didnt read where it described an outer join to a column like


    which is what i am having problems on...
    i know how to write outer joins from oracle to ansi compliant, just having problems with certain situations like the one stated above.

IMN logo majestic logo threadwatch logo seochat tools logo