OraFAQ Forum: SQL & PL/SQL » How to fill a column with given values?

Home » SQL & PL/SQL » SQL & PL/SQL » How to fill a column with given values?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to fill a column with given values? [message #645355]

Fri, 04 December 2015 04:26

quirks
Messages: 85
Registered: October 2014

Member

Hello,

I've got a tricky Problem (at least it seems to me like one). I've got the Table below.

IDX|NEW_FLAG|VALUE
------------------
  1|        |
  2|       1|    a
  3|        |
  4|        |
  5|       1|
  6|        |
  7|        |
  8|       1|    b
  9|        |
 10|        |

I want to calculate a new column in which I insert the last (ordered by IDX) occurrence of VALUE that matches the last instance of NEW_FLAG. I know this explanation sounds strange, so I've done a little illustration of the expected output.

IDX|NEW_FLAG|VALUE|CALC_VALUE
---|--------|-----|----------
  1|        |     |          
  2|       1|    a|         a
  3|        |     |         a
  4|        |     |         a
  5|       1|     |          
  6|        |     |          
  7|        |     |          
  8|       1|    b|         b
  9|        |     |         b
 10|        |     |         b

Here is a little script to create MYTABLE:

Toggle Spoiler

And here is my best attempt (but I've problems with the NULL value).

Toggle Spoiler

I've mangled my mind, but can not find a working approach. Does anyone of you got an idea how to achieve my goal?

Thanks in advance and best regards
quirks

[Updated on: Fri, 04 December 2015 04:36]

Report message to a moderator

Re: How to fill a column with given values? [message #645356 is a reply to message #645355]

Fri, 04 December 2015 05:04

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Thanks for the test case. Spot on!

select  idx
      ,new_flag
      ,value
      ,case
                when new_flag is not null
                then value
                else lag(value) ignore nulls over (order by idx)
        end calc_value
      ,first_value(value) over ( from mytable;
with t as
        (select idx
              , new_flag
              , value
              , sum(new_flag) over(order by idx) grp
        from    mytable
        )
select  idx
      ,new_flag
      ,value
      ,case
                when new_flag is not null
                then value
                else lag(value) ignore nulls over (partition by grp order by idx) 
        end c1
       ,first_value(value) over(partition by grp order by idx)  c2
from    t
order by idx

Report message to a moderator

Re: How to fill a column with given values? [message #645359 is a reply to message #645356]

Fri, 04 December 2015 06:13

quirks
Messages: 85
Registered: October 2014

Member

Thank you very much for removing my mental deadlock!

I never thought about summing up the NEW_FLAG to get distinctive groups. Using it within WITH makes it even more charming.

I take your second way to receive the result, so my final SQL looks like this:

WITH T
     AS (SELECT IDX
               ,NEW_FLAG
               ,VALUE
               ,SUM(NEW_FLAG) OVER (ORDER BY IDX) GRP
         FROM   MYTABLE)
SELECT   IDX
        ,NEW_FLAG
        ,VALUE
        ,FIRST_VALUE(VALUE) OVER(PARTITION BY GRP ORDER BY IDX) CALC_VALUE
FROM     T
ORDER BY IDX

Report message to a moderator

Re: How to fill a column with given values? [message #645365 is a reply to message #645359]

Fri, 04 December 2015 07:04

Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA

Senior Member

Model solution:

select  *
  from  tbl
  model
    dimension by(idx)
    measures(new_flag,value,value calc_value)
    rules(
          calc_value[any] order by idx = case new_flag[cv()]
                                           when 1 then value[cv()]
                                           else calc_value[cv() - 1]
                                         end
         )
  order by idx
/

Report message to a moderator

Previous Topic:	Grouping with sum and retain first row
Next Topic:	Update multiple rows with multi join

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 13 04:27:19 CDT 2026