flag - 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

flag

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-07-03 : 12:50:05

hi all!

i have columns.
1. Flag : stores only + / - / <NULL>
2. Number : stores float

how would i select this 2 columns to generate -ve and +ve number depend on the flag? eg :
Flag : -
Number : 4 ====> -4 and +4 if flag=+

when i try to just to combine with -(Flag) for -ve, it gave converting type error even i already CAST it..

tq in advance

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

sshelper
Posting Yak Master

216 Posts

Posted - 2007-07-03 : 12:52:28

One option is to use a CASE statement to generate the output:

SELECT CASE WHEN Flag = '-' THEN -1 ELSE 1 END * YourNumber FROM YourTable

SQL Server Helper
http://www.sql-server-helper.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-03 : 12:53:21

Why have you chosen this design? Float can easily store negative values.
Store complete value in Number and use Flag as an reminder of what sign Number has, with the SIGN function.

Yikes.

Peter Larsson
Helsingborg, Sweden

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-07-03 : 13:00:24

THAT"S Why! unfortunately this tbl has to be leave intact. so how? temp table?

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-03 : 13:08:13

What result should you get when Flag is NULL?

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-03 : 13:09:29

select isnull(CAST(flag as varchar(1)), '+') + CAST(YourNumber as varchar(10)) as formated
FROM YourTable

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

RocketScientist
Official SQLTeam Chef

85 Posts

Posted - 2007-07-03 : 13:12:59

select
case
when flag is null then 1
when flag = '+' then 1
else -1
end * number
from <table>

that assumes you want NULL and + to be treated the same. You'll want to mess with the defaults (yeah, it's suposed to be + - or null, but what if it isn't).

A better way to store the data is to just sign the number. A second best would be to store -1 or 1 in the sign field that way it's a simple multiplication problem.

X002548
Not Just a Number

15586 Posts

Posted - 2007-07-03 : 13:44:01

Better still would be to not use a flag in the first place

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-07-03 : 18:42:58

whoa. i guess you can be glad there's not a separate column for each digit of the number...

elsasoft.org

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-07-03 : 20:46:39

hi spirit..thanks
so i CAST to varchar first and then CAST to FLOAT, right?

rocket & sshelper ----> yeaa.. i did the same thing before that gave me converting problem in my crystal report...

thanks all

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-07-03 : 21:20:11

okey spirit.. CAST in CAST = go casting... :D
well.. this one works finally

select CAST(isnull(CAST(adjFlag as varchar(1)), '+') + CAST(adjQty as varchar(10)) as float) as formated
from tblstkadjdetail
tq

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources