All Forums General
tbenard 7 posts Joined 03/13
10 Apr 2013
using a calculation done in the previous row in this row's calculation

I am doing a query in which the results of a previous row calculation are used in the next row calculation (recursive I guess).
 
For example the results should look like this:
 

Item

Loc

Week

A

B

C

Inventory (Prev Inv + A + B - C)

 

1111

aaaa

201301

100

40

60

80

First time prev inv is null

1111

aaaa

201302

30

200

100

210

 

 
So I can use
MIN(Inventory) OVER(ORDER BY item, loc, week ROWS BETWEEN 1 PRECEDING AND 1
       PRECEDING) as PrevInv
 
And the second row will be good (PrevInv + A + B – C)
 
However on the rest of the rows I cannot use the previous row's created Inventory value.

dnoeth 4628 posts Joined 11/04
10 Apr 2013

Based on your narration you might not need recursion, just a modification of your query:

SUM(a+b-c) OVER (ORDER BY week ROWS UNBOUNDED PRECEDING AS Inventory

 

Dieter
 

Dieter

tbenard 7 posts Joined 03/13
11 Apr 2013

Thanks Dieter.  I am always amazed at how quickly and accurately you guys answer posts. 
Unfortunately, I have one more wrinkle... this works fine, but basically I need this inventory to reset with each new item-location combination. 
I have the data ordered by item-location-week.  On the first week for every item location, I calculate inventory for that row a little differently, then I want to do the sum for the remaining weeks of that item-location week.  This works fine for the first item-location, but then on the second item-location I calculate the first week, but the sum just continues from the previous item location.
case when CUST_WEEK = POS.POSMINWK
then -- first week
ZEROIFNULL(WHS_OH_QTY) + ZEROIFNULL(STORE_OH_QTY) + ZEROIFNULL(CUST_DEMAND_FCST)
+ ZEROIFNULL(SHIP_UNSHIP) - ZEROIFNULL(CUST_POS_FCST)
else -- not the first week
SUM(ZEROIFNULL(WHS_OH_QTY) + ZEROIFNULL(STORE_OH_QTY) + ZEROIFNULL(CUST_DEMAND_FCST)
+ ZEROIFNULL(SHIP_UNSHIP) - ZEROIFNULL(CUST_POS_FCST))
OVER (ORDER BY PUBLISH_DATE,CUST_ITEM,CUST_WHS,CUST_WEEK ROWS UNBOUNDED PRECEDING)
end
as PROJ_INV

dnoeth 4628 posts Joined 11/04
11 Apr 2013

 I need this inventory to reset with each new item-location combination

This sounds like you could simply add a "PARTITION BY item, location" to the SUM.
Dieter

Dieter

tbenard 7 posts Joined 03/13
11 Apr 2013

perfect.  thanks again Dieter.

jyo 8 posts Joined 01/14
23 Jan 2014

I will be greatly appreciated if anyone could help on below problem.
I've data senerio like below and assume that there is no data prior to 2011and also previous amt values are derived by using olap preceding function so 2011 rows previous amount is zero and for 2012 aaa row previous amt is 20, now my question is with in the sql coding like preceding olap function is there is any function available to insert a row if a previous row is not found in current year, for ex: from the below senerio since 2011 abc is not found in 2012 year, a new row need to be added with the following values on each column ( 2012     abc  0 (curent amt) 10 (previous amt))
Year    company       amt          previous amt
2011     abc                  10                0
2011     aaa                   20               0
2012     aaa                   30               20
 

dnoeth 4628 posts Joined 11/04
24 Jan 2014

That's quite complicated.
A possible solution is a Cross Join between the list of years and the list of companies to create all combinations, followed by an Left Join to your query and finally the OLAP function the previous value.
Another solution will be based on creating Periods (current row's year, next row's year) and then EXPAND ON by year.
Is this for a view definition?

Dieter

jyo 8 posts Joined 01/14
24 Jan 2014

Thank you Dieter for prompt and quick response.

Can you please explain in detail or a sample code on
second option.
I am framing a query to create a view, this is very complicated qry which joins with huge data tables and at different levels, think that after going through all the process the query retrieved above given sample data. So i was looking for a solution just in the final step if row does not exist in current year when compared to previous year that should be added as a new for current, instead of going through huge table joins.
In the fist option you mentioned cross join years and company that means do i need to have a separate qry again joining with huge tables? Fyi when i checked the difference for two specific years for 2011 there are 30 companies which does not exist in 2012.
Thank you again for your valuable solutions.

dnoeth 4628 posts Joined 11/04
24 Jan 2014

Yes, you need to Cross Join years and companies, preferable not on the big table, better use the comanies table. But of course it's still a an extra step.
Regarding EXPAND ON, i wrote a similar query at 
http://forums.teradata.com/forum/database/query-on-csum
 
Agin, this is one or two extra steps on your large result set just to get those 30 missing rows.

Dieter

jyo 8 posts Joined 01/14
25 Jan 2014

Dieter,
In the above example, I did not specify all combinations of the company categories just not to confuse my situation with too many details.
In my case, looks like EXPAND ON is the best option then the cross join as the final query results are against multiple transactional tables, if I need to fetch distinct combinations of companies and subcategories need to go against multiple huge tables, which  will be pretty much same as what I have written to retrieve the final results.
And thanks for providing the query-on-csum forum, which is pretty much similar to my issue... but please excuse me, I really could not able to follow the code to frame customized Period parameters just for year  and I also searched for other forums in your website all of the examples are related to day or month. Could you please write the code specific to the year for the below example…
Assume Table Name is : CFORECAST
Year     company     amt     previous amt
2011       abc          10             0
2011       aaa          20             0
2012       aaa          30             20
2012       abc           0              10  --- {EXPAND ON  OLAP function Should GENERATE THIS ROW}
I truly appreciate your help...
 
 
 

dnoeth 4628 posts Joined 11/04
26 Jan 2014

You need to cast the year to a date CAST((yr*10000) - 19000000 + 101 AS DATE) use this in the Period and finally EXTRACT the year again:

SELECT company, newyr,
   CASE WHEN yr = newyr  THEN amt ELSE 0 END AS amt,
   CASE WHEN yr = newyr  THEN prevamt ELSE amt END AS prevamt
FROM
 (
   SELECT company, yr, EXTRACT(YEAR FROM BEGIN(pd2)) AS newyr, amt, prevamt
   FROM
    (
      SELECT
         company, yr, amt, prevamt,
         PERIOD(CAST((yr*10000) - 19000000 + 101 AS DATE),
                COALESCE(MIN(CAST((yr*10000) - 19000000 + 101 AS DATE))
                         OVER (PARTITION BY company
                               ORDER BY yr
                               ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING), DATE '2012-01-02')) AS pd
      FROM tab
    ) AS dt
   EXPAND ON pd AS pd2 BY INTERVAL '1' YEAR
 ) AS dt

 

Dieter

jyo 8 posts Joined 01/14
26 Jan 2014

Thank you Deiter, will incorporate above code in my qry and will update my responce. No more words to say for your constant help, you are THE BEST GURU :-) !

jyo 8 posts Joined 01/14
26 Jan 2014

Deiter,
I just modified my code to have automated begin & end years for Period function (as I was getting error msg. beginning bound must be less than the ending bound so figured out automation for end dates which is greater then begin date) and implemented the code as you stated, it did successfully work :-)!! code did generate new rows only for rows which does not have future periods, which is very efficient way to do instead of cross join!! The only thing I observed is when I give 101 for 'end date' conversion to full date, the EXPAND ON function did not generate new rows, when I changed to 201 or 301 etc., did generate new rows; 
SELECTcompany, newyr,
CASE WHEN yr = newyr THEN amt ELSE 0 END AS amt,
CASE WHEN yr = newyr THEN prevamt ELSE amt END AS prevamt FROM
(SELECT company, yr, EXTRACT(YEAR FROM BEGIN(pd2)) AS newyr, amt, prevamt FROM
(SELECT company, yr, amt, prevamt,
PERIOD(CAST((yr*10000) - 19000000 + 101 AS DATE), CAST((FollowingYR*10000) - 19000000 + 201 AS DATE) )) AS pd
FROM tab ) AS dt
EXPAND ON pd AS pd2 BY INTERVAL '1' YEAR ) AS dt
 
Once again Deiter, Thank you for your constant support...

vetriselvan 9 posts Joined 04/14
09 Oct 2014

