Need to replace 0 with last non null value in view

Question

Need to replace 0 with last non null value in view

vandana_j79

Mr or Mrs. 500

Points: 536
More actions
November 26, 2010 at 9:07 am

#229179

Hi
I have view which gives me below output but i need to replace 0 with last non zero value. At present it's showing below out put
idend_date Total ATotal B
1131/05/201000
1130/06/20101435.840
1131/07/20101781.010
1131/08/201000
1130/09/201000
2231/05/201000
2230/06/20101210.770
2231/07/201000
2231/08/2010058.75
2230/09/201000
2331/05/201000
2330/06/20106979.780
2331/07/201000
2331/08/201000
2330/09/201013959.560
but i want:
idend_date Total ATotal B
1131/05/201000
1130/06/20101435.840
1131/07/20101781.010
1131/08/20101781.010
1130/09/20101781.010
2231/05/201000
2230/06/20101210.770
2231/07/20101210.770
2231/08/20101210.7758.75
2230/09/20101210.7758.75
2331/05/201000
2330/06/20106979.780
2331/07/20106979.780
2331/08/20106979.780
2330/09/201013959.560
Just wondering how can i see this in a view.
Many Thanks

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

vandana_j79 (11/26/2010)
Just wondering how can i see this in a view.

I know a View would certainly be convenient but, no matter how you slice it, the code to do it in a view is going to have to "look" at the table 5 times to do it in a view.

There is a very high speed method that will look at the table only once to return the proper data but it can't be done in a view but the code is nasty fast and quite short. I might even be able to do it in an mlTVF (multi-line Table Valued Function) which would be nearly as good as a view. Would that work for you?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)