Case Statement help

I am trying to create columns from calculations. Essentially what I want is
this:
Column1 = A then (Column2 - Column3) as ActualAmount else
Column1 = B then (Column2 - Column3) as BudgetAmount
Maybe I am not thinking straight, but this seems like it should be simple.
Here is what I started with:
Case When GLBA.ACTUAL_FLAG = 'A' Then
GLBA.QUARTER_TO_DATE_DR - GLBA.QUARTER_TO_DATE_CR as
ActQtrBalance,
GLBA.BEGIN_BALANCE_DR - GLBA.BEGIN_BALANCE_CR as ActBeginBalance,
GLBA.PERIOD_NET_DR - GLBA.PERIOD_NET_CR as ActPeriodNet,
GLBA.BEGIN_BALANCE_DR - GLBA.BEGIN_BALANCE_CR + GLBA.
PERIOD_NET_DR - GLBA.PERIOD_NET_CR as ActEndBalance
Else
GLBA.QUARTER_TO_DATE_DR - GLBA.QUARTER_TO_DATE_CR as
BudQtrBalance,
GLBA.BEGIN_BALANCE_DR - GLBA.BEGIN_BALANCE_CR as BudBeginBalance,
GLBA.PERIOD_NET_DR - GLBA.PERIOD_NET_CR as BudPeriodNet,
GLBA.BEGIN_BALANCE_DR - GLBA.BEGIN_BALANCE_CR + GLBA.
PERIOD_NET_DR - GLBA.PERIOD_NET_CR as BudEndBalance
End
I thought Decode was the way to go but can't seem to make that work either.
Please and Thank you
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200510/1David,
Use as an example - listing two columns because you have two column aliases
can account for NULLs if required.
Try:
CREATE TABLE TESTTABLE300
(COLUMN1 CHAR(1) NOT NULL,
COLUMN2 INT NOT NULL,
COLUMN3 INT NOT NULL)
INSERT TESTTABLE300
VALUES('A',1000,300)
INSERT TESTTABLE300
VALUES('B',1000,600)
SELECT COLUMN1,
CASE COLUMN1 WHEN 'A' THEN (Column2 - Column3) END as ActualAmount,
CASE COLUMN1 WHEN 'B' THEN (Column2 - Column3) END as BudgetAmount
FROM TESTTABLE300
--DROP TABLE TESTTABLE300
HTH
Jerry
"David P via webservertalk.com" <u12188@.uwe> wrote in message
news:5653e494447eb@.uwe...
>I am trying to create columns from calculations. Essentially what I want is
> this:
> Column1 = A then (Column2 - Column3) as ActualAmount else
> Column1 = B then (Column2 - Column3) as BudgetAmount
> Maybe I am not thinking straight, but this seems like it should be simple.
> Here is what I started with:
> Case When GLBA.ACTUAL_FLAG = 'A' Then
> GLBA.QUARTER_TO_DATE_DR - GLBA.QUARTER_TO_DATE_CR as
> ActQtrBalance,
> GLBA.BEGIN_BALANCE_DR - GLBA.BEGIN_BALANCE_CR as
> ActBeginBalance,
> GLBA.PERIOD_NET_DR - GLBA.PERIOD_NET_CR as ActPeriodNet,
> GLBA.BEGIN_BALANCE_DR - GLBA.BEGIN_BALANCE_CR + GLBA.
> PERIOD_NET_DR - GLBA.PERIOD_NET_CR as ActEndBalance
> Else
> GLBA.QUARTER_TO_DATE_DR - GLBA.QUARTER_TO_DATE_CR as
> BudQtrBalance,
> GLBA.BEGIN_BALANCE_DR - GLBA.BEGIN_BALANCE_CR as
> BudBeginBalance,
> GLBA.PERIOD_NET_DR - GLBA.PERIOD_NET_CR as BudPeriodNet,
> GLBA.BEGIN_BALANCE_DR - GLBA.BEGIN_BALANCE_CR + GLBA.
> PERIOD_NET_DR - GLBA.PERIOD_NET_CR as BudEndBalance
> End
> I thought Decode was the way to go but can't seem to make that work
> either.
> Please and Thank you
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200510/1|||So I just need to create a CASE statement for each calculation.
I'll give that a try.
Jerry Spivey wrote:
>David,
>Use as an example - listing two columns because you have two column aliases
>can account for NULLs if required.
>Try:
>CREATE TABLE TESTTABLE300
>(COLUMN1 CHAR(1) NOT NULL,
> COLUMN2 INT NOT NULL,
> COLUMN3 INT NOT NULL)
>INSERT TESTTABLE300
>VALUES('A',1000,300)
>INSERT TESTTABLE300
>VALUES('B',1000,600)
>SELECT COLUMN1,
> CASE COLUMN1 WHEN 'A' THEN (Column2 - Column3) END as ActualAmount,
> CASE COLUMN1 WHEN 'B' THEN (Column2 - Column3) END as BudgetAmount
>FROM TESTTABLE300
>--DROP TABLE TESTTABLE300
>HTH
>Jerry
>[quoted text clipped - 28 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200510/1