asked on

T-SQL syntax question

Hi

I have a table with 4 fields, the last field is an amount, which can be positive or negative. I want to insert the data from that table in a table that has the same first 3 fields and then 2 amount fields, one for positive, one for negative amounts.

I am trying to use the "Insert Into <new table> (f1, f2, f3, apos, aneg) select f1, f2, f3, a1, a1 from <old table>" syntax.
But I don't know how to specify the fields so that:
- if a1 is positive, then apos = a1, aneg=0
- if a1 is negative, then apos = 0, aneg=a1

Can someone help me with how to achieve what I need ?

Thanks
Bernard

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

A pair of CASE statements with logic to pick off positives and negatives will work.

<air code, modify to meet your needs>

Insert Into <new table> (f1, f2, f3, apos, aneg) 
SELECT blah, yadda, yabba, 
   CASE WHEN a1 > 0 THEN a1 ELSE 0 END as postive_column, 
   CASE WHEN a1 < 0 THEN a1 ELSE 0 END as negative_column
FROM old_table

Open in new window

Ryan Chong

try:

Insert Into newtable (f1, f2, f3, apos, aneg)
select f1, f2, f3,
case when a1 >=0 then a1 else 0 end,
case when a1 >=0 then 0 else a1 end
from oldtable

Bernard Thouin

ASKER

Hi all of you

Thanks for the solutions, I have used the CASE solution first suggested by ste5an, so he gets the points.