This discussion is archived
1 2 3 Previous Next 37 Replies Latest reply: Jan 4, 2013 12:58 PM by 978776 Go to original post RSS
  • 15. Re: Merging two queries?
    Gaff Journeyer
    Currently Being Moderated
    The query itself is more complex, but your question about how to find an outlier is basically what you asked in an earlier thread. What was wrong with that approach for this problem ?

    Re: Remove outliers when calculating safety stock?
  • 16. Re: Merging two queries?
    978776 Newbie
    Currently Being Moderated
    The issue of outliers is resolved. The problem now is how do I even get the week with 0 in value.
    Frank Kulash has helped me before and even with this...and I hope he can help me on the last part...
  • 17. Re: Merging two queries?
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    As Gaff asked, don't you already know how to include all periods in a given range, even if there is nothing in the tables for those periods? Post your code, where you tried to apply that solution to this problem.

    Always post the results you want from the given data, and explain how you get those results from that data. No kidding, you have to get into the habit of doing this every time you have a question. It will save time if you don't make us beg for it.
  • 18. Re: Merging two queries?
    978776 Newbie
    Currently Being Moderated
    Hi,

    Your solution was the one I asked for. What I'm wondering is how I can get it to take account of weeks without value. The output should be like this:
    MATERIAL     PLANT     QUANTITY     Year_Week
    -------          -----     --------     --------
    Label1R2C     1900     2 524          1219
    Label1R2C     1900     6 945          1220
    Label1R2C     1900     1 279          1221
    Label1R2C     1900     59 750          1222   '**Outlier**
    Label1R2C     1900     0          1223   '**Outlier** ?
    Label1R2C     1900     1 165          1224
    Label1R2C     1900     0          1225   '**Outlier** ?
    Label1R2C     1900     11 634          1226
    Label2R1B     1900     0          1219     
    Label2R1B     1900     0          1220
    Label2R1B     1900     0          1221
    Label2R1B     1900     0          1222
    Label2R1B     1900     0          1223
    Label2R1B     1900     1 165          1224
    Label2R1B     1900     0          1225
    Label2R1B     1900     11 634          1226     '**Outlier** ?
    Your current solution show the result like this, without weeks that has no values.
    MATERIAL     PLANT     QUANTITY     Year_Week
    -------          -----     --------     --------
    Label1R2C     1900     2 524          1219
    Label1R2C     1900     6 945          1220
    Label1R2C     1900     1 279          1221
    Label1R2C     1900     59 750          1222   '**Outlier**
    Label1R2C     1900     1 165          1224
    Label1R2C     1900     11 634          1226
    Label2R1B     1900     1 165          1224
    Label2R1B     1900     11 634          1226
  • 19. Re: Merging two queries?
    Frank Kulash Guru
    Currently Being Moderated
    570142 wrote:
    Hi,

    Your solution was the one I asked for. What I'm wondering is how I can get it to take account of weeks without value. The output should be like this:
    MATERIAL     PLANT     QUANTITY     Year_Week
    -------          -----     --------     --------
    Label1R2C     1900     2 524          1219
    Label1R2C     1900     6 945          1220
    Label1R2C     1900     1 279          1221
    Label1R2C     1900     59 750          1222   '**Outlier**
    Label1R2C     1900     0          1223   '**Outlier** ?
    Does that mean you don't know if the row above is an outlier or not?
    How can anybody produce the results you want if you can't say what those results are?

    Maybe you want something like this:
    WITH      params                          AS
    (
         SELECT     DATE '2012-05-01'     AS start_date
         ,     DATE '2012-06-30'     AS end_date
         ,     'IYYYIW'          AS format_text
         FROM     dual
    )
    ,     all_periods          AS
    (
         SELECT DISTINCT
                 TO_CHAR ( start_date + LEVEL - 1
                   , format_text
                   )          AS period_label
         FROM     params
         CONNECT BY     LEVEL     <= 1 + end_date 
                             - start_date
    )
    ,     got_base_material     AS
    (
         SELECT  t.material
         ,     t.plant
         ,     t.quantity
         ,     TO_CHAR (t.Posting_Date, p.format_text)     AS period_label
         ,      REGEXP_REPLACE ( t.material
                                 , 'R..$'
                                 )               AS base_material
         FROM        tblcon  t
         CROSS JOIN  params  p
     )
    ,     got_aggregates          AS
    (
         SELECT     FIRST_VALUE (MAX (m.material)) 
                            OVER ( PARTITION BY  m.base_material
                                      ORDER BY      d.period_label  DESC
                               )          AS last_material
         ,          NVL ( SUM (m.quantity)
                    , 0
                    )               AS period_quantity
         ,          d.period_label
         ,          m.plant
         ,       NVL ( AVG (SUM (m.quantity))
                             OVER ( PARTITION BY  m.base_material
                                      ,                  m.plant
                            )
                    , 0
                    )           AS period_quantity_avg
         ,       NVL ( STDDEV (SUM (quantity))
                                OVER ( PARTITION BY  m.base_material
                                           ,                     m.plant
                            )
                    , 0
                    )           AS period_quantity_stddev
         FROM                 all_periods         d
         LEFT OUTER JOIN  got_base_material  m  PARTITION BY ( m.base_material
                                                           , m.plant
                                           )
                                    ON  m.period_label  = d.period_label
         GROUP BY  m.base_material
         ,            d.period_label
         ,          m.plant
    )
    SELECT       last_material
    ,       period_quantity
    ,       period_label
    ,       plant
    FROM       got_aggregates
    WHERE       ABS ( period_quantity
               - period_quantity_avg
               )          < period_quantity_stddev
    ORDER BY  last_material
    ,            period_label
    ,          plant
    ;
  • 20. Re: Merging two queries?
    978776 Newbie
    Currently Being Moderated
    Big Thanks!

    One question...
    What is the function for CROSS JOIN params p? I do not understand it ...
  • 21. Re: Merging two queries?
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    570142 wrote:
    Big Thanks!

    One question...
    What is the function for CROSS JOIN params p? I do not understand it ...
    It's not a function.
    CROSS JOIN       is a way of joing tables, somewhat like
    INNER JOIN
    LEFT OUTER JOIN       and
    FULL OUTER JOIN
    FROM          table_a
    CROSS JOIN    table_b
    joins every row of table_a to every row of table_b, exactly the same as
    FROM          table_a
    JOIN          table_b   ON  1 = 1
    If either table has exactly 1 row (as params does), then the effect is to append the columns of the 1-row table to every row of the other table.

    If you want to understand what any sub-query does, run that sub-query alone, with as little additional stuff as possible, and compare the output to the contents of its base tables. In this case, the sub-query got_base_material depends on the sub-query params, so you have to include params to run it, like this:
    WITH      params                          AS
    (
         SELECT     DATE '2012-05-01'     AS start_date
         ,     DATE '2012-06-30'     AS end_date
         ,     'IYYYIW'          AS format_text
         FROM     dual
    )
    ,     got_base_material     AS
    (
         SELECT  t.material
         ,     t.plant
         ,     t.quantity
         ,     TO_CHAR (t.Posting_Date, p.format_text)     AS period_label
         ,      REGEXP_REPLACE ( t.material
                                 , 'R..$'
                                 )               AS base_material
         FROM        tblcon  t
         CROSS JOIN  params  p
    )
    SELECT    *
    FROM       got_base_material
    ;
  • 22. Re: Merging two queries?
    978776 Newbie
    Currently Being Moderated
    Ok, now I understand.

    If I want to be able to select on posting_date and plant.
    Where do I put the selection of the best now?
             Where     t.plant = '1900' 
             AND     t.Posting_date >= to_date('2012-05-01','yyyy-mm-dd') 
             AND     t.Posting_date <= to_date('2012-06-30','yyyy-mm-dd') 
  • 23. Deja Vu All Over Again
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    570142 wrote:
    Ok, now I understand.

    If I want to be able to select on posting_date and plant.
    Where do I put the selection of the best now?
    Where     t.plant = '1900' 
    AND     t.Posting_date >= to_date('2012-05-01','yyyy-mm-dd') 
    AND     t.Posting_date <= to_date('2012-06-30','yyyy-mm-dd') 
    Didn't you ask this before? {message:id=10753889}

    What's wrong with the answer you got before? {message:id=10753960}
  • 24. Re: Merging two queries?
    978776 Newbie
    Currently Being Moderated
    Hi,

    Yes, that's right. But now it does not work after CROSS JOIN params p is added. There, the reason I asked...
  • 25. Re: Merging two queries?
    978776 Newbie
    Currently Being Moderated
    Frank, you are absolutely right. I've asked this before ... :-)
    I see that you thoroughly explained everything to me. Thanks for that.
    I must apologize because I touched it together.

    What I wonder is how I get SQL to display 0 every week regardless of whether the material is during these weeks. We know that materials Label2R1B available during period 2012-06-01 to 2012-06-30 in the table.
    WITH      params                          AS
    (
         SELECT     DATE '2012-05-01'     AS start_date
         ,     DATE '2012-06-30'     AS end_date
         ,     'IYYYIW'          AS format_text
         FROM     dual
    )
    ,     all_periods          AS
    (
         SELECT DISTINCT
                 TO_CHAR ( start_date + LEVEL - 1
                   , format_text
                   )          AS period_label
         FROM     params
         CONNECT BY     LEVEL     <= 1 + end_date 
                             - start_date
    )
    ,     got_base_material     AS
    (
         SELECT  t.material
         ,     t.plant
         ,     t.quantity
         ,     TO_CHAR (t.Posting_Date, p.format_text)     AS period_label
         ,      REGEXP_REPLACE ( t.material
                                 , 'R..$'
                                 )               AS base_material
         FROM        tblcon  t
         CROSS JOIN  params  p
          Where     t.plant = '1900' 
          AND     t.Posting_date >= to_date(start_date,'yyyy-mm-dd') 
          AND     t.Posting_date <= to_date(end_date,'yyyy-mm-dd') 
     )
    ,     got_aggregates          AS
    (
         SELECT     FIRST_VALUE (MAX (m.material)) 
                            OVER ( PARTITION BY  m.base_material
                                      ORDER BY      d.period_label  DESC
                               )          AS last_material
         ,          NVL ( SUM (m.quantity)
                    , 0
                    )               AS period_quantity
         ,          d.period_label
         ,          m.plant
         ,       NVL ( AVG (SUM (m.quantity))
                             OVER ( PARTITION BY  m.base_material
                                      ,                  m.plant
                            )
                    , 0
                    )           AS period_quantity_avg
         ,       NVL ( STDDEV (SUM (quantity))
                                OVER ( PARTITION BY  m.base_material
                                           ,                     m.plant
                            )
                    , 0
                    )           AS period_quantity_stddev
         FROM                 all_periods         d
         LEFT OUTER JOIN  got_base_material  m  PARTITION BY ( m.base_material
                                                           , m.plant
                                           )
                                    ON  m.period_label  = d.period_label
         GROUP BY  m.base_material
         ,            d.period_label
         ,          m.plant
    )
    SELECT       last_material
    ,       period_quantity
    ,       period_label
    ,       plant
    FROM       got_aggregates
    WHERE       ABS ( period_quantity
               - period_quantity_avg
               )     <= 2.0 *      period_quantity_stddev
    And last_material IN('Label2R1B')        
    ORDER BY  last_material
    ,            period_label
    ,          plant
    ;
    Output:
    LAST_MATERIAL     PERIOD_QUANTITY     PERIOD_LABEL     PLANT
    -------          -------------     --------     -----
    Label2R1B     0          201218          1900
    Label2R1B     0          201219          1900
    Label2R1B     0          201220          1900
    Label2R1B     0          201221          1900
    Label2R1B     0          201222          1900
    Label2R1B     0          201223          1900
    Label2R1B     1165          201224          1900
    Label2R1B     0          201225          1900
    Label2R1B     11634          201226          1900
    But if I selects the period 2012-05-01 to 2012-05-30, I get no hits at all. I want it to display like this:
    LAST_MATERIAL     PERIOD_QUANTITY     PERIOD_LABEL     PLANT
    -------          -------------     --------     -----
    Label2R1B     0          201218          1900
    Label2R1B     0          201219          1900
    Label2R1B     0          201220          1900
    Label2R1B     0          201221          1900
    Label2R1B     0          201222          1900
    How do I do that? I
  • 26. Re: Merging two queries?
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    570142 wrote:
    ... But if I selects the period 2012-05-01 to 2012-05-30, I get no hits at all. I want it to display like this:
    LAST_MATERIAL     PERIOD_QUANTITY     PERIOD_LABEL     PLANT
    -------          -------------     --------     -----
    Label2R1B     0          201218          1900
    Label2R1B     0          201219          1900
    Label2R1B     0          201220          1900
    Label2R1B     0          201221          1900
    Label2R1B     0          201222          1900
    How do I do that? I
    Once again, it depends on why why you want those results.

    Earlier, I showed how to filter if you wanted to ignore certain rows . Now I'm guressing (only guessing; that's all I can do if you dont' explain) that you want to do something a little different. Instead of pretending that (for example)
    plants other than 1900 is not found , it looks like you want to pretend that
    plant *1900 is found* in the given date range.

    Here's one way to do that:
    WITH      params                          AS
    (
         SELECT     DATE '2012-05-01'     AS start_date
         ,     DATE '2012-05-30'     AS end_date
         ,     'IYYYIW'          AS format_text
         FROM     dual
    )
    ,     target_plants          AS
    (
         SELECT  '1900'          AS plant     FROM dual
    --     UNION ALL SELECT '1901'     FROM dual       -- repeat as often as needed
    )
    ,     target_materials     AS
    (
         SELECT     'Label2R1B'     AS material     FROM dual
    --     UNION ALL SELECT 'Label1R2B' FROM dual     -- repeat as often as needed
    )
    ,     target_base_materials     AS
    (
         SELECT     material
         ,     REGEXP_REPLACE ( material
                          , 'R..$'
                          )     AS base_material
         FROM    target_materials
    )
    ,     all_periods          AS
    (
         SELECT DISTINCT
                 TO_CHAR ( start_date + LEVEL - 1
                   , format_text
                   )          AS period_label
         FROM     params
         CONNECT BY     LEVEL     <= 1 + end_date 
                             - start_date
    )
    ,     got_base_material     AS
    (
         SELECT  t.material
         ,     t.plant
         ,     t.quantity
         ,     TO_CHAR (t.posting_Date, p.format_text)     AS period_label
         ,      REGEXP_REPLACE ( t.material
                                 , 'R..$'
                                 )               AS base_material
         FROM    tblcon         t
         JOIN     target_plants  tpl     ON   t.plant          = tpl.plant
         JOIN       params             p     ON   t.posting_date      >= p.start_date
                                          AND  t.posting_date      <= p.end_date
     )
    ,     got_aggregates          AS
    (
         SELECT    FIRST_VALUE ( MAX ( NVL ( m.material
                                            , tm.material
                               )
                            ) 
                         )
                            OVER ( PARTITION BY  tm.base_material
                                      ORDER BY      d.period_label  DESC
                               )          AS last_material
         ,          NVL ( SUM (m.quantity)
                    , 0
                    )               AS period_quantity
         ,           d.period_label
         ,          tp.plant
         ,       NVL ( AVG (SUM (m.quantity))
                             OVER ( PARTITION BY  tm.base_material
                                      ,                  tp.plant
                            )
                    , 0
                    )           AS period_quantity_avg
         ,       NVL ( STDDEV (SUM (quantity))
                                OVER ( PARTITION BY  tm.base_material
                                           ,                     tp.plant
                            )
                    , 0
                    )           AS period_quantity_stddev
         FROM                 all_periods          d
         CROSS JOIN      target_plants              tp
         CROSS JOIN      target_base_materials  tm
         LEFT OUTER JOIN  got_base_material       m   ON   m.period_label  = d.period_label
                                                   AND  m.plant           = tp.plant
                                      AND      m.base_material = tm.base_material
         GROUP BY  tm.base_material
         ,            d.period_label
         ,          tp.plant
    )
    SELECT       last_material
    ,       period_quantity
    ,       period_label
    ,       plant
    FROM       got_aggregates
    WHERE       ABS ( period_quantity
               - period_quantity_avg
               )     <= 2.0 *      period_quantity_stddev
    ORDER BY  last_material
    ,            period_label
    ,          plant
    ;
    This query allows you to look for any number of plants (1 or more) and any number of materials (1 or more) in the same query.
  • 27. Re: Merging two queries?
    978776 Newbie
    Currently Being Moderated
    Hi again Frank,

    I think I explained badly what I'm after.

    What I try to do is to calculate safety stock based on weeks quantity, with respect to outliers.
    The user selects the period and plant in this sub-query. In another main-query as this sub-query is a part of, the user selects among other material.
    This sub-query produces the substrate to the main-query which I later do the calculation itself in.

    If there is only selling for 1 week in a period of 8 weeks, it is important it' taking account of 8 weeks in the calculation.
    If it's not any sale in the selected period, it should show 0 instead of nothing.
    If there has been an release change of material it shall take earlier releaser quantity into account.

    The main-query needs material, sum_of_quantity and plant from this sub-query.

    I have been unclear, in this sub-qury I just need to select the period and plant. The SQL I am looking for is in your last exemple, but I do not have the selection of the material when it is done in the main-query.

    I hope you understand better now what I'm looking for.
  • 28. Re: Merging two queries?
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    570142 wrote:
    ... I hope you understand better now what I'm looking for.
    Sorry, no.

    Point out where the query in my last message, {message:id=10760962} is not producing the right results. Post new sample data and desired output if necessary.
  • 29. Re: Merging two queries?
    978776 Newbie
    Currently Being Moderated
    Hi again,

    I'll try to explain a little more.
    Your last example is exactly what I'm looking for. But the selection of the materials is done today in the main-query. This is to verify that the material is in the material table.

    I want the selection of materials to the main question type like this:
    SELECT tmp_mtrl.material, tmp_mtrl.plant FROM tblMaterial,
    (WITH      params                          AS
    (
         SELECT     DATE '2012-05-01'     AS start_date
         ,     DATE '2012-05-30'     AS end_date
         ,     'IYYYIW'          AS format_text
         FROM     dual
    )
    ,     target_plants          AS
    (
         SELECT  '1900'          AS plant     FROM dual
    --     UNION ALL SELECT '1901'     FROM dual       -- repeat as often as needed
    )
    ,     target_materials     AS
    (
    SELECT     'Label2R1B'     AS material     FROM dual
    --     UNION ALL SELECT 'Label1R2B' FROM dual     -- repeat as often as needed
    )
    ,     target_base_materials     AS
    (
         SELECT     material
         ,     REGEXP_REPLACE ( material
                          , 'R..$'
                          )     AS base_material
         FROM    target_materials
    )
    ,     all_periods          AS
    (
         SELECT DISTINCT
                 TO_CHAR ( start_date + LEVEL - 1
                   , format_text
                   )          AS period_label
         FROM     params
         CONNECT BY     LEVEL     <= 1 + end_date 
                             - start_date
    )
    ,     got_base_material     AS
    (
         SELECT  t.material
         ,     t.plant
         ,     t.quantity
         ,     TO_CHAR (t.posting_Date, p.format_text)     AS period_label
         ,      REGEXP_REPLACE ( t.material
                                 , 'R..$'
                                 )               AS base_material
         FROM    tblcon         t
         JOIN     target_plants  tpl     ON   t.plant          = tpl.plant
         JOIN       params             p     ON   t.posting_date      >= p.start_date
                                          AND  t.posting_date      <= p.end_date
     )
    ,     got_aggregates          AS
    (
         SELECT    FIRST_VALUE ( MAX ( NVL ( m.material
                                            , tm.material
                               )
                            ) 
                         )
                            OVER ( PARTITION BY  tm.base_material
                                      ORDER BY      d.period_label  DESC
                               )          AS last_material
         ,          NVL ( SUM (m.quantity)
                    , 0
                    )               AS period_quantity
         ,           d.period_label
         ,          tp.plant
         ,       NVL ( AVG (SUM (m.quantity))
                             OVER ( PARTITION BY  tm.base_material
                                      ,                  tp.plant
                            )
                    , 0
                    )           AS period_quantity_avg
         ,       NVL ( STDDEV (SUM (quantity))
                                OVER ( PARTITION BY  tm.base_material
                                           ,                     tp.plant
                            )
                    , 0
                    )           AS period_quantity_stddev
         FROM                 all_periods          d
         CROSS JOIN      target_plants              tp
         CROSS JOIN      target_base_materials  tm
         LEFT OUTER JOIN  got_base_material       m   ON   m.period_label  = d.period_label
                                                   AND  m.plant           = tp.plant
                                      AND      m.base_material = tm.base_material
         GROUP BY  tm.base_material
         ,            d.period_label
         ,          tp.plant
    )
    SELECT       last_material As Material
    ,       period_quantity
    ,       period_label
    ,       plant
    FROM       got_aggregates
    WHERE       ABS ( period_quantity
               - period_quantity_avg
               )     <= 2.0 *      period_quantity_stddev
    ORDER BY  last_material
    ,            period_label
    ,          plant ) tmp_mtrl
    WHERE tmp_mtrl.material = tblMaterial.material 
    AND tmp_mtrl.Plant = tblMaterial.plant 
    AND tblMaterial.material = 'Label2R1B'  
    GROUP BY tmp_mtrl.material,   tmp_mtrl.plant
    ;

Legend

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