Decode - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Decode

Author

Topic

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2008-07-15 : 16:17:34

Hi all,

Below is the view, I need some help with.


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

THe view works fine.But 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.

cvipin
Yak Posting Veteran

51 Posts

Posted - 2008-07-15 : 16:22:53

You can use case statement.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-15 : 16:24:59

is that not an oracle view? you should be aware this forum is for Microsoft SQL Server

Em

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2008-07-15 : 16:28:14

Sorry below is the view I created in SQLserver

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

Will I be able to use case statement to convert qty to int and also make the qty in (-) for code=3? Thanks

cvipin
Yak Posting Veteran

51 Posts

Posted - 2008-07-15 : 16:30:54

select itm,sile, convert(int, CASE WHEN code = 3 Then -qty Else qty End) qty
from text where code in (2,3)

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2008-07-15 : 16:54:02

I tried this


create view text_vw (itm,
                     sile,
                     eqty
                     )
                                       
as         
select itm,sile,convert(int, CASE WHEN code = 3 Then -qty Else qty End) qty
from text
where code in (2,3);

I am getting the error message :

Operand data type varchar is invalid for minus operator

Please help.

cvipin
Yak Posting Veteran

51 Posts

Posted - 2008-07-15 : 17:05:16

Try this:

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)

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2008-07-15 : 17:39:39

THanks a lot.

Just one last question, will this impact database performance by any chance? THanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 17:51:16