Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Unanswered: outer join oracle query translate in sql server

    Hi,
    I have this oracle query with outer join situation. how can i convert it into sql server query.

    SELECT distinct ae.dB_CONTRACT,CP.PC_CODE,BID_ITEM.ITEM_NO,
    'N',BID_ITEM.PRICE_WORDS,OFF_ITEM.DESCPT,
    OFF_ITEM.UNITS,OFF_ITEM.TYPE_ITEM,
    PRES_ITEM.RET_PERC
    FROM BID_TOTAL,BID_ITEM,OFF_ITEM,PRES_ITEM, AE_CONTRACT AE, CONTRACT_PC CP
    WHERE RANK_NUMB = 1
    AND BID_TOTAL.DB_CONTRACT = 37044
    AND BID_TOTAL.DB_CONTRACT = BID_ITEM.DB_CONTRACT
    AND BID_TOTAL.BID_VENDOR = BID_ITEM.BID_VENDOR
    AND BID_ITEM.DB_CONTRACT = OFF_ITEM.DB_CONTRACT
    AND BID_ITEM.ITEM_NO = OFF_ITEM.ITEM_NO
    AND OFF_ITEM.ITEM_NO = PRES_ITEM.ITEM_NO (+)
    AND AE.DB_CONTRACT=BID_TOTAL.DB_cONTRACT
    AND CP.DB_CONTRACT = AE.DB_CONTRACT
    AND CP.pc_code = 1

    Any Help will be appreciated.
    mr_roomi

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I thhink this is it

    Code:
    SELECT DISTINCT 
    	  ae.dB_CONTRACT
    	, CP.PC_CODE
    	, BID_ITEM.ITEM_NO
    	, 'N'
    	, BID_ITEM.PRICE_WORDS
    	, OFF_ITEM.DESCPT
    	, OFF_ITEM.UNITS
    	, OFF_ITEM.TYPE_ITEM
    	, PRES_ITEM.RET_PERC
         FROM BID_TOTAL
         JOIN BID_ITEM
           ON BID_TOTAL.DB_CONTRACT = BID_ITEM.DB_CONTRACT
          AND BID_TOTAL.BID_VENDOR  = BID_ITEM.BID_VENDOR
         JOIN OFF_ITEM
           ON BID_ITEM.ITEM_NO      = OFF_ITEM.ITEM_NO
          AND BID_ITEM.DB_CONTRACT  = OFF_ITEM.DB_CONTRACT
    LEFT JOIN PRES_ITEM
           ON OFF_ITEM.ITEM_NO      = PRES_ITEM.ITEM_NO
         JOIN AE_CONTRACT AE
           ON AE.DB_CONTRACT        = BID_TOTAL.DB_cONTRACT
         JOIN CONTRACT_PC CP
           ON CP.DB_CONTRACT        = AE.DB_CONTRACT
        WHERE RANK_NUMB             = 1
          AND BID_TOTAL.DB_CONTRACT = 37044
          AND CP.pc_code            = 1
    Test it out...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    worked excellent

    Hi,
    thank you very much..it worked excellent


    Originally posted by Brett Kaiser
    I thhink this is it

    Code:
    SELECT DISTINCT 
    	  ae.dB_CONTRACT
    	, CP.PC_CODE
    	, BID_ITEM.ITEM_NO
    	, 'N'
    	, BID_ITEM.PRICE_WORDS
    	, OFF_ITEM.DESCPT
    	, OFF_ITEM.UNITS
    	, OFF_ITEM.TYPE_ITEM
    	, PRES_ITEM.RET_PERC
         FROM BID_TOTAL
         JOIN BID_ITEM
           ON BID_TOTAL.DB_CONTRACT = BID_ITEM.DB_CONTRACT
          AND BID_TOTAL.BID_VENDOR  = BID_ITEM.BID_VENDOR
         JOIN OFF_ITEM
           ON BID_ITEM.ITEM_NO      = OFF_ITEM.ITEM_NO
          AND BID_ITEM.DB_CONTRACT  = OFF_ITEM.DB_CONTRACT
    LEFT JOIN PRES_ITEM
           ON OFF_ITEM.ITEM_NO      = PRES_ITEM.ITEM_NO
         JOIN AE_CONTRACT AE
           ON AE.DB_CONTRACT        = BID_TOTAL.DB_cONTRACT
         JOIN CONTRACT_PC CP
           ON CP.DB_CONTRACT        = AE.DB_CONTRACT
        WHERE RANK_NUMB             = 1
          AND BID_TOTAL.DB_CONTRACT = 37044
          AND CP.pc_code            = 1
    Test it out...
    mr_roomi

  4. #4
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    if your oracle install is 9i
    the ansi join syntax actually works
    after 20 years!!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •