12 Replies Latest reply on Dec 4, 2018 8:04 AM by BluShadow

# calculate and produce result

Ok, I have simplified requirement now.

Below is table and expected output

```WITH t AS
(SELECT 1 AS O, 1 AS L, 'X' AS P, 250 AS Q, 4 AS GS, 50 AS gcq, 200 AS TR, 7 AS SI, 'Y' AS FLAG, SYSDATE AS rd FROM DUAL UNION
SELECT 2 AS O, 1 AS L, 'Y' AS P, 300 AS Q, 7 AS GS, 50 AS gcq, 200 AS TR, 7 AS SI, 'Y' AS FLAG, SYSDATE AS rd FROM DUAL

) SELECT t1.lvl, t.O, t.L, t.P, t.q, t.rd FROM t, LATERAL (SELECT LEVEL lvl FROM DUAL CONNECT BY LEVEL <= CASE WHEN t.flag = 'N' THEN 1
WHEN t.flag = 'Y' AND t.q > t.tr THEN CASE WHEN t.q / t.gcq > t.gs THEN t.gs END
WHEN t.flag = 'Y' AND t.q > t.tr THEN CASE WHEN t.q / t.gcq < t.gs THEN t.q / t.gcq END
END) t1;

Expected output -

For O = 1 Need 4 lines as 50 qty in first 3 as gcq is 50 and in last line 100 to make it equal to Q i.e. 250

In date column add 7 working day to each line as well.

LVL O L P Q RD

1 1 1 X 50          03-DEC-2018 10:51:26 AM
2 1 1 X 50          12-DEC-2018 10:51:26 AM
3 1 1 X 50          21-DEC-2018 10:51:26 AM
4 1 1 X 100         01-JAN-2019 10:51:26 AM

For O = 2 split in 6 lines on basis of when q > tr and q/gcq < gs

1 2 1 Y 50           03-DEC-2018 10:51:26 AM
2 2 1 Y 50           12-DEC-2018 10:51:26 AM
3 2 1 Y 50           21-DEC-2018 10:51:26 AM
4 2 1 Y 50           01-JAN-2019 10:51:26 AM
5 2 1 Y 50 10-JAN-2019 10:51:26 AM
6 2 1 Y 50 21-JAN-2019 10:51:26 AM
```
• ###### 1. Re: calculate and produce result

Hi,

Thanks for posting the sample data.

Don't forget to post the exact results you want from that sample data.  Format the post so it's clear what values are in which columns.  If the results depend on SYSDATE, then say which SYSDATE produces the results you post; e.g. "If I run the query at 13:00 on December 2, 1028, then the output should be …".

Always say what version of Oracle you're using (e.g. 12.2.0.2.0).

See the forum FAQ: Re: 2. How do I ask a question on the forums?

• ###### 2. Re: calculate and produce result

3134376 wrote:

Hi All,

I hve two table as below , I need to build a logic in sql only to do calcuation on quantity and then on basis of rule shown below want to return additional rows.

WITH t1 AS

( SELECT 1 AS order_number, 1 AS line_number, 'x' AS item, 250 AS quantity, 3 split_count, 'c' criteria, 't1' criteria_tier FROM DUAL UNION

SELECT 1 AS order_number, 2 AS line_number, 'y' AS item, 250 AS quantity, 3 split_count, 'c' criteria, 't1' criteria_tier FROM DUAL UNION

SELECT 1 AS order_number, 3 AS line_number, 'z' AS item, 250 AS quantity, 10 split_count, 'c' criteria,'t1' criteria_tier FROM DUAL UNION

SELECT 1 AS order_number, 4 AS line_number, 'a' AS item, 250 AS quantity, 10 split_count, 'c' criteria, 't1' criteria_tier FROM DUAL

)

SELECT * FROM t1;

WITH t2 AS

(SELECT 'x' AS item, 'c' AS criteria, 100 AS tier1_qty, 100 AS tier1_qty_tr, NULL AS tier2_qty, NULL AS tier2_qty_tr, '7' AS split_interval, 'N' AS split_allowed FROM DUAL UNION

SELECT 'y' AS item, 'c' AS criteria, 50 AS tier1_qty, 300 AS tier1_qty_tr, NULL AS tier2_qty, NULL AS tier2_qty_tr, '7' AS split_interval, 'Y' AS split_allowed FROM DUAL UNION

SELECT 'z' AS item, 'c' AS criteria, 50 AS tier1_qty, 300 AS tier1_qty_tr, NULL AS tier2_qty, NULL AS tier2_qty_tr, '7' AS split_interval, 'Y' AS split_allowed FROM DUAL UNION

SELECT 'a' AS item, 'c' AS criteria, 50 AS tier1_qty, 200 AS tier1_qty_tr, NULL AS tier2_qty, NULL AS tier2_qty_tr, '7' AS split_interval, 'Y' AS split_allowed FROM DUAL))

SELECT * FROM t2;

Match

t1.item, criteria, criteria_tier = t2.item, criteria, criteria_tier i.e (criteria_tier1 OR 2)

Rule 1 -

If t2.split_allowed = N return as below

ORDER_NUMBER LINE_NUMBER ITEM QUANTITY delivery_date

1 1 x 250 SYSDATE

Rule 2 -

If t2.split_allowed = Y and t21.tier1_qty_tr < t1.quantity then return as below

ORDER_NUMBER LINE_NUMBER ITEM QUANTITY delivery_date

1 2 y 250 SYSDATE

Rule 3 -

If t2.split_allowed = Y and t21.tier1_qty_tr > t1.quantity and t1.quantity / t2.tier1_qty > t1.split_count then generate t1.split_count + 1 rows as below.

ORDER_NUMBER LINE_NUMBER ITEM QUANTITY delivery_date

1 2 y 50 SYSDATE

1 2.1 y 50 SYSDATE + 7)

1 2.2 y 50 (SYSDATE + 7) + 7

1 2.3 y 100 ((SYSDATE + 7) + 7) + 7 exculde weekend while adding 7 in next rows

If t2.split_allowed = Y and t21.tier1_qty_tr > t1.quantity and t1.quantity / t2.tier1_qty < t1.split_count then generate rows as count of the calculation.

250/50

ORDER_NUMBER LINE_NUMBER ITEM QUANTITY delivery_date

1 3 z 50 SYSDATE

1 3.1 z 50 SYSDATE + 7)

1 3.2 z 50 (SYSDATE + 7) + 7

1 3.3 z 50 ((SYSDATE + 7) + 7) + 7

1 3.4 z 50 (((SYSDATE + 7) + 7) + 7) + 7 exculde weekend while adding 7 in next rows

What are the expected & desired results based upon sample data?

• ###### 3. Re: calculate and produce result

Hi,

3134376 wrote:

Hi All,

...

If t2.split_allowed = Y and t21.tier1_qty_tr > t1.quantity and t1.quantity / t2.tier1_qty < t1.split_count then generate rows as count of the calculation.

250/50

ORDER_NUMBER LINE_NUMBER ITEM QUANTITY delivery_date

1 3 z 50 SYSDATE

1 3.1 z 50 SYSDATE + 7)

1 3.2 z 50 (SYSDATE + 7) + 7

1 3.3 z 50 ((SYSDATE + 7) + 7) + 7

1 3.4 z 50 (((SYSDATE + 7) + 7) + 7) + 7 exculde weekend while adding 7 in next rows

You need to be more precise.

How many output rows will you want?

How is quantity calculated? Explain in general terms, and give a specific example.

What does " exculde weekend while adding 7" mean?  Explain in general terms, and give a specific example.

Why don't you want any output for item='a'?

I don't see anything in your explanation about some columns, like order_number, criteria and criteria_tier.  Do they play any role in this problem (aside from being displayed in the final output)?  If so, explain.

• ###### 4. Re: calculate and produce result

Hi,

Please don't make changes to your posts.  It's very confusing, and easy to miss.  If you need to make corrections to an existing message, post a new reply.  The only change you should make to an existing message is to add a line like "EDIT: See reply #4 for corrections".

Here's one way to do what you requested:

SELECT    c.lvl

,         t.o

,         t.l

,         t.p

,         c.q

,         cf.dt

FROM      t

CROSS APPLY (

SELECT  LEVEL  AS lvl

,       CASE

WHEN  CONNECT_BY_ISLEAF = 0

THEN  gcq

ELSE  q - (gcq * (LEVEL - 1))

END    AS q

FROM    dual

CONNECT BY   t.flag    = 'Y'

AND  t.q       > t.tr

AND  LEVEL     <= LEAST ( t.gs

, CEIL (t.q / t.gcq)

)

)       c

JOIN      calendar  cp  ON   cp.dt           = TRUNC (t.rd)

JOIN      calendar  cf  ON   cf.bus_day_cnt  = cp.bus_day_cnt + (7 * (c.lvl - 1))

AND  cf.day_type     = 'B'

ORDER BY  t.o

,         c.lvl

;

