Home » SQL & PL/SQL » SQL & PL/SQL » select with while (Oracle 12c)
select with while [message #681477] Wed, 22 July 2020 04:04 Go to next message
mape
Messages: 262
Registered: July 2006
Location: Slovakia
Senior Member
Hello


I would like to get an advice.
create table xxx_mape_123
(month_key number , flag varchar2(10) )

  insert into xxx_mape_123 values (202001, NULL);
  insert into xxx_mape_123 values (202002, NULL);
  insert into xxx_mape_123 values (202003, 'Y');
  insert into xxx_mape_123 values (202004, NULL);
  insert into xxx_mape_123 values (202005, NULL);
  insert into xxx_mape_123 values (202006, 'Y/N');
  insert into xxx_mape_123 values (202007, NULL);
  insert into xxx_mape_123 values (202008, NULL);

    select * from xxx_mape_123
     order by month_key;


And I would like to write select where logic will be:
- when find not null FLAG (order by month_key) display next FLAG like you finded before

The result should be like this:
202001 NULL
202002 NULL
202003 Y
202004 Y
202005 Y
202007 Y/N
202008 Y/N

Does anybody know how to do that ??

Thanks a lot
Regards
MArtin
Re: select with while [message #681478 is a reply to message #681477] Wed, 22 July 2020 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> select month_key, last_value(flag ignore  nulls) over (order by month_key) flag
  2  from xxx_mape_123
  3  order by month_key;
 MONTH_KEY FLAG
---------- ----------
    202001
    202002
    202003 Y
    202004 Y
    202005 Y
    202006 Y/N
    202007 Y/N
    202008 Y/N

8 rows selected.
Re: select with while [message #681480 is a reply to message #681478] Wed, 22 July 2020 05:12 Go to previous messageGo to next message
mape
Messages: 262
Registered: July 2006
Location: Slovakia
Senior Member
This is a great select Smile

thanks a lot
Re: select with while [message #681481 is a reply to message #681480] Wed, 22 July 2020 05:45 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thank Oracle to provide such useful functions. Smile

Re: select with while [message #681489 is a reply to message #681477] Wed, 22 July 2020 12:23 Go to previous message
EdStevens
Messages: 1250
Registered: September 2013
Senior Member
I would suggest that your MONTH_KEY be a DATE, not a NUMBER. You use may not care about day, hour, minute, and second, but you can always default them to day 1 and time zero. Hardly a week goes by that, on some oracle forum, someone will be searching for a solution to a problem that was self-inflicted by not using DATE to store dates.
Previous Topic: ORA-00932: inconsistent datatypes: expected - got NCLOB
Next Topic: API for Progress Line
Goto Forum:
  


Current Time: Mon Sep 28 13:22:27 CDT 2020