How to calculate columns in select statement

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

How to calculate columns in select statement

Author

Topic

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-11-11 : 18:05:24

In the code below if a = 1 then I want to multiple b,c,d by -1. How do I do this. I was thinking somehow to use the declare statement.


SELECT
a,
b,
c,
d
FROM X

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-11 : 18:24:23

SELECT a
, CASE WHEN a = 1 THEN b * -1 else b END AS b
, CASE WHEN a = 1 THEN c * -1 else c END AS c
, CASE WHEN a = 1 THEN d * -1 else d END AS d
FROM X

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

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-11-12 : 04:25:24

quote:
Originally posted by X002548

SELECT a
, CASE WHEN a = 1 THEN b * -1 else b END AS b
, CASE WHEN a = 1 THEN c * -1 else c END AS c
, CASE WHEN a = 1 THEN d * -1 else d END AS d
FROM X

Because this is repeated 3 times I was wondering if I could set a variable as true then I don't need to check it each time. Also I already have a case statement for each of those lines therefore I need to nest the cases.

For each of the 4 columns (b,c,d,e) I need to check this:

if a = 2 then * by -1
if a = 3 then null
if z <> 292 then get amount

I try to nest the case statements but get error message on the last ELSE. Any ideas?


CASE
   WHEN z <> 292
      THEN 
      CASE 
         WHEN a = 2
	    THEN ROUND(sil.SalesInvoiceLineAmount,2)) * -1
         WHEN a = 3
	    THEN NULL
      ELSE
	 ROUND(sil.SalesInvoiceLineAmount,2))
   ELSE 
      NULL 
END AS [Net None VAT]

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-11-12 : 04:33:39

I found it - i forgot to use the END statement in the nested CASE statement

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-12 : 10:08:31

[code]
CASE WHEN z <> 292 THEN
CASE WHEN a = 2 THEN ROUND(sil.SalesInvoiceLineAmount,2)) * -1
WHEN a = 3 THEN NULL
ELSE ROUND(sil.SalesInvoiceLineAmount,2))
ELSE NULL
END
END AS [Net None VAT]
[/code]

You would do well to make sure you format your code so it's more readable

And what happens when z = 292? You know you will get null

So how will you know when <> 292 & a = 3?

Also...column names with spaces...not a good idea

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

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-11-12 : 11:00:16

quote:

You would do well to make sure you format your code so it's more readable

And what happens when z = 292? You know you will get null

So how will you know when <> 292 & a = 3?

Also...column names with spaces...not a good idea

Hi,

I don't know how to format this type of code. C# is easy to format whereas to me this isn't. What I did seemed OK to me.
If z = 292 then the cell should read as null.
What do you mean column names shouldn't have spaces? None of mine have spaces?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-12 : 11:16:03

I think Brett is refering to your aliasing to [Net None VAT]:
END AS [Net None VAT]

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-11-12 : 11:24:50

quote:
Originally posted by Lamprey

I think Brett is refering to your aliasing to [Net None VAT]:
END AS [Net None VAT]

Ah OK, This is the column header in the report. Maybe I can delete the spaces and rename it in reporting services...That's my next task.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources