Hello,
SCENARIO:
I would like to have these 2 queries be combined and the results as well. I tried to add QUERY2 in the WHERE clause of QUERY1, what I got was multiple field instances as a result. Included below the the query/result that I would wnt to achieve.
Thanks in advance!
QUERY 1
LOCKING ROW FOR ACCESS
-
SELECT DISTINCT
-
HIST_1.ORDER_ID AS ORDER_ID,
-
HIST_1.ORIGINATOR_NM AS USERID,
-
HIST_1.BAN AS BAN,
-
HIST_1. EVENT_TS AS BEGIN_TIME
-
-
FROM EXCEPTION_EVENT_HIST HIST_1
-
INNER JOIN EXCEPTION_EVENT_HIST HIST_2
-
ON HIST_1.ORDER_ID = HIST_2.ORDER_ID
-
and HIST_1.EVENT_TYPE_NM = 'Item Pkg'
-
and HIST_2.EVENT_TYPE_NM = 'Item Msg'
-
and HIST_1.package_method_nm in ( 'Worklist Manager')
-
-
where HIST_1.ban = '5000'
-
and HIST_1. ORIGINATOR_NM in ('mm061w','es162s','mp8244')
-
group by
-
HIST_1.ORDER_ID
-
, HIST_1.ORIGINATOR_NM
-
, HIST_1.BAN
-
, HIST_1.EVENT_TS
-
ORDER BY 4;
-
QUERY 1 OUTPUT:
ORDERID USERID BAN BEGINTIME
001 User1 5000 2009-01-10 08:35:53
001 User2 5000 2009-01-10 11:35:53
001 User3 5000 2009-01-10 21:35:53
QUERY 2 -
SELECT
-
HIST_3. ORIGINATOR_NM AS USERID2
-
HIST_3. EVENT_TS as END2_time
-
FROM EXCEPTION_EVENT_HIST HIST_3
-
WHERE HIST_2.ban = '5000'
-
and HIST_3.PACKAGE_DISPOSITION_TYPE_NM = 'Packaged'
-
and HIST_3.PACKAGE_METHOD_NM = 'Assign To Me'
-
group by
-
HIST_3. ORIGINATOR_NM
-
HIST_3. EVENT_TS
-
Order by 2
-
QUERY 2 OUTPUT:
USERID2 END2_TIME
USER100 2009-01-10 08:38:53
USER300 2009-01-10 11:38:53
USER200 2009-01-10 21:38:53
Expected result:
ORDERID USERID BAN BEGINTIME USERID2 END2_TIME
001 User1 5000 2009-01-10 08:35:53 USER100 2009-01-10 08:38:53
001 User2 5000 2009-01-10 11:35:53 USER300 2009-01-10 11:38:53
001 User3 5000 2009-01-10 21:35:53 USER200 2009-01-10 21:38:53
