This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Apr 2, 2013 6:20 PM by Frank Kulash Go to original post RSS
  • 15. Re: Query with conditional Counting Columns
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Reini,
    Freakster235 wrote:
    Hello Frank,

    your query Works, but it seems that i got missing results. I think i know what that Happen.
    I have a Couple of Orders that have the exactly same create Date, this occurs of the Orders will be
    Imported from file.

    And i think your 2nd subquery groups all Orders together when they have the same create Date and behause of that Group issue i have a Bunch of missing results.
    Is there a Way to modify this query? Die order_nr is my primary Key.
    I hope you know what i mean.
    Sorry,
    I don't know what you mean about duplicate dates. Post some sample data that shows the problem, and the correct results you want from that sample data. Please do this whenever you have a problem; don't wait for people to beg.

    I don't know what business reality you're trying to show here. What are you trying to count? If the GROUP BY clause of got_aggregates doesn't reflect whatever your entities are, then you need to change that GROUP BY clause. Perhaps you need some other expression in that GROUP BY clause, and perhaps you don't need create_date.
  • 16. Re: Query with conditional Counting Columns
    993527 Newbie
    Currently Being Moderated
    Hello Frank,

    That is right, for the word "the" we have to use 3 different words "der, die, das" in german.

    Here the sample data:
    CREATE      TABLE      TABLE_2
       
    (     "ORDER_NR"      VARCHAR2 (12)
    ,      "PRIORITY"      VARCHAR2 (2)
    ,      "WO_STEP"      VARCHAR2 (1)
    ,      "STEP_DATE"      DATE
    );
    
    CREATE      TABLE      TABLE_1
    (     "ORDER_NR"           VARCHAR2     (12) PRIMARY KEY
    ,      "PRIORITY"           VARCHAR2      (2)
    ,     "DEPT"               VARCHAR2 (1)
    ,      "CREATE_DATE"      DATE
    ,     "ACT_STEP"          VARCHAR2     (2)
    ,     "STEP_DATE"          DATE
    ,     "EMPLOYEE"          VARCHAR2     (5)
    ,     "DESCRIPTION"     VARCHAR2     (20)
    );
    
    INSERT      INTO      TABLE_1      (ORDER_NR,               DEPT,           PRIORITY,      CREATE_DATE,                                                        ACT_STEP,     STEP_DATE,                                                            EMPLOYEE,     DESCRIPTION) 
                        VALUES           ('1KKA1T205634',     'A',               '12',          TO_DATE('10-FEB-13 10:00:00','DD-MON-RR HH24:MI:SS'),     'U',          TO_DATE('28-FEB-13 12:00:00','DD-MON-RR HH24:MI:SS'),     'W0010',     'CLEAN HOUSE');
    INSERT      INTO      TABLE_1      (ORDER_NR,               DEPT,          PRIORITY,     CREATE_DATE,                                                        ACT_STEP,     STEP_DATE,                                                            EMPLOYEE,     DESCRIPTION)
                        VALUES           ('1KKA1Z300612',     'S',               '02',          TO_DATE('08-FEB-13 14:00:00','DD-MON-RR HH24:MI:SS'),     'F',          TO_DATE('20-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'),     'K0052',     'REPAIR CAR');
    INSERT      INTO      TABLE_1      (ORDER_NR,               DEPT,          PRIORITY,      CREATE_DATE,                                                        ACT_STEP,     STEP_DATE,                                                            EMPLOYEE,     DESCRIPTION) 
                        VALUES           ('1KKA1T205635',     'A',               '05',          TO_DATE('10-FEB-13 10:00:00','DD-MON-RR HH24:MI:SS'),     'U',          TO_DATE('28-FEB-13 12:00:00','DD-MON-RR HH24:MI:SS'),     'W0012',     'CLEAN HOUSE');
    INSERT      INTO      TABLE_1      (ORDER_NR,               DEPT,          PRIORITY,     CREATE_DATE,                                                        ACT_STEP,     STEP_DATE,                                                            EMPLOYEE,     DESCRIPTION)
                        VALUES           ('1KKA1Z300613',     'A',               '12',          TO_DATE('08-FEB-13 14:00:00','DD-MON-RR HH24:MI:SS'),     'F',          TO_DATE('20-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'),     'K0053',     'REPAIR CAR');
    INSERT      INTO      TABLE_1      (ORDER_NR,               DEPT,           PRIORITY,      CREATE_DATE,                                                        ACT_STEP,     STEP_DATE,                                                            EMPLOYEE,     DESCRIPTION) 
                        VALUES           ('1KKA1T205636',     'A',               '12',          TO_DATE('10-FEB-13 10:00:00','DD-MON-RR HH24:MI:SS'),     'U',          TO_DATE('28-FEB-13 12:00:00','DD-MON-RR HH24:MI:SS'),     'W0010',     'CLEAN HOUSE');
    INSERT      INTO      TABLE_1      (ORDER_NR,               DEPT,          PRIORITY,     CREATE_DATE,                                                        ACT_STEP,     STEP_DATE,                                                            EMPLOYEE,     DESCRIPTION)
                        VALUES           ('1KKA1Z300614',     'S',               '02',          TO_DATE('08-FEB-13 14:00:00','DD-MON-RR HH24:MI:SS'),     'F',          TO_DATE('20-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'),     'K0052',     'REPAIR CAR');
    INSERT      INTO      TABLE_1      (ORDER_NR,               DEPT,          PRIORITY,      CREATE_DATE,                                                        ACT_STEP,     STEP_DATE,                                                            EMPLOYEE,     DESCRIPTION) 
                        VALUES           ('1KKA1T205637',     'A',               '05',          TO_DATE('10-FEB-13 10:00:00','DD-MON-RR HH24:MI:SS'),     'U',          TO_DATE('28-FEB-13 12:00:00','DD-MON-RR HH24:MI:SS'),     'W0012',     'CLEAN HOUSE');
    INSERT      INTO      TABLE_1      (ORDER_NR,               DEPT,          PRIORITY,     CREATE_DATE,                                                        ACT_STEP,     STEP_DATE,                                                            EMPLOYEE,     DESCRIPTION)
                        VALUES           ('1KKA1Z300615',     'A',               '12',          TO_DATE('08-FEB-13 14:00:00','DD-MON-RR HH24:MI:SS'),     'F',          TO_DATE('20-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'),     'K0053',     'REPAIR CAR');
    
    
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1T205634',     '12',          'A',          TO_DATE('12-FEB-13 13:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1T205634',     '12',          'R',          TO_DATE('21-FEB-13 09:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1T205634',     '12',          'U',          TO_DATE('28-FEB-13 12:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1Z300612',     '02',          'A',          TO_DATE('12-FEB-13 13:52:42','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1Z300612',     '02',          'F',          TO_DATE('20-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1T205635',     '05',          'A',          TO_DATE('12-FEB-13 13:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1T205635',     '05',          'R',          TO_DATE('21-FEB-13 09:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1T205635',     '05',          'U',          TO_DATE('28-FEB-13 12:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1Z300613',     '12',          'A',          TO_DATE('12-FEB-13 13:52:42','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1Z300613',     '12',          'F',          TO_DATE('20-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'));                    
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1T205635',     '12',          'A',          TO_DATE('12-FEB-13 13:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1T205635',     '12',          'R',          TO_DATE('21-FEB-13 09:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1T205635',     '12',          'U',          TO_DATE('28-FEB-13 12:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1Z300614',     '02',          'A',          TO_DATE('12-FEB-13 13:52:42','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1Z300614',     '02',          'F',          TO_DATE('20-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1T205636',     '05',          'A',          TO_DATE('12-FEB-13 13:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1T205636',     '05',          'R',          TO_DATE('21-FEB-13 09:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1T205636',     '05',          'U',          TO_DATE('28-FEB-13 12:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1Z300615',     '12',          'A',          TO_DATE('12-FEB-13 13:52:42','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1Z300615',     '12',          'F',          TO_DATE('20-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'));
    COMMIT;
    The result from your Query is:
    DEPT  PD02  PD05  PD12  TOTAL
    ----  ----  ----  ----  -----  
    A        0     0     1      3
    S        0     0     0      1
    But thar is not the truth ;) I have a total of 8 Order's not of 4.
    The results should look like this:
    DEPT  PD02  PD05  PD12  TOTAL
    ----  ----  ----  ----  -----  
    A        0     0     2      6
    S        0     0     0      2
    Your subquery got_aggregates Groups all Jobs with the same create_date together and because of this i got missing Orders in the result.

    Thanks.

    Reini
  • 17. Re: Query with conditional Counting Columns
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Freakster235 wrote:
    Hello Frank,

    That is right, for the word "the" we have to use 3 different words "der, die, das" in german.
    You have to use at least 3 different words. Don't "dem", "den" and "des" also mean "the"?
    Actually, you write English very well. In fact, you should write more of it: you should post an explanation of what you're trying to do. People like me can help you write queries that get the right results for small sets of sample data, but unless we understand why you want the results you do, we might give you solutions that just happen to work for the small sample sets, more or less by chance.
    ... But thar is not the truth ;) I have a total of 8 Order's not of 4.
    So you want to count distinct orders; and create_date has nothing to do with this problem. Is that right?
    If so, don't use create_date in the GROUP BY clause of got_aggregates; use order_nr instead:
    WITH     priorities          AS
    (
         SELECT      '01' AS min_priority, '03' AS max_priority, 5 AS max_age  FROM dual  UNION ALL
         SELECT      '04',                  '08',                  8                FROM dual  UNION ALL
         SELECT      '09',                 '15',                 30             FROM dual  
    )
    ,     got_aggregates          AS
    (
         SELECT    t1.dept
         ,       t1.priority
    --     ,       t1.create_date     -- *****  NOT NEEDED  *****
         ,       MAX (CASE WHEN t2.wo_step = 'A' THEN t2.step_date END)     AS first_a_date
         ,       MAX (CASE WHEN t2.wo_step = 'U' THEN t2.step_date END)     AS last_u_date
         FROM               table_1     t1
         LEFT OUTER JOIN       table_2     t2  ON       t2.order_nr     = t1.order_nr     -- ***** CHANGED  *****
         GROUP BY  t1.dept
         ,       t1.priority
         ,       t1.order_nr          -- *****  CHANGED  *****
    )
    SELECT    a.dept
    ,       COUNT (CASE WHEN p.min_priority = '01' THEN 1 END)     AS pd02
    ,       COUNT (CASE WHEN p.min_priority = '04' THEN 1 END)     AS pd05
    ,       COUNT (CASE WHEN p.min_priority = '09' THEN 1 END)     AS pd12
    ,       COUNT (*)                                           AS total
    FROM             got_aggregates  a
    LEFT OUTER JOIN      priorities      p  ON   a.priority          BETWEEN  p.min_priority
                                                              AND      p.max_priority
                            AND  NVL ( a.last_u_date
                                      , SYSDATE
                                  ) - a.first_a_date <= p.max_age
    GROUP BY  a.dept
    ORDER BY  a.dept
    ;
    There's no need to include either create_date nor order_nr in the SELECT clause of got_aggregates.
    In your new sample data, there is an order_nr that exists in table_1 but not in table_2. In order to count that order_nr, we need to use an outer join in got_aggregates.
    The results should look like this:
    DEPT  PD02  PD05  PD12  TOTAL
    ----  ----  ----  ----  -----  
    A        0     0     2      6
    S        0     0     0      2
    That's what I get now:
    D       PD02       PD05       PD12      TOTAL
    - ---------- ---------- ---------- ----------
    A          0          0          2          6
    S          0          0          0          2
  • 18. Re: Query with conditional Counting Columns
    993527 Newbie
    Currently Being Moderated
    Hi Frank,

    of course they all mean "the" but it depends on the word you want to address. An example:
    The Dog is a kind of a male, here you have to use "der" like "Der Hund"
    The Cat is a kind of a female, here it is "die" like "Die Katze"
    The Windows is a kind of a Thing, an then you have to use "das" like "Das Fenster"
    And then do not forget a whole bunch of grammar exceptions, but English is not even better in grammar i think.

    Back to Topic :)
    With the modifications that you mentioned in your last post it works correctly.

    What i tried to explain in my last post is, that the result from your last query does not count all Orders, some of the Orders gets igonred.
    And i thought that these error (miscount) came on, since all the ignored Orders have exactly the same creation timestamp, did i assume that wrong?
    The Group By creation date, Groups all Orders with the same Creation Date together and then they will be counted only one time right?
    Now your query uses the Order_Nr for join which is Unique.

    But can you explain to me what does OUTER JOIN LEFT or RIGHT?

    That case with Order_Nr exists in Table_1 but not in Table_2 was a typing error from me. In my production database, there ist a constraint between table_1 and table_2 and each order_nr in Table_1
    have to have at least one entry in table_2.

    So Sorry, i have to look like this:
    CREATE      TABLE      TABLE_2
       
    (     "ORDER_NR"      VARCHAR2 (12)
    ,      "PRIORITY"      VARCHAR2 (2)
    ,      "WO_STEP"      VARCHAR2 (1)
    ,      "STEP_DATE"      DATE
    );
     
    CREATE      TABLE      TABLE_1
    (     "ORDER_NR"           VARCHAR2     (12) PRIMARY KEY
    ,      "PRIORITY"           VARCHAR2      (2)
    ,     "DEPT"               VARCHAR2 (1)
    ,      "CREATE_DATE"      DATE
    ,     "ACT_STEP"          VARCHAR2     (2)
    ,     "STEP_DATE"          DATE
    ,     "EMPLOYEE"          VARCHAR2     (5)
    ,     "DESCRIPTION"     VARCHAR2     (20)
    );
     
    INSERT      INTO      TABLE_1      (ORDER_NR,               DEPT,           PRIORITY,      CREATE_DATE,                                                        ACT_STEP,     STEP_DATE,                                                            EMPLOYEE,     DESCRIPTION) 
                        VALUES           ('1KKA1T205634',     'A',               '12',          TO_DATE('10-FEB-13 10:00:00','DD-MON-RR HH24:MI:SS'),     'U',          TO_DATE('28-FEB-13 12:00:00','DD-MON-RR HH24:MI:SS'),     'W0010',     'CLEAN HOUSE');
    INSERT      INTO      TABLE_1      (ORDER_NR,               DEPT,          PRIORITY,     CREATE_DATE,                                                        ACT_STEP,     STEP_DATE,                                                            EMPLOYEE,     DESCRIPTION)
                        VALUES           ('1KKA1Z300612',     'S',               '02',          TO_DATE('08-FEB-13 14:00:00','DD-MON-RR HH24:MI:SS'),     'F',          TO_DATE('20-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'),     'K0052',     'REPAIR CAR');
    INSERT      INTO      TABLE_1      (ORDER_NR,               DEPT,          PRIORITY,      CREATE_DATE,                                                        ACT_STEP,     STEP_DATE,                                                            EMPLOYEE,     DESCRIPTION) 
                        VALUES           ('1KKA1T205635',     'A',               '05',          TO_DATE('10-FEB-13 10:00:00','DD-MON-RR HH24:MI:SS'),     'U',          TO_DATE('28-FEB-13 12:00:00','DD-MON-RR HH24:MI:SS'),     'W0012',     'CLEAN HOUSE');
    INSERT      INTO      TABLE_1      (ORDER_NR,               DEPT,          PRIORITY,     CREATE_DATE,                                                        ACT_STEP,     STEP_DATE,                                                            EMPLOYEE,     DESCRIPTION)
                        VALUES           ('1KKA1Z300613',     'A',               '12',          TO_DATE('08-FEB-13 14:00:00','DD-MON-RR HH24:MI:SS'),     'F',          TO_DATE('20-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'),     'K0053',     'REPAIR CAR');
    INSERT      INTO      TABLE_1      (ORDER_NR,               DEPT,           PRIORITY,      CREATE_DATE,                                                        ACT_STEP,     STEP_DATE,                                                            EMPLOYEE,     DESCRIPTION) 
                        VALUES           ('1KKA1T205636',     'A',               '12',          TO_DATE('10-FEB-13 10:00:00','DD-MON-RR HH24:MI:SS'),     'U',          TO_DATE('28-FEB-13 12:00:00','DD-MON-RR HH24:MI:SS'),     'W0010',     'CLEAN HOUSE');
    INSERT      INTO      TABLE_1      (ORDER_NR,               DEPT,          PRIORITY,     CREATE_DATE,                                                        ACT_STEP,     STEP_DATE,                                                            EMPLOYEE,     DESCRIPTION)
                        VALUES           ('1KKA1Z300614',     'S',               '02',          TO_DATE('08-FEB-13 14:00:00','DD-MON-RR HH24:MI:SS'),     'F',          TO_DATE('20-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'),     'K0052',     'REPAIR CAR');
    INSERT      INTO      TABLE_1      (ORDER_NR,               DEPT,          PRIORITY,      CREATE_DATE,                                                        ACT_STEP,     STEP_DATE,                                                            EMPLOYEE,     DESCRIPTION) 
                        VALUES           ('1KKA1T205637',     'A',               '05',          TO_DATE('10-FEB-13 10:00:00','DD-MON-RR HH24:MI:SS'),     'U',          TO_DATE('28-FEB-13 12:00:00','DD-MON-RR HH24:MI:SS'),     'W0012',     'CLEAN HOUSE');
    INSERT      INTO      TABLE_1      (ORDER_NR,               DEPT,          PRIORITY,     CREATE_DATE,                                                        ACT_STEP,     STEP_DATE,                                                            EMPLOYEE,     DESCRIPTION)
                        VALUES           ('1KKA1Z300615',     'A',               '12',          TO_DATE('08-FEB-13 14:00:00','DD-MON-RR HH24:MI:SS'),     'F',          TO_DATE('20-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'),     'K0053',     'REPAIR CAR');
     
     
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1T205634',     '12',          'A',          TO_DATE('12-FEB-13 13:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1T205634',     '12',          'R',          TO_DATE('21-FEB-13 09:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1T205634',     '12',          'U',          TO_DATE('28-FEB-13 12:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1Z300612',     '02',          'A',          TO_DATE('12-FEB-13 13:52:42','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1Z300612',     '02',          'F',          TO_DATE('20-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1T205635',     '05',          'A',          TO_DATE('12-FEB-13 13:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1T205635',     '05',          'R',          TO_DATE('21-FEB-13 09:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1T205635',     '05',          'U',          TO_DATE('28-FEB-13 12:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1Z300613',     '12',          'A',          TO_DATE('12-FEB-13 13:52:42','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1Z300613',     '12',          'F',          TO_DATE('20-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'));                    
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1T205636',     '12',          'A',          TO_DATE('12-FEB-13 13:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1T205636',     '12',          'R',          TO_DATE('21-FEB-13 09:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1T205636',     '12',          'U',          TO_DATE('28-FEB-13 12:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1Z300614',     '02',          'A',          TO_DATE('12-FEB-13 13:52:42','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1Z300614',     '02',          'F',          TO_DATE('20-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1T205637',     '05',          'A',          TO_DATE('12-FEB-13 13:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1T205637',     '05',          'R',          TO_DATE('21-FEB-13 09:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1T205637',     '05',          'U',          TO_DATE('28-FEB-13 12:00:00','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1Z300615',     '12',          'A',          TO_DATE('12-FEB-13 13:52:42','DD-MON-RR HH24:MI:SS'));
    INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                        VALUES           ('1KKA1Z300615',     '12',          'F',          TO_DATE('20-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'));
    COMMIT;
    But this does nothing change to the results.

    Thank You.
  • 19. Re: Query with conditional Counting Columns
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Freakster235 wrote:
    ... What i tried to explain in my last post is, that the result from your last query does not count all Orders, some of the Orders gets igonred.
    And i thought that these error (miscount) came on, since all the ignored Orders have exactly the same creation timestamp, did i assume that wrong?
    No, I think you understand correctly.
    The Group By creation date, Groups all Orders with the same Creation Date together and then they will be counted only one time right?
    Now your query uses the Order_Nr for join which is Unique.
    Before, when the GROUP BY clause included create_date, the query was counting distinct create_dates.
    Now that the GROUP BY clause includes order_nr instead of create_date, the query is counting distinct order_nrs, which, I assume, means it's counting orders.
    But can you explain to me what does OUTER JOIN LEFT or RIGHT?
    FROM             x
    LEFT OUTER JOIN  y  ON  [join_condition]
    means join tables x and y if possible, but if the join condition fails, still include a row with the data from table x.
    That case with Order_Nr exists in Table_1 but not in Table_2 was a typing error from me. In my production database, there ist a constraint between table_1 and table_2 and each order_nr in Table_1
    have to have at least one entry in table_2.
    Okay; in that case, you can go back to using a plain old inner JOIN in sub-query got_aggregates:
    ...     FROM     table_1     t1
         LEFT      table_2     t2  ON       t2.order_nr     = t1.order_nr
    But this does nothing change to the results.
    An outer join here will get the right results, but it will be less efficient.
    An outer join differs from an inner join only in what happens when a given row in table_1 (in this example) does not have a matching row in table_2. You say that every row in table_1 has at least 1 matching row in table_2, so there will not be any difference in the results.
  • 20. Re: Query with conditional Counting Columns
    993527 Newbie
    Currently Being Moderated
    Hello Frank,

    thank you for your help, now it works really perfect, and i have exact the result i expect.
    Little by little i know how it works and where i have to use which strategies or command, thanks for your teaching.
    Where did you learn all that stuff?
    Where can i see, how to build a SQL script most efficiently?

    What is the different between an plain JOIN and use a "WHERE Table1.identifier1 = Table2.identifier1"?

    Thank You.
  • 21. Re: Query with conditional Counting Columns
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Freakster235 wrote:
    ... Where did you learn all that stuff?
    Practice, studying examples and experimenting wth them, and reading, especially the SQL Language manual.
    Where can i see, how to build a SQL script most efficiently?
    Sorry, I don't know much about efficiency.
    Have you looked at the 2 Day + Performance Tuning guide?
    What is the different between an plain JOIN and use a "WHERE Table1.identifier1 = Table2.identifier1"?
    Sorry, I don't understand.
    What do you mean by "a plain join"? Many people (me included) seem to think of an inner join as the simplest, plainest kind of join. In fact, I used that word in my last message:
    Frank Kulash wrote:
    ... Okay; in that case, you can go back to using a plain old inner JOIN in sub-query got_aggregates:
    What you posted looks like an inner join condition, so I'm not sure to what you're comparing it.
    Can you post 2 complete queries; one showing a "plain" join, and one showing some other kind of join?
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points