How To Translate Oracle Decode

Aug 2, 2006

Hi,

How to translate oracle Decode without changed code I mean:

I have one application and instead to change all decode to case when I would like just replace decode for dbo.decode, so I

Wrote this function

select dbo.fnDecode( 1 , 2 , 3 )

first parameter always int, and the others parameters could be char, int or float.

I would like to test first value and return second or third value

-> sql_variant for all parameters, ok

but I can use + or -

I can't do this

select dbo.fnDecode( 1 , 2 , 3 ) + dbo.fnDecode( 1 , 2 , 3 )

If I put cast ok, but I is there another way, overload this call?

With clr doesn't work, because Sql Server doesn't accept function overload calls from C#



Any ideia????????????????????

cheers,

View 4 Replies


ADVERTISEMENT

Equallent To Decode In Oracle?

Jun 21, 2004

Hi, Here is my oracle statement. How can I change it to be compatible with SQL Server?

update propertytable set visible =decode(propertyid, 1,0, 2,0, 3,1, 5,1, 6,1, 7,0, 9,1, 10,1, 11,0, 14,1, 30,1, 38,1, 60,0, 232,0, 233,0, 415,1, 605,0) where parentid between 2000006001 and 2000006020

Thanks...

View 10 Replies View Related

Oracle Translate Function Equivalent In SQL Server

May 5, 2006

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.

View 14 Replies View Related

Oracle Translate Function Equivalent In SQL Server

May 5, 2006

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.

View 4 Replies View Related

Outer Join Oracle Query Translate In Sql Server

Apr 16, 2004

Hi,
I have this oracle query with outer join situation. how can i convert it into sql server query.

SELECT distinct ae.dB_CONTRACT,CP.PC_CODE,BID_ITEM.ITEM_NO,
'N',BID_ITEM.PRICE_WORDS,OFF_ITEM.DESCPT,
OFF_ITEM.UNITS,OFF_ITEM.TYPE_ITEM,
PRES_ITEM.RET_PERC
FROM BID_TOTAL,BID_ITEM,OFF_ITEM,PRES_ITEM, AE_CONTRACT AE, CONTRACT_PC CP
WHERE RANK_NUMB = 1
AND BID_TOTAL.DB_CONTRACT = 37044
AND BID_TOTAL.DB_CONTRACT = BID_ITEM.DB_CONTRACT
AND BID_TOTAL.BID_VENDOR = BID_ITEM.BID_VENDOR
AND BID_ITEM.DB_CONTRACT = OFF_ITEM.DB_CONTRACT
AND BID_ITEM.ITEM_NO = OFF_ITEM.ITEM_NO
AND OFF_ITEM.ITEM_NO = PRES_ITEM.ITEM_NO (+)
AND AE.DB_CONTRACT=BID_TOTAL.DB_cONTRACT
AND CP.DB_CONTRACT = AE.DB_CONTRACT
AND CP.pc_code = 1

Any Help will be appreciated.

View 3 Replies View Related

A Function Smilar To DECODE Function In Oracle

Oct 19, 2004

I need to know how can i incoporate the functionality of DECODE function like the one in ORACLE in mSSQL..
please if anyone can help me out...


ali

View 1 Replies View Related

DECODE In MS SQL

Jan 12, 2004

In Oracle we have expression

SELECT
.....
DECODE (T.LANGUAGE, 'E', T.DESC_ENG, T.DESC)
FROM
TABLE T

if acts like that :
if T.LANGUAGE = 'E' it returns T.DESC_ENG. If it is different - T.DESC.


Is there some way to make the same in MS SQL Server ?

View 10 Replies View Related

DECODE?

Jul 20, 2005

I have to run a query to give a column a value based on a time range. Can Iuse DECODE?select decode(trans_date, trans_date>='01-Jul-2002' andtrans_date<='30-Jun-2003','Fiscal2002', .....) as fiscal,from. . .where. . .

View 1 Replies View Related

Decode Function

Aug 28, 2001

HI,
Is there any way to replicate the decode function in SQL Server 7.0? I have a problem where I have to write a query such that if the value in a column is '0' , I need to output it as 'No' and if it is '1', I need to output it as 'Yes'. An example of my problem is as follows:

