Solved

SQL Query Results to new field based on criteria

Posted on 2009-07-07
8
249 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
 

Author Comment

by:ftarvin
ID: 24798646
worked like a charm!

Thanks for the quick info.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Mysql Crashing Intermittently 16 82
sql query help 2 45
SQL Server 2008 R2 - Sums/Grouping 7 51
MySqlDump not dumping triggers 1 17
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now