For the calendar table, see PL/SQL function to calculate non-working days  (reply #4).

If t is populated on December 3, 2018, then the output from the query above is

LVL     O    L P        Q DT

---- ---- ---- --- ------ -----------

1    1    1 X       50 03-Dec-2018

2    1    1 X       50 12-Dec-2018

3    1    1 X       50 21-Dec-2018

4    1    1 X      100 03-Jan-2019

1    2    1 Y       50 03-Dec-2018

2    2    1 Y       50 12-Dec-2018

3    2    1 Y       50 21-Dec-2018

4    2    1 Y       50 03-Jan-2019

5    2    1 Y       50 14-Jan-2019

6    2    1 Y       50 24-Jan-2019

In my calendar table, there are 3 holidays between December 3, 2018 and January 24, 2019; that's why the dt column is up to 3 days later than what you posted.  If you create a calendar table without those holidays (or with a column like bus_day_cnt that ignores holidays), then you can get the exact results you requested.

You can do this job without a calendar table, but it's much more complicated and less efficient.

[EDIT: Actually, it's not all that complicated.  See reply #7, below.)

• ###### 5. Re: calculate and produce result

John Thorton wrote:

Out of the 40 topics the OP created  (20 topics per page history), 25 of them are left UNANSWERED

• ###### 6. Re: calculate and produce result

Why to mark answer if solution not given ?

• ###### 7. Re: calculate and produce result

Hi,

Here's a way to do it without a calendar table, using a recursive WITH clause instead of CONNECT BY:

WITH  r    (o, l, p, q, gcq, rd, lvl, max_lvl)  AS

(

SELECT  o, l, p, q, gcq, rd

,       1    AS lvl

,       CASE

WHEN  flag = 'N'

OR    q    <= tr

THEN  1

ELSE  LEAST ( gs

, CEIL (q / gcq)

)

END  AS max_lvl

FROM    t

UNION ALL

SELECT  o, l, p, q, gcq

,       rd + CASE  TO_CHAR (rd, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH')

WHEN  'THU'  THEN  11

WHEN  'FRI'  THEN  11

WHEN  'SAT'  THEN  10

ELSE   9

END  AS rd

,       lvl + 1   AS lvl

,       max_lvl

FROM    r

WHERE   lvl  < max_lvl

)

SELECT    lvl, o, l, p

,         CASE

WHEN  lvl  < max_lvl

THEN  gcq

ELSE  q - (gcq * (lvl - 1))

END  AS q

,         rd

FROM      r

ORDER BY  o, lvl

;

Output (exactly as requested):

LVL     O    L P        Q RD

---- ---- ---- --- ------ -----------

1    1    1 X       50 03-Dec-2018

2    1    1 X       50 12-Dec-2018

3    1    1 X       50 21-Dec-2018

4    1    1 X      100 01-Jan-2019

1    2    1 Y       50 03-Dec-2018

2    2    1 Y       50 12-Dec-2018

3    2    1 Y       50 21-Dec-2018

4    2    1 Y       50 01-Jan-2019

5    2    1 Y       50 10-Jan-2019

6    2    1 Y       50 21-Jan-2019

Whether you use CONNECT BY or recursive WITH, I still recommend using a calendar table, especially if you need to consider holidays.

What results would you want if t.rd is a Saturday or Sunday?  You may need to adjust the CASE expression that generates rd slightly.

• ###### 8. Re: calculate and produce result

3134376 wrote:

Why to mark answer if solution not given ?

Agreed on your point.  It's when the person DISAPPEARS from not answering people's questions to continue is the issue.  Most of the threads people respond waiting for you to come back to answer yet nothing from you.

• ###### 9. Re: calculate and produce result

This forum is a gold mine can you tell me how I can search it effectively for my needs

@ Moderators,

Modify the way of searching this forum and don't give me silly excuses.

Shall I raise a SR for same ?

• ###### 10. Re: calculate and produce result

Hi,

3134376 wrote:

...

Modify the way of searching this forum and don't give me silly excuses.

Shall I raise a SR for same ?

Sorry, I don't understand.

If you have trouble using some feature of this forum (such as the search feature), or if you want to suggest improvements, create a new thread in the Community Feedback Forum:

By the way, if you do have trouble using the search feature, you've got lots of company.  Usually, I don't even try the forum search feature; I use a generic search engine.  Often, most of the hits are from this forum.

• ###### 11. Re: calculate and produce result

Why I can't access link given here -

• ###### 12. Re: calculate and produce result

3134376 wrote:

Why I can't access link given here -

Because that link goes to a post on the MOSC community, for which you will need to be a paying customer with a Customer Support Identifier.  If your company has a support contract with Oracle (which they should have if they are using it commercially) then you need to find out who is the "Oracle Administrator" within your company and ask for them to add you to the list of users within your company who can access Oracle Support (MOS) and then you'll be able to also access the MOS community (MOSC)