Solved

SQL Query Results to new field based on criteria

Posted on 2009-07-07
8
251 Views
Last Modified: 2012-05-07
select d as dog, b as boy, 1 as [positive], 1 as [negative]
 where x

so in this example the result in positive and negative would be the same.

I need to add logic to query so that if query result is positive number then result will only show up in [positive] else if amount is negative it will only show up in new [negative] field in result set.
0
Comment
Question by:ftarvin
  • 4
  • 3
8 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 24798175
For example if x is your value, then you can do like this:
select d as dog, b as boy, case when x >= 0 then x end as [positive], case when x < 0 then x end as [negative]

Open in new window

0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 24798177
you need somme CASE expression
select dog, boy, value
, case when value > 0 then value end as positive
, case when value < 0 then value end as negative
from (
select d as dog, b as boy
  , (some_expression) as value 
 from sometable
 where x
) as sub_query

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24798203
For MySQL you can also use the IF construct:
SELECT IF(x>=0,x,NULL) AS [positive]
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:ftarvin
ID: 24798646
worked like a charm!

Thanks for the quick info.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24798658
ftarvin,

You are most welcome.

Happy coding!

Best regards,
Kevin
0
 

Author Comment

by:ftarvin
ID: 24798661
both experts provided same solution at the same time... split points?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24798675
Definitely appropriate. :)
0
 

Author Closing Comment

by:ftarvin
ID: 31600808
Thanks again boyz.. funny thing is I had tried that but missed a #$% comma from previous select field and kept thinking it was an issue with my case expression! sloppy!

Speak to you soon Hall of Famer's !
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question