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

merikaa · #1 (**permalink**) 06-09-10, 16:44

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!

Quote:

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

Stealth_DBA · #2 (**permalink**) 06-09-10, 16:56

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?

merikaa · #3 (**permalink**) 06-09-10, 17:05

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

Lenny77 · #4 (**permalink**) 06-09-10, 17:07

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

merikaa · #5 (**permalink**) 06-09-10, 17:19

Ah...looks awesome, Lenny! I will try this first thing when I get to work tomorrow

Thank you so much for your guidance.

Internet Services

Web Development

Digital Marketing

Consumer Tech