Oracle query in SQL Server

Hi,

I want to write this query in sql server. Please help me, it is very urgent :(

Any help will be appreciated.

SELECT VALID_ITEM.PC_CODE,PC_TITLE,VALID_ITEM.DB_CONTRACT ,
valid_item.ITEM_NO,DESCPT,UNITS,CONTQ,
CONTR_PRICE ,nz(QTD,0) as qtd,ind_ovr_und,
nz(QTD,0) - NVL(QUANTITY,0) as QUANT_PREV,
Nz(QUANTITY,0) as quant_rev,
round(NVL(quantity,0)*nvl(CONTR_PRICE,0),2) as QTD_VAL,
AMT_PAID_ITEM,AMT_RET_ITEM
FROM VALID_ITEM, cqe_item,CONTRACT_PC
WHERE valid_item.db_contract = contract_pc.db_contract
and valid_item.pc_code = contract_pc.pc_code
and valid_item.db_contract = cqe_item.db_contract (+)
and cqe_item.cqe_numb (+) = :EST_NO
and valid_item.item_no = cqe_item.item_no (+)
and valid_item.pc_code = cqe_item.pc_code (+)
order by valid_item.item_noHow about

SELECT i.PC_CODE
, PC_TITLE
, i.DB_CONTRACT
, i.ITEM_NO
, DESCPT
, UNITS
, CONTQ
, CONTR_PRICE
, ISNULL(QTD,0) as qtd
, ind_ovr_und
, ISNULL(QTD,0) - ISNULL(QUANTITY,0) as QUANT_PREV
, Nz(QUANTITY,0) as quant_rev
, ROUNDISNULL(quantity,0)*ISNULL(CONTR_PRICE,0),2) as QTD_VAL
, AMT_PAID_ITEM
, AMT_RET_ITEM
FROM VALID_ITEM v
LEFT JOIN cqe_item i
ON v.db_contract = i.db_contract
AND v.item_no = i.item_no
AND v.pc_code = i.pc_code
LEFT JOIN CONTRACT_PC c
ON v.db_contract = contract_pc.db_contract
AND v.pc_code = contract_pc.pc_code
WHERE i.cqe_numb = @.EST_NO
ORDER BY i.item_no|||Hi Brett,

It worked fine but it is not extracting the same rows as Oracle code.? Data is same...what is possible reason?

SELECT i.PC_CODE
, PC_TITLE
, i.DB_CONTRACT
, i.ITEM_NO
, DESCPT
, UNITS
, CONTQ
, CONTR_PRICE
, ISNULL(QTD,0) as qtd
, ind_ovr_und
, ISNULL(QTD,0) - ISNULL(QUANTITY,0) as QUANT_PREV
, ISNULL(QUANTITY,0) as quant_rev
, ROUND (ISNULL(quantity,0)*ISNULL(CONTR_PRICE,0),2) as QTD_VAL
, AMT_PAID_ITEM
, AMT_RET_ITEM
FROM VALID_ITEM as v
LEFT JOIN cqe_item as i
ON v.db_contract = i.db_contract
AND v.item_no = i.item_no
AND v.pc_code = i.pc_code
LEFT JOIN CONTRACT_PC as c
ON v.db_contract = c.db_contract
AND v.pc_code = c.pc_code
WHERE i.cqe_numb = 58 --gave the value
and i.db_contract =26999 --add one line
ORDER BY i.item_no|||Well you added another predicate...db_Contract_num...

But I don't know how you can have an outter join to a variable...

Like this

and cqe_item.cqe_numb (+) = :EST_NO|||Brett,

I got the problem...problem is with this line of code that you probably missed to translate i.e.

cqe_item.cqe_numb (+) = :EST_NO

I think you did not give solution of right join here? Am I right?

Thanks for you time..|||I guess you missed me pointing that out...

How do you have a right join to a variable?

What does that even mean?

An outter join is between tables

Is that meant to accomodate something like...

ISNULL(cqe_item.cqe_numb,@.EST_NO) = @.EST_NO

??????????????|||Hi Brett,

in table cqe_numb can be 1,2,3...scenerio is join on values ...am I right?
But this query is working perfect with oracle... :( ...:( ...

cqe_item.cqe_numb (+) = :EST_NO

SQL> desc cqe_item
Name Null? Type
---------- --- --
DB_CONTRACT NOT NULL NUMBER(6)
=======================================
CQE_NUMB NOT NULL NUMBER(3) ====right here
=======================================
PC_CODE NOT NULL NUMBER(2)
ITEM_NO NOT NULL VARCHAR2(7)
QUANTITY NUMBER(11,3)
AMT_PAID_ITEM NUMBER(11,2)
AMT_RET_ITEM NUMBER(10,2)
QTD_ITEM NUMBER(11,3)
FY_ITEM NUMBER(4)|||I have no idea...

Anyone heard of a right join to a variable?