Thanks again Jeff.

--
Ray Briggs II
Lone Star Medical Products, Inc.
xxx-xxx-xxxx  Ext. 148
mailto:ray@l...


"Jeff Mason" <je.mason@a...> wrote in message
news:233179@s..._language...
>
> That's the way to do it.
>
> You might simplify the CASE expressions a bit; since the tests are
inclusive
> of all values you can make use of the 'else' clause instead of repeating
the
> (opposite) comparison, as:
>
> select ar_invoice,
> case
> when ar_amount<0 then ar_amount
> else 0.00
> end as CREDIT,
> case
> when ar_amount>0 then ar_amount
> else 0.00
> end as PAYMENT
> from ar;
>
>
> --
> Jeff Mason              Custom Apps, Inc.
> Jeff@c...
>
>
> -----Original Message-----
> From: Ray [mailto:ray@l...]
> Sent: Friday, November 08, 2002 11:19 AM
> To: sql language
> Subject: [sql_language] Better way to seperate column
>
>
> Here is my original table
> INVOICE   AMOUNT
> 12345     100.00
> 12345   -100.00
> 84253     300.00
> 84253     -175.00
> 84253     -125.00
>
> And I want to seperate the amount into two seperate columns. One with
> payments and one with credits.
>
> INVOICE   CREDIT    PAYMENT
> 12345     100.00     0.00
> 12345   0.00      -100.00
> 84253     300.00     0.00
> 84253     0.00      -175.00
> 84253     0.00      -125.00
>
> The following SQL will do it but I am wondering if there is a better way.
>
> select ar_invoice,
> case
> when ar_amount<0 then ar_amount
> when ar_amount>=0 then 0.00 end,
> case
> when ar_amount<=0 then 0.00
> when ar_amount>0 then ar_amount
> end
> from ar
>
> I am using DB2 if that makes any difference.
>
>
> --
> Ray Briggs II
> Lone Star Medical Products, Inc.
> xxx-xxx-xxxx  Ext. 148
> mailto:ray@l...
>
>
>
>
>
>
>
>