Oracle Trigger...Plz Translat in SQL Server

Hi,

Can any , please, translate this oracle trigger in SQL Server

Any Help will be appreciated.

DECLARE
DB_ID NUMBER;
CQE NUMBER;
PC NUMBER;
item VARCHAR2(7);
AMT_PAID NUMBER(12,2);
AMT_RET NUMBER(12,2);
ITEM_NEW VARCHAR2(1);
quant number(12,3);
fiyr NUMBER;
BEGIN
/* Find the non null values to be used for propagating changes */
if :new.db_contract is not null then
db_id := :new.db_contract;
cqe := :new.cqe_numb;
pc := :new.pc_code;
item := :new.item_no;
fiyr := :new.fy_item;
else
db_id := :old.db_contract;
cqe := :old.cqe_numb;
pc := :old.pc_code;
item := :old.item_no;
fiyr := :old.fy_item;
end if;
amt_paid := nvl(:new.amt_paid_item,0) -nvl(:old.amt_paid_item,0);
amt_ret := nvl(:new.amt_ret_item,0) -nvl(:old.amt_ret_item,0);
quant := nvl(:new.quantity,0) -nvl(:old.quantity,0);
/* RAISE_APPLICATION_ERROR(-20501,'CHEK1 '||DB_ID||' CHEK2 '||PC
||' CHEK3 '||ITEM); */
SELECT NEW_ITEM INTO ITEM_NEW
FROM VALID_ITEM
WHERE DB_CONTRACT = DB_ID
AND PC_CODE = PC
AND ITEM_NO = ITEM;
update ae_contract
set amt_paid_contr = nvl(amt_paid_contr,0) + amt_paid,
amt_ret_contr = nvl(amt_ret_contr,0) + amt_ret
where db_contract = db_id;
if item_new = 'N' then
update vendor
set used_amt = nvl(used_amt,0) + amt_paid + amt_ret
where db_vendor = (select gen_contr from ae_contract
where ae_contract.db_contract=db_id);
end if;
update enc_det
set amt_paid_fy = nvl(amt_paid_fy,0) + amt_paid,
amt_ret_fy = nvl(amt_ret_fy,0) + amt_ret
where db_contract = db_id
and pc_code = pc
and fy = fiyr;
update valid_item
set tamt_ret_item = nvl(tamt_ret_item,0) + amt_ret,
tamt_paid_item = nvl(tamt_paid_item,0) + amt_paid,
qtd = nvl(qtd,0) + quant
where db_contract = db_id
and pc_code = pc
and item_no = item;
end;sorry i forgot to tell..in oracle it is row level trigger...

thanks

Oracle Translate function equivalent in SQL Server

Hi
I want to know the equivalent of the Oracle translate function in SQL Server.
eg : select translate('entertain', 'et', 'ab') from dual.
I tried the SQL Server Replace function , but it replaces only one character or a sequence of character and not each occurrence of each of the specified characters given in the second argument i.e 'et'.
Please let me know if there is some other equivalent function in SQL Server
thanks.

Hi,

no there is no quivalent for the translate function.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Well, it is "easy" enough to replicate. Easy being relative of course :) You could use the CLR in 2005, which would probably be my suggestion, but another way is to stack replaces and substrings.

I of course no nothing about Oracle, so I used the following as a guide:

http://www.techonthenet.com/oracle/functions/translate.php

--translate('1tech23', '123', '456); would return '4tech56'
--translate('222tech, '2ec', '3it'); would return '333tith'

declare @.value varchar(100)
declare @.replace varchar(3) --you could support many many more if you want
--I have nested 200+ replace commands and still get
--snappy enough results
declare @.replaceWith varchar(3)

set @.value = '1tech23'
set @.replace = '123'
set @.replaceWith = '456'

select replace(replace(replace(@.value,substring(@.replace,1,1),substring(@.replaceWith,1,1)),substring(@.replace,2,1),substring(@.replaceWith,2,1)),substring(@.replace,3,1),substring(@.replaceWith,3,1))

Your example was:

set @.value = 'entertain'
set @.replace = 'et'
set @.replaceWith = 'ab'

select replace(replace(replace(@.value,substring(@.replace,1,1),substring(@.replaceWith,1,1)),substring(@.replace,2,1),substring(@.replaceWith,2,1)),substring(@.replace,3,1),substring(@.replaceWith,3,1))

This returns:

anbarbain

If you aren't making heavy duty use of this, a T-SQL function will work to genericise it:

create function dbo.translate
(
@.value varchar(max),
@.replace varchar(3),
@.replaceWith varchar(3)
) returns varchar(max) as

begin
return (replace(replace(replace(@.value,substring(@.replace,1,1),substring(@.replaceWith,1,1)),substring(@.replace,2,1),substring(@.replaceWith,2,1)),substring(@.replace,3,1),substring(@.replaceWith,3,1)))
end
go

select dbo.translate('entertain', 'et','ab')
go

Just code as many replace/substring command pairs as you allow by the size of the replace parameter.

Oracle Translate function equivalent in SQL Server

Hi

I want to know the equivalent of the Oracle translate function in SQL Server.

eg : select translate('entertain', 'et', 'ab') from dual.

I tried the SQL Server Replace function , but it replaces only one character or a sequence of character and not each occurrence of each of the specified characters given in the second argument i.e 'et'.

Please let me know if there is some other equivalent function in SQL Server

thanks.CASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp) works wonders.

-PatP|||oh, i would just love to see how CASE works in this, er, um, case

care to share the example for TRANSLATE('entertain', 'et', 'ab'), pat?|||No. I'm tired, cranky, and trying to help. What are you doing to help?

-PatP|||What are you doing to help?subtly trying to inform the original poster that pursuing CASE may be a waste of time until he sees an actual example (which i am having a hard time conceiving)

i merely tried to match the degree of subtlety in my reply to yours

:)|||Maybe I am being a thicky pants but do nested REPLACEs not do the trick?|||thicky pants?

yes, that's the way i'd do it, as many nested REPLACE functions as characters to be translated

here's a classic example of TRANSLATE being used for simple encryption --

SELECT TRANSLATE(mycolumn,'abcdefghijklmnopqrstuvxyz', '5869413270plokij^#jm![edxc')|||Gotta love that Friday Feeling:
CREATE FUNCTION dbo.Thicky_Pants
(
@.Input AS VarChar(1000),
@.Find AS VarChar(100),
@.Replace AS VarChar(100)
)
RETURNS VarChar(1000)
AS
BEGIN

DECLARE @.i AS TinyInt

SELECT @.i = 1

WHILE @.i <= LEN(@.Find) BEGIN

SELECT @.Input = REPLACE(@.Input, SUBSTRING(@.Find, @.i, 1), SUBSTRING(@.Replace, @.i, 1))
SELECT @.i = @.i + 1

END
RETURN @.Input
END
GO

DECLARE @.String AS VarChar(1000)

SELECT @.String = 'pootle_flump'
SELECT @.String = dbo.Thicky_Pants(@.String, 'pt', 'xz')

PRINT @.String
xoozle_flumx|||here's a classic example of TRANSLATE being used for simple encryption --

SELECT TRANSLATE(mycolumn,'abcdefghijklmnopqrstuvxyz', '5869413270plokij^#jm![edxc')

Unless you are developing an application to write cryptograms for the Sunday paper, I hope nobody would use such an algorithm.

TRANSLATE is one of the goofier built-in functions in Oracle. The laser was once described as "a solution in search of a problem". Maybe someday there will be a practical use for TRANSLATE as well. In 10 years of SQL Server programming I've never found a need for such a function.|||In 10 years of SQL Server programming I've never found a need for such a function.well, there's your answer -- you need it all the time in oracle :)|||Somehow I've manager to get through a few Oracle projects without using it.