ms sql query problem with a case statement

20 pts.
Tags:
CASE statement
MSSQL
SQL
[strong]This code works: SELECT     passengers.*, passship.passship_name AS name, passship.passship_speed AS speed, passship.passship_size AS cargo, hyperjump.hops AS hops, ROUND((passengers.pass_engers * passengers.pass_price) / (hyperjump.hops * passship.passship_speed), 0) AS ptprofit                        FROM         passengers INNER JOIN                       hyperjump ON passengers.pass_terms = hyperjump.startsystem AND passengers.pass_system = hyperjump.endsystem CROSS JOIN                       passship WHERE     (passship.passship_name = 'Rinkratt') ORDER BY ptprofit DESC, passengers.pass_terms, passengers.pass_term The problem is that sometimes [/strong][strong]passengers.pass_engers > passship.passship_size if it is I need to swap the [/strong][strong]passengers.pass_engers with passship.passship_size[/strong] [strong] I am trying this and getting errors on the > symbol SUM(CASE passengers.pass_engers > passship.passship_size THEN ROUND((passship.passship_size * passengers.pass_price)                       / (hyperjump.hops * passship.passship_speed), 0) ELSE ROUND((passengers.pass_engers * passengers.pass_price)                       / (hyperjump.hops * passship.passship_speed), 0) AS ptprofit) Can anyone tell me what is wrong with the sum or if there is a different/better way to do this?[/strong][strong][/strong][strong][/strong]

Software/Hardware used:
using MsSQL on windows server
ASKED: October 10, 2009  3:02 AM
UPDATED: October 18, 2009  5:25 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

I assume the problem is with parenthesis placing the “As” inside.

SUM(CASE
WHEN passengers.pass_engers > passship.passship_size
THEN ROUND((passship.passship_size * passengers.pass_price)
/ (hyperjump.hops * passship.passship_speed), 0)
ELSE ROUND((passengers.pass_engers * passengers.pass_price)
/ (hyperjump.hops * passship.passship_speed), 0)
END) AS ptprofit

I still get errors trying to use the SUM
This is what I ended up using and it worked:

CASE
WHEN passengers.pass_engers >= passship.passship_size
THEN ROUND((passship.passship_size * passengers.pass_price)
/ (hyperjump.hops * passship.passship_speed), 0)
WHEN passengers.pass_engers < passship.passship_size
THEN ROUND((passengers.pass_engers * passengers.pass_price)
/ (hyperjump.hops * passship.passship_speed), 0)
END AS ptprofit

I also changed the datatype from int to bigint

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following