convert in TSQL

Question

Post reply

convert in TSQL

newbieuser

SSCrazy Eights

Points: 9423
More actions
July 1, 2008 at 8:38 am

#190029

Posted - 07/01/2008 : 10:34:47
--------------------------------------------------------------------------------
Hi all, I created a view in oracle like below and will have to create the same in sqlserver database.
create or replace force view text_vw (itm,
sile,
eqty
)
as
select itm,sile,to_number(qty)
from text
where code in (2,3);
I used to_number in oracle to convert a column value from string to numeric. Please help on how I can do this in sqlserver.
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

DNA_DBA SSCrazy Eights Points: 9118 More actions · Answer 1

You can try cast. This example will transform a varchar to an int:

create view text_vw (itm, sile, eqty)

as

select itm,sile,cast(qty as int)

from text

where code in (2,3);

have a look at cast and convert in books online

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 2

Hi,

One input is You dont have Force keyword in SQL Server and if you want to override the existing view then you use ALTER statement.

Thanks -- Vj

http://dotnetvj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 3

Thanks, all. I tried the below view and it worked.

create or replace force view text_vw (itm,
sile,
eqty
)
as
select itm,sile,convert(int,qty)
from text
where code in (2,3);

But now, the application team wants to convert the qty in the view to (-) ve value only for code=3.

So, for the rows with code=3 and qty=8 in table text, the view should have eqty as -8.

Is it possible to do this using convert statement?

Thanks much.

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4552 More actions · Answer 4

You can use the case function for this as below:

alter view text_vw (itm, sile, eqty)

as

select itm,sile,

case code

when 3 then (convert(int,qty))*(-1)

else convert(int,qty)

end

from text

where code in (2,3);

Reminder:

or replace force

will not work in SQL Server

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 5

newbieuser

SSCrazy Eights

Points: 9423

July 16, 2008 at 8:34 am

#843814

Thanks very much. It worked.

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 6

Hi all,

I have the below view created:

create view text_vw (itm, sile, eqty)
as
select itm,sile,
case when code=3 then -1*convert(int,qty)
else convert(int,qty) end qty
from text
where code in (2,3);

Currently, the text.eqty is of varchar(8). The qty in the view converts the value to number. But, we would like to specify number(8,2) in the view because of application reasons. Is it possible to mention this in the view??

THanks a lot!!!!

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4552 More actions · Answer 7

create view text_vw (itm, sile, eqty)

as

select itm,sile,

case when code=3 then -1*cast(qty as numeric (8,2))

else cast(qty as numeric (8,2)) end qty

from text

where code in (2,3);

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 8

Thanks Ehsan. I tried the view but I'm getting this below error:

Arithmetic overflow error converting varchar to data type numeric.

I appreciate your help. Thank you.

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 9

I changed the numeric to (11,2) and it worked.

Thanks much.

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4552 More actions · Answer 10

Nice to know! Note that you will face the same error again if, for example the value in the field 'qty'is '1234567890'. You need to define the scale and precisoin accordingly in the view.

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]