November 1, 2007 at 1:59 pm
Hi everyone,
I have an [Amount] field, and a [TransDate] field in the Payments table.
[Amount] field holds both negative and positive values.
Without using self referencing join, I want to create a view which splits [Amount] field into [AmountIn] for positive values [AmountOut] for negative values.
TABLE
Amount.......................TransDate
100.00........................1/10/2007
-100.00.......................2/10/2007
VIEW
AmountIn.............AmountOut..............TransDate
100.00......................null.....................1/10/2007
null..........................-100.00................2/10/2007
Anyone can help me?
Thanks
November 1, 2007 at 2:04 pm
select transdate,case when amount > 0 then amount end as amountin,case when amount < 0 then amount end as amountout from payments
November 1, 2007 at 2:19 pm
Here is my code:
------
CREATE VIEW
AS
SELECT
CASE WHEN Amount > 0 THEN his_amount ELSE '' END as [AmountIn],
CASE WHEN Amount < 0 THEN his_amount ELSE '' END as [AmountOut],
TransDate
FROM Payments
------
Error:
Server: Msg 257, Level 16, State 51, Line 1
Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query.
Is there any way to work with money datatype in the case statement?
Thanks everyone
November 1, 2007 at 2:21 pm
Sorry, please consider his_amount as Amount. I mistyped.
November 1, 2007 at 2:22 pm
The issue is that you're throwing a varchar into the ELSE clause (''). That won't implicit convert to a MONEY value. So - switch the ELSE portion to give back a number.
CREATE VIEW
AS
SELECT
CASE WHEN Amount > 0 THEN his_amount ELSE 0 END as [AmountIn],
CASE WHEN Amount < 0 THEN his_amount ELSE 0 END as [AmountOut],
TransDate
FROM Payments
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 1, 2007 at 2:25 pm
Those were the fastest replies ever.
Thank you very much Adam and Matt!!
November 1, 2007 at 5:28 pm
Now, I need also [Balance] field in the view. Since [AmountIn] and [AmountOut] are not a physical fields, I cannot do something like this:
Can anyone help me with this?
SELECT
CASE WHEN Amount > 0 THEN Amount ELSE 0 END as [AmountIn],
CASE WHEN Amount < 0 THEN Amount ELSE 0 END as [AmountOut],[AmountIn] - [AmountOut] as [BALANCE],
TransDate
FROM Payments
---------
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Amount In'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Amount In'.
Thanks
November 2, 2007 at 7:08 am
day (11/1/2007)
Now, I need also [Balance] field in the view. Since [AmountIn] and [AmountOut] are not a physical fields, I cannot do something like this:Can anyone help me with this?
SELECT
CASE WHEN Amount > 0 THEN Amount ELSE 0 END as [AmountIn],
CASE WHEN Amount < 0 THEN Amount ELSE 0 END as [AmountOut],[AmountIn] - [AmountOut] as [BALANCE],
TransDate
FROM Payments
---------
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Amount In'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Amount In'.
Thanks
Queries process in a specific order. You're generating aliases and then trying to reference them in the same statement. I'd try this CTE:
WITH AmountCalc AS (
SELECT
CASE WHEN Amount > 0 THEN Amount ELSE 0 END as [AmountIn],
CASE WHEN Amount < 0 THEN Amount ELSE 0 END as [AmountOut],
TransDate
FROM whatever)
SELECT AmountCalc.AmountIn,
AmountCalc.AmountOut,
[AmountIn] - [AmountOut] as [BALANCE],
TransDate
FROM AmountCalc
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply