Convert Query result to negative number

Question

Post reply

Convert Query result to negative number

Lana Underwood

Old Hand

Points: 338
More actions
February 23, 2005 at 12:55 am

#163779

Is there a way to get query results to display as negative numbers based on criteria matching? For example, I have a table that has two particular fields I need to retrieve data from: RecordType and Quantity_Ord. Everything in Quantity_Ord is listed as a positive number. However, if RecordType = 20 (meaning the product was returned), I need the data in Quantity_Ord to be listed as a negative number instead. (I didn't set the table up, so I have no control over how the data is stored).
And to complicate things: I need to see all records, not just those where RecordType = 20.
Thanks!!!

Viewing 7 posts - 1 through 6 (of 6 total)

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

Helen-84411 Hall of Fame Points: 3320 More actions · Answer 1

Hai,

Try this

Create table Qty

(REcordType int null,

Quantity_Ord int null)

Insert into Qty values (20,100)

Insert into Qty values (21,100)

select RecordType, Quantity_Ord = case when REcordType <> 20 then Quantity_Ord else Quantity_Ord * -1 end

from qty

Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me

Antares686 SSC Guru Points: 125444 More actions · Answer 2

The query engine performs better when using = values as opposed to != or <> so rewtire the previosu like so.

select RecordType, Quantity_Ord = case when RecordType = 20 then Quantity_Ord * -1 else Quantity_Ord end

from qty

As well if this is something you need to do regulary you might want to create a function with 3 inputs to do the logic of the case.

Lana Underwood Old Hand Points: 338 More actions · Answer 3

Is there any way to do this without creating a new table? I apologize for not giving the whole picture . . . The company that provided our software (Aspen Sytems) created the table structure and simply gave us a SQL query builder that does not give you the option to create additional tables. You can use many of the functions from SQL (i.e., Expression, Condition, Avg, Like, etc.) but not a full-function query analyzer.

Therefore, I am trying to get around this particular problem. I have access to query analyzer, but I am trying to set up reports for the users within the software that will show product usage and the numbers are not calculating properly because I cannot convert the particular fields with Record Type of 20 to negative numbers.

Thanks for your reply!!!

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 4

Lana,

Try this approach:

SELECT (FieldName * -1) FROM TableName

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 5

Let's retry that last one so it actually helps:

SELECT RecordType, CASE WHEN RecordType = 20 THEN (Quantity_Ord * -1)

ELSE Quantity_Ord END AS Quantity_Ordered

FROM TABLENAME

Lana Underwood Old Hand Points: 338 More actions · Answer 6

Awesome example -- works great. Thanks for your help!!