I have a query as below;

Select TRN-AMT
Into WK-TRN-AMT
From TRNTABLE
Where TRN-DATE <= WK-TRN-DATE.

In TRNTABLE the TRN-AMT can hold both Positive (>= 0) and
Negative (< 0) values.

Is there anyway I can adjust the above query to get TRN-AMT to
be moved INTO WK-POS-AMT when TRN-AMT >= 0 and to WK-NEG-AMT
in all other situations.

Any help will be much appreciated.

Thanks.

Natasha.

You would have to make it two different queries:

Select TRN-AMT
Into WK-POS-AMT
From TRNTABLE
Where TRN-DATE <= WK-TRN-DATE
and trn-amt >= 0.

Select TRN-AMT
Into WK-NEG-AMT
From TRNTABLE
Where TRN-DATE <= WK-TRN-DATE
and trn-amt < 0.


Andy

Sorry, I'm looking for a single query... Is it possible ? - N.

No, it is not possible with a singe query.

Andy

Well, I guess you could do it this way: I did not test it so you may need to cast nulls to the TRN-AMT data type before it works.

You might try this, but depending on your cituation or how you are coding it after words it might not work

Oups. n_i we must be on the same page now

natasha, try substituting 0(zero) instead of NULL come to think of it. Then you can do SUM function to get your summary data.