Results 1 to 3 of 3

Thread: SQL Help!

  1. #1
    Join Date
    Mar 2006

    Question Unanswered: SQL Help!

    Hi All,
    I need to write a SQL for reporting. Here are the schema of the one of tables:

    ID Amt_A Amt_C Amt_C
    1 100.00 0.00 200.00
    2 0.00 120.00 0.00
    3 0.00 133.00 300.00

    I need to write a sql to display those amount in separate row (amt > 0) as below:

    ID AMT
    1 100.00
    1 200.00
    2 120.00
    3 133.00
    3 300.00

    I could only think of one way in doing it, I would alias the table into 3 tables and each of them would select the "ID" and one of the "Amt_x" column, then join them again. But its seems not efficient enough. As the above table is only one of the tables in the query, and the data size in Production environment is large, I am afraid the performance would be very slow.

    Is there any other smarter or simpler way to do that?

    Thank you very much.

  2. #2
    Join Date
    May 2003
    Use the UNION ALL statement. You will have 3 SQL statements in the UNION ALL, each with 2 columns in the select list. Check the SQL Reference for usage if you don't understand.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2003
    try the following:
    select id, amt_a as amt from table where amt_a > 0
    union all
    select id, amt_b as amt from table where amt_b > 0
    union all
    select id, amt_c as amt from table where amt_c > 0
    Hope this helps,

Posting Permissions

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