2 Replies Latest reply: Feb 26, 2013 9:17 AM by nydba RSS

    query help

    nydba
      -- DB version 10.2.0.4 and 11gr2
      -- some of these tables have more columns than provided below but posted only relevant columns
      -- I have included ,create DDL/insert stmts and queries and final drop for your reference
      -- i am looking for cancelled orders by user
      -- need help where it has to show order#102 for Mary snow in cancelled column
      CREATE TABLE tst_user_ACTIONS
      (
        USER_ACTION_ID  NUMBER                          NOT NULL,
        USER_ACTION     VARCHAR2(50)              NOT NULL
      );
      
      Insert into tst_user_ACTIONS     Values     (0, 'CANCEL');
      Insert into tst_user_ACTIONS   Values       (2, 'FINISH');
      COMMIT;
      
      
      create table tst_users
      (
        USER_ID  NUMBER                          NOT NULL,
        U_NAME     VARCHAR2(75)              
      );
      
      
      Insert into tst_users     Values     (94511, 'john smith');
      Insert into tst_users    Values      (94512, 'Mary snow');
      COMMIT;
      
      
      create table tst_order_stages
      (
        stg_ID  NUMBER                          NOT NULL,
        stgNAME     VARCHAR2(75)              
      );
      
      
      Insert into tst_order_stages     Values     (21, 'initial Processing');
      Insert into tst_order_stages    Values      (22, 're-order');
      
      COMMIT;
      
      create table tst_orders
      (
        ord_ID  NUMBER                          NOT NULL,
        ord_date date               
      );
      Insert into tst_orders    Values     (101,sysdate);
      Insert into tst_orders    Values      (102, sysdate);
      Insert into tst_orders    Values     (103,sysdate);
      Insert into tst_orders    Values      (104, sysdate);
      
      COMMIT;
      
      
      create table tst_user_order_history
      (user_ord_hist_id number primary key,
        ord_ID  NUMBER,
        user_id number,
         stg_id number ,
        user_action_id number,
           created_date date               
      );
      
      
      
      Insert into tst_user_order_history  Values (441, 101, 94512, 21, 2,    TO_DATE('05/03/2012 13:38:00', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into tst_user_order_history  Values (442, 101, 94512, 21, 0,    TO_DATE('05/03/2012 13:21:43', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into tst_user_order_history values  (444, 102, 94512, 21, 0,    TO_DATE('05/16/2012 18:25:36', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into tst_user_order_history values  (445, 102, 94511, 21, 2,    TO_DATE('05/16/2012 20:18:04', 'MM/DD/YYYY HH24:MI:SS'));
      
      COMMIT;
      
      
      -- now queries/views
      
      CREATE  or replace VIEW tst_cancel_orders
      AS     SELECT u.user_id, tuoh.ord_id AS c_ord_id, MAX (tuoh.created_date) AS cancel_time
             FROM tst_user_order_history tuoh, tst_users u, tst_order_stages s
            WHERE     u.user_id=tuoh.user_id
                  AND s.stg_ID=tuoh.stg_ID
                  AND s.stg_id = 21
                  AND tuoh.user_action_id = 0
         GROUP BY u.user_id, tuoh.ord_id;
         
         
      
      CREATE  or replace view  tst_finish_orders
      AS     SELECT u.user_id, tuoh.ord_id AS fin_ord_id, tuoh.created_date AS finish_time,U.U_NAME
             FROM tst_user_order_history tuoh, tst_users u, tst_order_stages s
            WHERE     u.user_id=tuoh.user_id
                  AND s.stg_ID=tuoh.stg_ID
                  AND s.stg_id = 21
                  AND tuoh.user_action_id = 2;
      --final query
      select 
      tor.ORD_ID,
      tor.ORD_DATE,
      tco.c_ord_id,
      TFO.FIN_ORD_ID,tfo.u_name,tfo.user_id
      from TST_ORDERS tor,
      TST_CANCEL_ORDERS tco,
      TST_FINISH_ORDERS tfo
      where
      tor.ORD_ID=TCO.c_ord_id(+) and
      tor.ORD_ID=TFO.FIN_ORD_ID;
      
      --the above query displays the following
      
      ORD_ID     ORD_DATE                    C_ORD_ID     FIN_ORD_ID     U_NAME     USER_ID
      102     02/25/2013 20:14:25     102     102                   john smith     94511
      101     02/25/2013 20:14:25     101     101                  Mary snow     94512
      
      
      --this above results misleads that john smith canceled order_id 102,which is not the case
      --need to show mary smith  cancelled order_id 102
      --tried different left joins but not successful
      --looking for something like this ,if this is not possible , can you help me with other possible ways
      
      ORD_ID     ORD_DATE                                  C_ORD_ID     FIN_ORD_ID     U_NAME     USER_ID
      102     02/25/2013 20:14:25               102                   john smith     94511
                                                                     102                        Mary snow     94512
      101     02/25/2013 20:14:25          101     101                   Mary snow     94512
      --this is to drop above test tables and views
      drop table TST_USER_ORDER_HISTORY   ;
      drop table TST_USER_ACTIONS         ;
      drop table TST_USERS                ;
      drop table TST_ORDER_STAGES         ;
      drop table TST_ORDERS               ;
      drop view TST_FINISH_ORDERS         ;
      drop view TST_CANCEL_ORDERS         ;
        • 1. Re: query help
          Frank Kulash
          Hi,

          Sorry, it's not celar what you want to do. Do you want to add the user_id from the tco view, and also the u_name that goes with it?
          Adding the user_id is easy, since the tco view is already part of the query. If you can change the definition of tst_cancel_orders to include the u_name, then adding u_name to the final query would be just as easy.
          If you can't change the definition of tst_cancel_orders, you can join tst_users to your existing query, like this:
          select    tor.ORD_ID,
                 tor.ORD_DATE,
                 tco.c_ord_id,
                 tfo.FIN_ORD_ID,
                 tfo.U_NAME,
                 tfo.USER_ID,
                 cu.U_NAME          as C_U_NAME,     -- *****  NEW  *****
                 tco.USER_ID          as C_USER_ID     -- *****  NEW  *****
          from        TST_ORDERS          tor,
                 TST_CANCEL_ORDERS      tco,
                 TST_USERS          cu,          -- *****  NEW  *****
                 TST_FINISH_ORDERS     tfo
          where       tor.ORD_ID     = tco.C_ORD_ID (+) 
          and       tco.USER_ID     = cu.USER_ID (+)     -- *****  NEW  *****
          and       tor.ORD_ID     = tfo.FIN_ORD_ID
          ;
          Notice that this has to be an outer join, since the information from tco might be missing.

          Do you want to not display fin_ord_id and u_name some of the time? (It looks like that's what you requested for the row where ord_id=101.) If so, when?
          • 2. Re: query help
            nydba
            Thank you for your time

            yes ,name is not a requirement

            I am using business objects/crystal reports , where I have to show total canceled orders ,and finished orders by user and I am doing all my summaries in the frontend and yes I can change the definition


            ****Do you want to not display fin_ord_id and u_name some of the time? (It looks like that's what you requested for the row where ord_id=101.) If so, when? ****
            yes 101 cancelled and finished by Mary
            where as 102 is cancelld by mary not finished , that's why I am not displaying

            Edited by: nydba on Feb 26, 2013 7:15 AM