Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    6

    Unanswered: replicate row from query

    Hi All,

    I'd like to seek some assistance regarding the below scenario:

    i have a select query that returns the ff:
    POST_KEY ACCOUNT LOCAL_AMOUNT
    ---------- -------- ------------
    40 31042102 160
    40 31043101 1420

    my output should be:
    POST_KEY ACCOUNT LOCAL_AMOUNT
    ---------- -------- ------------
    40 31042102 160
    50 31042102 -160
    40 31043101 1420
    50 31043101 -1420

    i need to replicate each row from my query to have a negative amt and a post_key of 50.
    i am new to oracle and have no idea how to handle this kind of situation.

    any suggestions would be much appreciated.

    thanks much!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Maybe this:
    Code:
    SELECT POST_KEY, ACCOUNT, LOCAL_AMOUNT 
    FROM the_table
    UNION ALL
    SELECT 50, ACCOUNT, LOCAL_AMOUNT * -1
    FROM the_table
    ORDER BY 2

  3. #3
    Join Date
    Mar 2009
    Posts
    6
    thanks a lot shammat!

    i tried UNION and it works.
    however, my sql will be used by a DSQL (dynamic sql).
    And unfortunately it has a limitation which cannot handle UNION.
    just need a workaround n my simple select query . .

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Seems to be a very strange enviroment/programming language that doesn't allow a UNION in a dynamic SQL..

    However: create a VIEW based on the UNION and select from the view in your dynamic SQL

  5. #5
    Join Date
    Mar 2009
    Posts
    6
    I'm sorry.
    But what i meant was, we already have a dynamic sql code in place.

    I just need to create an sql query for a specific report.

    Thanks so much. i appreciate u taking time to reply.

Posting Permissions

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