select col_1+","+(value 'No' if col_2='0' and 'Yes' if col_2='1')+
","+col_3+...........

I appreciate your help in advance.
thanks,
Sravan.

View 3 Replies View Related

Decode In SQL Server

Jun 4, 2004

Hi

I have oracle statement and I want to translate it in SQL Server:

select DECODE(count(bid_Vendor), 1, NULL, COUNT(BID_VENDOR))
from bid_total

I tried it this way:
Select case(count(bid_vendor) when 1 then null else count(bid_vendor)end) as cs from bid_total

It gave me error i.e.

Incorrect syntax near the keyword 'when'

View 1 Replies View Related

Case? Decode?

Jun 8, 2008

Hello,

I need to do the following:

Lets say we have the following table.

A B C
1 XYZ XYZ
100

Now if the value of a is "100" then i need to do a join and get a value from another table, else I put the value of b a "test"

I am trying to do this using case

like this

select case
(when a = 100 then (select col from ....where newtable.id = a.id
else "test"
end) as xyz
from a,b,other tables.

But the problem is that the subquery returns more than one row..(it should return one row)

How do I do this?

View 1 Replies View Related

Alternate To Decode

Sep 30, 2006

hi i have a view in oracle. in that i am using decode function.
same query i want to write it in sqlserver. what it is the alternate
to decode.

this is a cross tab query

SELECT code, SUM(DECODE(field1, 4, Present_Value, 0)) AS c1, SUM(DECODE(field1, 5, Present_Value, 0)) AS c2,
SUM(DECODE(field1, 6, Present_Value, 0)) AS c3,SUM(DECODE(field1, 9, Present_Value, 0)) AS c4
FROM (SELECT field1,Code, Present_Value FROM table1) DERIVEDTBL GROUP BY code

thanks

suji

View 8 Replies View Related

GROUP BY On DECODE

May 14, 2007

Hi,I have two questions, firstly..
I am hoping there is a way to GROUP BY on a concatenated output like the following decode etc..

SELECT
DECODE(PRIM_REASN,'','Reasons not entered',PRIM_REASN)
|| ' '
||SECDY_REASN
|| ' '
|| TERT_REASN) Downtime_Reasons,
EVENT_DUR/60 Minutes

FROM
DOWNTIME_WEB
WHERE
DOWNTIME_WEB.TIMESTAMP>= (SELECT PRODN_SHIFT.START_DATIME
FROM PRODN_SHIFT
WHERE
PRODN_SHIFT.START_DATIME <=sysdate
AND PRODN_SHIFT.END_DATIME > sysdate)
AND
TRIM(DOWNTIME_WEB.STATUS_TEXT) = 'STOPPED'
AND
TRIM(DOWNTIME_WEB.WORK_CTR_NAME) = 'PACK2'
ORDER BY DOWNTIME_WEB.EVENT_DUR

I have tried to put the DECODE etc into the GROUP BY statement but I get an error - " not a valid GROUP BY clause"

Secondly, is there a way of modifying value in field A based on the value in field B

Erroneous example follows - in an effort to explain what I want to do (from above):

