Friday, March 9, 2012

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_NUMBThey 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 ;)|||any more help from some on on join?|||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|||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|||I forget what MOD does...do you know?|||Hi Brett.

Mod return the remainder of a division|||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|||AND cast(B1.RANK_NUMB as int) % 2 = 0|||What does this do again?

SELECT cast(100.00/20.00 as int) % 2|||Hi Brett,

Thank you very much...it worked

No comments:

Post a Comment