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

    Join Date
    Feb 2001
    Posts
    68
    Rep Power
    14

    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'



    -----

    FROM
    gnams gnamsOffices,
    offices offices,
    offmls offmls,
    phones phone,
    phones fax,
    Addresses addresses,
    gnams gnamsMgr,
    phones mgrPhone,
    phones asstMgrPhone
    WHERE
    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
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2001
    Posts
    68
    Rep Power
    14
    k, read it...

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

    fax.gctyp(+)='FX'

    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