Hi I need help

[mca.narender]

Hi,

I am converting one query from SQLServer to oracle i get some error related to Exists clause. what i need to do. Date related change i tried in oracle. all subquery return row and i got error on first line.

Thanks in advance

This is the SQL Server Code:

IF EXISTS(select uniqueid from contract where contract_id = 'AL00038900' and contract_year = 7)
BEGIN
IF NOT EXISTS
(
select uniqueid
from contract
where
contract_id = 'AL00038900' and
contract_year = 7 and
contract_name = 'Colonial Bank, N.A.' and
contract_start = '2006-03-01'
)
BEGIN
UPDATE contract
SET
contract_name = 'Colonial Bank, N.A.',
contract_start = '2006-03-01 00:00:00.000',
contract_end = '2007-02-28'
WHERE uniqueid =
(
select uniqueid
from contract
where contract_id = 'AL00038900' and contract_year = 7
)
END
END
ELSE
BEGIN
INSERT INTO
contract
(
contract_id, contract_year, contract_name, contract_start, contract_end
)
VALUES
(
@contract_id, @contract_year, @contract_name, @contract_start, @contract_end
)
END

[debasisdas]

I think what you are looking for is Merge statement.

http://psoug.org/reference/merge.html

[mca.narender]

Hi, Thanks for your response. I tried this but not worked, In this query i used only single table. i want to do this without help of procedure. Is ther any option to do that ?

[br_astronut]

Is this a one time procedure or something that will have to be done again and again? Where is the source data for the INSERTS coming from?