Iterating the Leading rows in SQL - get the first non-zero data

Database
N/A

Iterating the Leading rows in SQL - get the first non-zero data

Hi All,
 
I have the following input data.

 

create multiset table tmp_input_data
(dt_fld date,
  amt_fld decimal(38,10));
 

insert into tmp_input_data(dt_fld, amt_fld)
select '01-May-2016', 0
union
select '03-May-2016', 0
union
select '06-May-2016', 19.10
union
select '07-May-2016', 0
union
select '10-May-2016', 0
union
select '12-May-2016', 0
union
select '15-May-2016', 26.20
union
select '17-May-2016', 0
union
select '18-May-2016', 0
union
select '21-May-2016', 0
union
select '22-May-2016', 0
union
select '25-May-2016', 0
union
select '27-May-2016', 35.34
 
With this, I would like to have the output as given below. I want to iterate the immediate Following rows, if it is not equal to zero, set that as the current row output and go on with the next rows. I need this as SQL statement only.
 
dt_fld, amt_fld
01-May-2016, 19.10
03-May-2016, 19.10
06-May-2016, 19.10
07-May-2016, 26.20
10-May-2016, 26.20
12-May-2016, 26.20
15-May-2016, 26.20
17-May-2016, 35.34
18-May-2016, 35.34
21-May-2016, 35.34
22-May-2016, 35.34
25-May-2016, 35.34
27-May-2016, 35.34
 
I can sort the input data set in Date descending order, write recursive query and get it, but it is going in infinite loop, the query runs on non-stop.I have also tried with LAST_DAY function, but SQL Assistant doesn't recognize it, throws invalid function error message.


Please share any other approach we have for this, thank you in advance.


Accepted Solutions
N/A

Re: Iterating the Leading rows in SQL - get the first non-zero data

LAST_DAY can't be used for this, maybe you mean LAST_VALUE, which is implemented since TD14.10:

 

LAST_VALUE(NULLIF(amt_fld,0) IGNORE NULLS) OVER (ORDER BY dt_fld DESC)

Before  you can use RESET WHEN:

MAX(amt_fld) OVER (ORDER BY dt_fld DESC RESET WHEN amt_fld <> 0 )
1 ACCEPTED SOLUTION
6 REPLIES
N/A

Re: Iterating the Leading rows in SQL - get the first non-zero data

LAST_DAY can't be used for this, maybe you mean LAST_VALUE, which is implemented since TD14.10:

 

LAST_VALUE(NULLIF(amt_fld,0) IGNORE NULLS) OVER (ORDER BY dt_fld DESC)

Before  you can use RESET WHEN:

MAX(amt_fld) OVER (ORDER BY dt_fld DESC RESET WHEN amt_fld <> 0 )
N/A

Re: Iterating the Leading rows in SQL - get the first non-zero data

Hi dnoeth,

 

Yes, I meant the LAST_VALUE analytical function, my database version doesn't support it unfortunately.

 

I tired with MAX() function earlier, but didn't use RESET, that is the mistake, thank you so much for the help!!!

N/A

Re: Iterating the Leading rows in SQL - get the first non-zero data

HI dnoeth,

 

I'm not getting the expected output, I've tried with the below SQL, could you please try from your end with the sample data given? Thank you in advance.

 

select dt_fld, amt_fld,
max(amt_fld) over (order by dt_fld desc RESET WHEN amt_fld<> 0) as amt_calc
from tmp_input_data
order by dt_fld

N/A

Re: Iterating the Leading rows in SQL - get the first non-zero data

It's working fine on your data (of course the Insert doesn't work as-is).

What result do you get instead?

N/A

Re: Iterating the Leading rows in SQL - get the first non-zero data

Hi dnoeth,

 

It works only when we have positive data in the table, for negative Min() works well, to have both working, I have used SUM(). It looks good now, thank you so much for the help :)

N/A

Re: Iterating the Leading rows in SQL - get the first non-zero data

Ups, of course, I didn't expect negative values :)