DECODE(PRIM_REASN,'',(DECODE(EVENT_DUR,<200,"Low",>200,"High"),PRIM_REASN)
ie:If PRIM_REASN is null I want EVENT_DUR to be tested to determine the new value for PRIM_REASN.
Except that tthe above won't work and I don't know what the right function/argument should be..

This is my first foray into SQL server, I am trying to migrate some access queries to work with web services and hence Xcelsius.


Cheers.

View 4 Replies View Related

DECODE In SQL Server?

Jul 20, 2005

What is the function in SQL that works like DECODE in Oracle?"Thanks,N

View 10 Replies View Related

Decode Funktion

Jan 23, 2006

Hi!



I have a question about the "Decode" funktion. is it only avaiable in Oracle? The reason for my question is that I need to SELECT a colum in a table based on a int value. So if the uservalue is lower then that take that colum, lower then that take colum...etc.

Can I use decode? or/and is there better funktion (I'm using MS SQL 2005)

Thanks in advance

Mark

View 6 Replies View Related

Urgent!!!!!!!! Decode Function In Sql

Dec 11, 2007

 
how can I write decode function in sql
such that i have a feild in my table called "file type" based on its content i want to pass a value to sql datasource
such that        if file type = doc i want to pass "~/Images/ico_filetype_10.gif" an so on
 

View 1 Replies View Related

What Is The DECODE Statement In SQLSERVER?

Mar 27, 2001

I need to know what is the DECODE statement called in SQLSERVER?

Can somebody provide me simple decode statement for sqlserver.

Thanks,
Ranjan

View 1 Replies View Related

How To Decode In SQL Server 2005

Mar 6, 2008

I am a novice to SQL Server, so this is probably a really easy problemto fix. I'm translating an Oracle query and need to change the'decode' to something compatible. Everything I've read points me tousing 'case' but no matter how I write it I can't get it to work andget a syntax error. Suggestions?select SYST CTR, isnull(substring(CD_A, 1, 3), ' ') RESCODE, DES DESCRIPTION, decode (substring(CD_A, 1, 3), CODE,PRICE,0) UNIT_PRICE, count (distinct OR_NO) QTYW

View 11 Replies View Related

Decode Base64 To Image!

Jun 7, 2006

Hi,

I have a xml string which is consist of some images encoded in base64; I have to extract these images in a stored procedure and save them in some tables.

The problem is that I can't decode this base64s to images. Is there a way to do it? (I use SQL Server 2005 Enterprise)

Thanx

View 5 Replies View Related

Translate Function

Nov 8, 2007

Can i use Translate function in sql server query? How i ll use? In my oracle, i m using a Translate function, but i want to run that in sql server. How to do that?

View 3 Replies View Related

Translate Function?

Jul 20, 2005

Hi allIs there a TSQL function like Oracle's Translate function? Where:Translate('13,000Miles','abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP QRSTUVWXYZ, ',' ') wouldreturn '13000'?I'm trying to extract a number from a string. REPLACE doesn't quite dowhat I want.Thanks

View 1 Replies View Related

How To Translate Into SSIS??

Mar 12, 2007

Hi everyone,

My current dutie is translate Vb code into SSIS. The following structure is getting me totally crazy:

sql = "SELECT * FROM TABLE1 WHERE FIELD = XXXX"
rs.execute sql
WHILE NOT RS.EOF
STUFF
ANOTHER SELECT AND ANOTHER LOOP
...
...

INSERT AND DELETE STUFF


END WHILE

Which is the best method in order to reach this goal? I'm trying to by means of OLEDB Source Editor connected to Script Component Task on data flow layer but I'm stuck with this.

I don't want to do cursors or something like that with T-SQL.

Thanks in advance for your inputs/help and regards,

View 8 Replies View Related

Can I Use Translate In Sql Server,is It A Keyword

Oct 3, 2006

im trying to find the equivalent of the below oracle code in sql server.But to my knowledge i feel "translate "is not supported by sql server. so please send me the equivalent sql server query for this.SELECT 'http://ploft.com/ploft?y='||translate(comp,' ','+')||',+'||translate(california,'','+')||',+'||ca||'+'||pzINTO :ls_MAP FROM project WHERE portno=:dbno;

View 3 Replies View Related

Translate Numbers To Words?

Aug 30, 2013

In the below query i need to display the amount in words also. How to do that in sql server 2008 R2?

SELECT ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS[TOTAL_CONCESSION_GIVEN] FROM BILL_MASTER BM

View 3 Replies View Related

Translate Sql Statement Into SQL-92 Standards

Jul 20, 2005

Can anyone help me translate this statement from using the legacyouter joins to the SQL-92 standards?Select CA.* From Customer C, Shipper S, Customer_Order CO,Cust_Address CA Where CA.Customer_ID =* CO.Customer_IDand CA.Addr_No =* isnull(S.Ship_To_Addr_No,CO.Ship_To_Addr_No)and C.ID = CO.Customer_IDand (S.Shipped_Date between '1/1/2003' and '12/31/2003')Try as I may, I simply can't find a working left, right, or full outerjoin statement that would give me the same results as the abovestatement gives. I thought this was suppose to work but don't knowwhy it doesn't. Anybody care to try or perhaps tell me why thestatement below doesn't work:Select CA.* From Customer C, ((Customer_Order CO left outer joinCust_Address CA on CA.Customer_ID = CO.Customer_ID) left outer joinShipper S on CA.Addr_No =isnull(S.Ship_To_Addr_No,CO.Ship_To_Addr_No)) Where C.ID =CO.Customer_IDand (S.Shipped_Date between '1/1/2003' and '12/31/2003')Thanks,Tony

View 4 Replies View Related

How To Translate Varchar Into Varbinary?

Apr 3, 2007

Hi everyone,



We're trying to migrate a varchar field from Sql2k to varbinary in a sql25k through a dtsx package. We get an error which tell us: "data will be lost".



Does anyone have any idea about that?



Thanks for your time and inputs,

View 1 Replies View Related

How To Translate DTSStepScriptResult_DontExecuteTask Into SSIS?

Mar 15, 2007

Hi everyone,

We're struggling ourselves with this and we are stuck.



Which is the equivalent for a SSIS in a Script Task component on CONTROL FLOW layer?

Main = DTSStepScriptResult_ExecuteTask


Thanks for your input and regards,

View 4 Replies View Related

How To Internationalize And Translate RDL Files

Aug 10, 2007

Hello,

I€™m new to this forum and my knowledge about RDL is very limited. We are developing software localization tools and some of our customers have requested a solution to translate RDL files.

Our tool has a generic XML parser so I reviewed the RDL documentation but didn€™t found any information about which XML elements do have translatable content. If there anyone who knows this or is there any documentation around describing which elements must be translated?

I also saw some proposals regarding the internationalization of RDL files. It seems that the proposed solutions were designed for developers and not for professional translators. In an environment where you have to translate several hundreds of RDL files into let€™s say 20 languages you can€™t expect that translators start to code RDL files. Are there any other approaches that can be used for a more efficient translation of RDL files?

Any feedback is appreciated.


Achim Herrmann

View 1 Replies View Related

Translate DSTX File

Dec 4, 2007

Is it possible to translate a DTSX/XML file into VB.Net or C# code, like you can do with a WSDL file? If so, is there a SSIS utility or third party available? Thank you.

JGL

View 4 Replies View Related

Translate Equivalent In Sql Sever

Apr 24, 2008



Hi,

I have an alphanumeic string as one of the column values.I want to get numeic value out of the string.
I was planning on translating all the characters of the alphanumeric sting to spaces and then using ltrim and rtrim on it should give me the required numeric sring.But there is no translate function in sql server

Example: abcd123xy 123
xyz45 45

Can someone provide me a way to get the numeric value out of the strng.


Thanks
Bobby

View 7 Replies View Related

Translate Rows To Columns - SQL2000

Apr 30, 2008

Hi

I want to translate the following



SERIALNO TYPE AMOUNT
1111 1 251111 1 5 1111 1 7 1111 1 3 1111 1 101111 3 1 1111 3 2 1111 3 4 1111 3 3
2222 0 1
2222 0 7
2222 0 1
2222 1 5
2222 1 1
2222 1 2
2222 1 3





to output in the following format


SERIALNO SUM of MIN SUM of MAX
1111 50 10
2222 9 11
For each SERIALNO I want to sum the AMOUNT, spliting the columns into 2 based on TYPE
There are only ever 2 values for TYPE for eachs serial no.
I don;t need to track the value of TYPE.
I assume you would use a MIN & MAX function but Im not sure how to write this into a view.

thanks...

View 11 Replies View Related

Analysis :: Translate Named Sets

May 19, 2011

I have some Named Sets that I want to translate. I have done exactly the same way as with the Calculated Members but the Sets don´t use the translations? Any ide why?

View 2 Replies View Related

How To Compile/translate A SSIS Package In Japanese

Jan 16, 2007

Hi,

I have a requirement to do a data migration work for a Japanese client. The problem is I don't know Japanese and the client SME does not know English. So, my requirement is, I should be able to create a SSIS package in English and then should be able to compile or translate it into Japanese so that my Japanese SME can understand the package. Is it possible to do? If so, how can I do it?

Thanks in advance.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved