Results 1 to 7 of 7

Thread: Query

  1. #1
    Join Date
    Apr 2008
    Posts
    8

    Unanswered: Query

    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.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Apr 2008
    Posts
    8

    Same Query

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


    Quote Originally Posted by ARWinner
    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

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    No, it is not possible with a singe query.

    Andy

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Well, I guess you could do it this way:
    Code:
    Select 
      case when TRN-AMT >= 0 then TRN-AMT else null end, 
      case when TRN-AMT < 0 then TRN-AMT else null end
    Into WK-POS-AMT, WK-NEG-AMT
    From TRNTABLE
    Where TRN-DATE <= WK-TRN-DATE
    I did not test it so you may need to cast nulls to the TRN-AMT data type before it works.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    You might try this, but depending on your cituation or how you are coding it after words it might not work

    Code:
    Select 
    CASE WK-POS-AMT WHEN trn-amt >= 0 then TRN-AMT else NULL END,
    CASE WK-NEG-AMT WHEN trn-amt < 0 then TRN-AMT else NULL END
    Into WK-POS-AMT, WK-NEGAMT
    From TRNTABLE
    Where TRN-DATE <= WK-TRN-DATE
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  7. #7
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •