T-SQL splitting a column

  • 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

  • select transdate,case when amount > 0 then amount end as amountin,case when amount < 0 then amount end as amountout from payments

  • 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

  • Sorry, please consider his_amount as Amount. I mistyped.

  • 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?

  • Those were the fastest replies ever.

    Thank you very much Adam and Matt!!

  • 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

  • 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 Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply