Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2010
    Posts
    3

    Question Unanswered: QMF Query, Combine data from 2 tables to create a single report

    Hello
    First of all, sorry for a long post and may not making any sense. My work is using IBM DB2 QMF and I'm just an end user with no access to the database and tables. I am allowed to create a query in QMF to customize a report for myself. Please give me some guidance and advice! Thank you so much!

    TABLES
    ---------
    TABLE#1 FLORIDA.CURRENT_MONTH_ORDER :
    COL 1: CUR_ORDER_NO
    COL 2: CUR_ORDER_QTY
    COL 3: CUR_ORDER_AMT
    COL 4: ITEM_NO
    COL 5: ITEM_DESCRIPTION
    COL 6: CUSTOMER_ID
    COL 7: ORDER_DATE

    TABLE#2 FLORIDA.HISTORY_MONTH_ORDER :
    COL 1: HISTORY_ORDER_NO
    COL 2: HISTORY_ORDER_QTY
    COL 3: HISTORY_ORDER_AMT
    COL 4: ITEM_NO
    COL 5: ITEM_DESCRIPTION
    COL 6: CUSTOMER_ID
    COL 7: ORDER_DATE
    ------------------------------------------------------------------------
    I would like to write a query to generate a report with data pulled from both tables. Prompt for input will be the order date range. (from/to) How can I make column 1 through 3 teamed up and given them new names?

    For example: I input a start Date of "1 January 2010" and End Date of "8 June
    2010", for the "ORDER_DATE" Range. The report should show all of the orders from the dates range and displays 7 columns. Column 1 through Column 3 contains data from TABLE 1 and TABLE 2. And instead of the name CUR_ORDER_NO or HISTORY_ORDER_NO, the new column name is going to be just "ORDER_NO"
    -------------------
    TABLE#1 FLORIDA.CURRENT_MONTH_ORDER
    This table keeps orders occured in the current month before being moved to the archived table at month-end
    -------------------
    TABLE#2 FLORIDA.HISTORY_MONTH_ORDER
    This table archived orders from start to the last completed month (in this case, the most recent completed month is MAY 2010)
    -------------------

    I wrote something like this but it is not working...


    SELECT DISTINCT
    CUR_ORDER_NO, CUR_ORDER_QTY, CUR_ORDER_AMT,
    ITEM_NO, ITEM_DESCRIPTION, CUSTOMER_ID, ORDER_DATE
    FROM FLORIDA.CURRENT_MONTH_ORDER
    WHERE ORDER_DATE => &FROM_DATE
    AND ORDER_DATE =< &TO_DATE
    UNION
    SELECT
    HISTORY_ORDER_NO, HISTORY_ORDER_QTY, HISTORY_ORDER_AMT,
    ITEM_NO, ITEM_DESCRIPTION, CUSTOMER_ID, ORDER_DATE
    FROM FLORIDA.HISTORY_MONTH_ORDER
    WHERE ORDER_DATE => &FROM_DATE
    AND ORDER_DATE =< &TO_DATE

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    merikaa, What is your error message?

    Is the data really stored in the tables as '1 January 2010'?

    What version of DB2 are you using and is this z/OS or LUW operating system?

  3. #3
    Join Date
    Jun 2010
    Posts
    3
    Hello Stealth DBA,

    I don't have access to work computer right now. The sample query in my post was also not the actual one. I can't include the actual tables and column names here. (I am afraid I will get fired...)

    I tested different queries and some had error (I forgot the messages) and some ran fine but the report gave me too many columns. It included
    CUR_ORDER_NO, CUR_ORDER_QTY, CUR_ORDER_AMT, HISTORY_ORDER_NO, HISTORY_ORDER_QTY, HISTORY_ORDER_AMT (these 6 columns should have been eliminated into 3)

    The QMF at work that I was using is version 9. I'm so sorry I don't know if it's z/OS or LUW. (don't even know what they are, hehe...sorry :P

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question

    Code:
    Select 
    cur_order_no order_no, cur_order_qty order_qty, cur_order_amt order_amt,
    item_no, item_description, customer_id, order_date, 'Current' 
    from florida.current_month_order
    where order_date between &from_date and &to_date
    union
    select
    history_order_no order_no, history_order_qty order_qty, 
    history_order_amt order_amt,
    item_no, item_description, customer_id, order_date, 'History'
    from florida.history_month_order
    where order_date between &from_date and &to_date
    Fetch first 100 rows only
    




    Best Regards: Lenny
    Last edited by Lenny77; 06-09-10 at 18:12.

  5. #5
    Join Date
    Jun 2010
    Posts
    3
    Ah...looks awesome, Lenny! I will try this first thing when I get to work tomorrow Thank you so much for your guidance.

Posting Permissions

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