Database

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

2 weeks ago

2 weeks ago

I have a table as similar to below and

1. I would need to update the CASE_ IN when there is a null then I must update it with previous non-null value.

Will this below SQL work?

last_value(case_in ignore nulls) over (partition by ID order by date rows unbounded preceding)

2. Next, I need to update the BR_CD. any values between 0-100 and NULL are invalid hence those needs to be updated to last previous BR_CD. How do I achieve this, please can you help? I have given the sample input and output.

ID | CASE_IN | BR_CD | Date |

1 | Y | 1234 | 1/2/12 |

1 | Y | 10 | 1/2/12 |

1 | 20 | 1/3/12 | |

1 | 30 | 1/4/12 | |

1 | Y | 10 | 2/8/12 |

2 | Y | 5678 | 3/3/18 |

2 | Y | ? | 3/5/18 |

2 | Y | 8765 | 3/7/18 |

Expected Output

ID | CASE_IN | BR_CD | Date |

1 | Y | 1234 | 1/2/12 |

1 | Y | 1234 | 1/2/12 |

1 | Y | 1234 | 1/3/12 |

1 | Y | 1234 | 1/4/12 |

1 | Y | 1234 | 2/8/12 |

2 | Y | 5678 | 3/3/18 |

2 | Y | 5678 | 3/5/18 |

2 | Y | 8765 | 3/7/18 |

Solved! Go to Solution.

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

2 weeks ago

1 ACCEPTED SOLUTION

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

2 weeks ago

2 weeks ago

Re: update current row with Previous row Valid value

The LAST_VALUE should return what you want and for the 2nd case you simply modify it to:

last_value(case when case_in not between 0 and 100 then case_in end ignore nulls) over (partition by ID order by date rows unbounded preceding)

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

2 weeks ago

2 weeks ago

Re: update current row with Previous row Valid value

should it be br_cd? because I need to update the br_Cd based on case_in

last_value(case when case_in not between 0 and 100 then br_cd end ignore nulls) over (partition by ID order by date rows unbounded preceding)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

2 weeks ago

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

2 weeks ago

2 weeks ago

Re: update current row with Previous row Valid value

Dieter thank you so much! But I need to update the br_cd based on the case_in

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

2 weeks ago

2 weeks ago

Re: update current row with Previous row Valid value

Your expected output calculates both columns independently...

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

2 weeks ago

2 weeks ago

Re: update current row with Previous row Valid value

Thank you dieter! you are amazing. The solution worked