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?

ORACLE PROCEDURE VS. MS SQL SERVER PROCEDURE URGENT!

hi Guys !!
I hope you can help with this one...
I have to make a Procedure that can be able to receive and Array and process
inside itself..
In oracle you can declare a TABLE_TYPE and then you pass and array to it..
But how can i do that in MS SQL SERVER...?
I'll be really thanksfully
greetings
RemiHi
There is no direct equivalent in SQL Server 2000 as your table type can have
different datatypes of each element. You may want to look at passing a
cursor or using a temporary table, alternatively it may be possible to stuff
(overlay) them into a character or binary variable. The following will be
useful reading: http://www.sommarskog.se/arrays-in-sql.html
John
"Remi" <Remi1@.hotmail.com> wrote in message
news:elM0x3M5EHA.2804@.TK2MSFTNGP15.phx.gbl...
> hi Guys !!
> I hope you can help with this one...
> I have to make a Procedure that can be able to receive and Array and
> process
> inside itself..
> In oracle you can declare a TABLE_TYPE and then you pass and array to it..
> But how can i do that in MS SQL SERVER...?
> I'll be really thanksfully
> greetings
> Remi
>|||Try looking into SQL_VARIANT data type.
Sincerely,
Anthony Thomas
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uv9l2VN5EHA.1192@.tk2msftngp13.phx.gbl...
Hi
There is no direct equivalent in SQL Server 2000 as your table type can have
different datatypes of each element. You may want to look at passing a
cursor or using a temporary table, alternatively it may be possible to stuff
(overlay) them into a character or binary variable. The following will be
useful reading: http://www.sommarskog.se/arrays-in-sql.html
John
"Remi" <Remi1@.hotmail.com> wrote in message
news:elM0x3M5EHA.2804@.TK2MSFTNGP15.phx.gbl...
> hi Guys !!
> I hope you can help with this one...
> I have to make a Procedure that can be able to receive and Array and
> process
> inside itself..
> In oracle you can declare a TABLE_TYPE and then you pass and array to it..
> But how can i do that in MS SQL SERVER...?
> I'll be really thanksfully
> greetings
> Remi
>

ORACLE PROCEDURE VS. MS SQL SERVER PROCEDURE URGENT!

hi Guys !!
I hope you can help with this one...
I have to make a Procedure that can be able to receive and Array and process
inside itself..
In oracle you can declare a TABLE_TYPE and then you pass and array to it..
But how can i do that in MS SQL SERVER...?
I'll be really thanksfully
greetings
Remi
Hi
There is no direct equivalent in SQL Server 2000 as your table type can have
different datatypes of each element. You may want to look at passing a
cursor or using a temporary table, alternatively it may be possible to stuff
(overlay) them into a character or binary variable. The following will be
useful reading: http://www.sommarskog.se/arrays-in-sql.html
John
"Remi" <Remi1@.hotmail.com> wrote in message
news:elM0x3M5EHA.2804@.TK2MSFTNGP15.phx.gbl...
> hi Guys !!
> I hope you can help with this one...
> I have to make a Procedure that can be able to receive and Array and
> process
> inside itself..
> In oracle you can declare a TABLE_TYPE and then you pass and array to it..
> But how can i do that in MS SQL SERVER...?
> I'll be really thanksfully
> greetings
> Remi
>
|||Try looking into SQL_VARIANT data type.
Sincerely,
Anthony Thomas

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uv9l2VN5EHA.1192@.tk2msftngp13.phx.gbl...
Hi
There is no direct equivalent in SQL Server 2000 as your table type can have
different datatypes of each element. You may want to look at passing a
cursor or using a temporary table, alternatively it may be possible to stuff
(overlay) them into a character or binary variable. The following will be
useful reading: http://www.sommarskog.se/arrays-in-sql.html
John
"Remi" <Remi1@.hotmail.com> wrote in message
news:elM0x3M5EHA.2804@.TK2MSFTNGP15.phx.gbl...
> hi Guys !!
> I hope you can help with this one...
> I have to make a Procedure that can be able to receive and Array and
> process
> inside itself..
> In oracle you can declare a TABLE_TYPE and then you pass and array to it..
> But how can i do that in MS SQL SERVER...?
> I'll be really thanksfully
> greetings
> Remi
>