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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment