1 2 3 37 Replies Latest reply: Jan 4, 2013 2:58 PM by 978776 Go to original post
• 15. Re: Merging two queries?
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?
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?
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?
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?
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?
Big Thanks!

One question...
What is the function for CROSS JOIN params p? I do not understand it ...
• 21. Re: Merging two queries?
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?
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
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?
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?
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?
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?
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?
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?
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
;``````
1 2 3