Oracle SQL - SCD track changed attribute through time

Question

i'm hitting the following error:

ORA-30483: window  functions are not allowed here
30483. 00000 -  "window  functions are not allowed here"

Im trying to build a horizontal view of changes to the BOOK_NO and WALK_NO attributes through time.

I thought a reasonable approach would be to RANK the effective_to dates and just decode the book_no||walk_no attribute at that value....but unsure where to go from here!

SQL below:

Select METER_PIT_REF 
,MAX(DECODE( RANK() OVER (PARTITION BY METER_PIT_REF ORDER BY EFFECTIVE_TO DESC),1,BOOK_NO||WALK_NO) )
,MAX(DECODE( RANK() OVER (PARTITION BY METER_PIT_REF ORDER BY EFFECTIVE_TO DESC),2,BOOK_NO||WALK_NO) )
,MAX DECODE( RANK() OVER (PARTITION BY METER_PIT_REF ORDER BY EFFECTIVE_TO DESC),3,BOOK_NO||WALK_NO) )
FROM BW_PIT_BOOKWALK_BRIDGE
WHERE BOOK_NO = 'M04'
GROUP BY METER_PIT_REF

Any advice massively appreciated!

Answer 1

Use a subquery:

Select METER_PIT_REF,
       MAX(CASE WHEN r = 1 THEN BOOK_NO||WALK_NO END),
       MAX(CASE WHEN r = 2 THEN BOOK_NO||WALK_NO END),
       MAX(CASE WHEN r = 3 THEN BOOK_NO||WALK_NO END)
FROM (SELECT b.*,
             RANK() OVER (PARTITION BY METER_PIT_REF ORDER BY EFFECTIVE_TO DESC) as r
      FROM BW_PIT_BOOKWALK_BRIDGE
     ) b
WHERE BOOK_NO = 'M04'
GROUP BY METER_PIT_REF;

I also replaced the DECODE() with CASE. It is the ANSI standard equivalent.