Hi Dieter,

 

I am trying to calculate the count of difference for the current and previous week like below.

 

 

YEAR    MONTH  WEEK   COUNT   DIFF

2014       04         13       1           1-(12th week count)

2014       04         14       2           1 (2-1)

2014       04         15       2           0 (2-2)

 

can you help me how to achieve this?

 

thanks 

 

Raja_KT 1246 posts Joined 07/09
09 Oct 2014

I m not Dieter :).
you can do something like this:
select year1,month1,week1,count1, (count1-coalesce(max(count1)over(order by count1 rows between  1 preceding and 1 preceding),0) m from your _table
you can also think of using partition by year, month... based on your data.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

vetriselvan 9 posts Joined 04/14
10 Oct 2014

Thanks raja..Its pefect :) :)

jyo 8 posts Joined 01/14
24 Aug 2015

Hi, I've the following situation - I am unable to generate with OREPLACE command. can someone help me...
For example I've a input string like : ('789778,i897990') and my output should look like  ('789778','i897990') (i.e., add single quotes before and after comma); OREPLACE( ('789778,i897990'),',','','') this is giving me an error "9881: Function 'OREPLACE' called with an invalid number or type of parameters).

dnoeth 4628 posts Joined 11/04
24 Aug 2015

Please post new questions a new topic.
You missed some single quotes:

OREPLACE('789778,i897990',',',''',''') 

But I doubt this is what you actually want...
 

Dieter

jyo 8 posts Joined 01/14
24 Aug 2015

Dieter - Could you please let me how to post new topic I am unable to locate.
Meanwhile, here is my thoughts on your question...
Thank you for your quick response. As you suspected true I am using this output of result in a query to fetch corresponding values from database; the reason I am doing this conversion is – one of the application which I am working does not generate correct format in separation of values.
The format which you provided does work correctly, I just added extra stuff to simulate to ('229689968A','150187260A'); when I run just “sel ( (concat('(','''',OREPLACE('229689968A,150187260A',',',''','''),'''', ')') )   ) “ this does give as expected format; but when I applied in query the query does not fetch any values neither it does error out ( as such there are values in TableA for the one which I provided. Could you please let me know how to solve this issue, below is the query..
 
*************************************************************************
select column1 from tableA
where columnc in  (concat('(','''',OREPLACE('229689968A,150187260A',',',''','''),'''', ')') )  
group by 1
 
*************************************************************************
 
 

dnoeth 4628 posts Joined 11/04
24 Aug 2015

To create a new topic:
Go to the forum you want to post the question, e.g. General, above the first question there should be a big button CREATE A NEW FORUM TOPIC.
 
You can't pass a list of values in a single string to an IN, even if you add quotes it's still a single string.
You need to split it, e.g.

WHERE  columnc IN
 (
   SELECT TRIM(token) 
   FROM TABLE (STRTOK_SPLIT_TO_TABLE(1, '229689968A,150187260A', ',')
        RETURNS (outkey INTEGER,
                 tokennum INTEGER,
                 token VARCHAR(50) CHARACTER SET UNICODE)
              ) AS d 
)

 

Dieter

jyo 8 posts Joined 01/14
25 Aug 2015

The above solution did work thank you very much. Just a performance question - when I run the above code as is the response time is quick but when I assign it to where clause that is to TAbleA where clause, the reponse time is very slow; FYI - The tableA contains more than 40 billion data; also if I assign values directly to the tableA the response time is quick too (i.e., where columnc in ('229689968A','150187260A')); whether there is any feasibility to make the query response time fater?
 
select column1 from tableA
where columnc in   (SELECT TRIM(token) FROM TABLE (STRTOK_SPLIT_TO_TABLE(1, '229689968A,150187260A', ',')    RETURNS (outkey INTEGER,
                 tokennum INTEGER,token VARCHAR(50) CHARACTER SET UNICODE)) AS d) group by 1
Thank you Dieter for the details to open new forum topic, I did find it. For next topic I will open new forum.
Thank you very much for your valuable responses.

You must sign in to leave a comment.