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

    Unhappy Unanswered: Join Translation in SQL Server

    Hi
    Can any one please translate these oracle inner join and outer join in SQL
    Server. Any help will be highly appreciate.


    SELECT V1.DB_VENDOR V1.NAME1,1,21)||DECODE(B1.JOINT_DESC,NULL,'',
    '(JV)')||
    DECODE(B1.NON_RESP,'Y','(NR)',
    'N','') V1_NAME1,
    B2.DB_CONTRACT B2_DB_CONTRACT, B2.BID_VENDOR B2_BID_VENDOR, B2.RANK_NUMB B2_RANK_NUMB,
    V2.DB_VENDOR V2_DB_VENDOR,
    SUBSTR(V2.NAME1,1,21)||DECODE(B2.JOINT_DESC,NULL,' ',
    '(JV)')||
    DECODE(B2.NON_RESP,'Y','(NR)',
    'N','') V2_NAME1
    FROM BID_TOTAL B1, BID_TOTAL B2, VENDOR V1, VENDOR V2
    WHERE V1.DB_VENDOR = B1.BID_VENDOR
    AND V2.DB_VENDOR (+) = B2.BID_VENDOR
    AND B1.DB_CONTRACT = B2.DB_CONTRACT (+)
    AND B1.RANK_NUMB > 1
    AND MOD(B1.RANK_NUMB,2) = 0
    AND B2.RANK_NUMB (+) = B1.RANK_NUMB + 1
    AND B2.DB_CONTRACT IS NOT NULL
    and b2.db_Contract=39624
    ORDER BY B1.RANK_NUMB
    mr_roomi

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    They are all INNER JOINed. The (+) means (I think) the same as (INDEX=) hint. I'm having bigger problems with DECODE, don't have anything on Horacle handy at this point
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

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

    Unhappy help

    any more help from some on on join?
    mr_roomi

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    SELECT SUBSTR(V1.DB_VENDOR V1.NAME1,1,21)
    	+ CASE  WHEN B1.JOINT_DESC IS NULL THEN '' ELSE '(JV)' END
    	+ CASE  WHEN B1.NON_RESP = 'Y'     THEN '(NR)'
    		WHEN B1.NON_RESP = 'N'     THEN ''
    	  END AS V1_NAME1
    	, B2.DB_CONTRACT AS B2_DB_CONTRACT
    	, B2.BID_VENDOR AS B2_BID_VENDOR
    	, B2.RANK_NUMB AS B2_RANK_NUMB
    	, V2.DB_VENDOR AS V2_DB_VENDOR
    	, SUBSTR(V2.NAME1,1,21)
    	+ CASE  WHEN B2.JOINT_DESC IS NULL THEN '' ELSE '(JV)' END
    	+ CASE  WHEN B2.NON_RESP = 'Y' THEN '(NR)'
    	        WHEN B2.NON_RESP = 'N' THEN '' 
    	  END AS V2_NAME1
          FROM BID_TOTAL B1
     LEFT JOIN BID_TOTAL B2 ON B1.DB_CONTRACT = B2.DB_CONTRACT (+)
    INNER JOIN VENDOR V1 ON V1.DB_VENDOR = B1.BID_VENDOR
    RIGHT JOIN VENDOR V2 ON V2.DB_VENDOR (+) = B2.BID_VENDOR
         WHERE B1.RANK_NUMB > 1
           AND MOD(B1.RANK_NUMB,2) = 0
           AND B2.DB_CONTRACT IS NOT NULL
           AND b2.db_Contract=39624
     ORDER BY B1.RANK_NUMB
    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.

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

    Mod Function

    Hi Brett,

    the mod function line is giving me error. I chanaged to this:
    AND B1.RANK_NUMB % '2' = '0'
    but it is still giving me error i.e.

    Operand type clash: varchar is incompatible with void type
    Server: Msg 8117, Level 16, State 1, Line 1
    Operand data type numeric is invalid for modulo operator.

    Thank you very much for immediate help.

    waiting for response
    mr_roomi

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I forget what MOD does....do you know?
    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.

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

    Mod

    Hi Brett.

    Mod return the remainder of a division
    mr_roomi

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Mr_roomi
    Hi Brett.

    Mod return the remainder of a division
    Well then that's a bizarre predicate

    AND B1.RANK_NUMB/2.00-FLOOR(B1.RANK_NUMB/2.00) = 0
    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.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    AND cast(B1.RANK_NUMB as int) % 2 = 0
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What does this do again?

    SELECT cast(100.00/20.00 as int) % 2
    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.

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

    Smile thank you

    Hi Brett,

    Thank you very much...it worked
    mr_roomi

Posting Permissions

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