I have an excel formula with nested IF condition that compares current row value with immediate previous row and previous row with previous second row value from same column.

Formula:

=IF(B8<>B7,IF(B7<>B6,B6,B7),B8) 

Tried below query but but getting error "ORA-00920: invalid relational operator"

select MYTABLE.*,
CASE    WHEN MSYMBOL over (order by MDATE)   <> lag(MSYMBOL,1) over (order by MDATE)
        THEN CASE 
                        WHEN lag(MSYMBOL,1) over (order by MDATE) <> lag(MSYMBOL,2) over (order by MDATE)
                        THEN lag(MSYMBOL,2) over (order by MDATE)
                        ELSE lag(MSYMBOL,1) over (order by MDATE)
             END,
        ELSE MSYMBOL over (order by MDATE)
END as FLAG
from MYTABLE

Table creation, insert statements and query are update in this link DB<>FIDDLE.

Appreciate any help with this. Thanks in advance.

Thanks,
Richa

CodePudding user response：

• MSYMBOL over (order by MDATE) is not valid, you just want to use MSYMBOL.
• You have an extra comma after the first END.
• Also, NULL <> something is never true and need to account for that.

You could use:

select MYTABLE.*,
       CASE
       WHEN MSYMBOL <> lag(MSYMBOL,1) over (order by MDATE)
       OR   (MSYMBOL IS NULL AND lag(MSYMBOL,1) over (order by MDATE) IS NOT NULL)
       OR   (MSYMBOL IS NOT NULL AND lag(MSYMBOL,1) over (order by MDATE) IS NULL)
       THEN CASE 
            WHEN lag(MSYMBOL,1) over (order by MDATE) <> lag(MSYMBOL,2) over (order by MDATE)
            OR   (lag(MSYMBOL,1) over (order by MDATE) IS NULL AND lag(MSYMBOL,2) over (order by MDATE) IS NOT NULL)
            OR   (lag(MSYMBOL,1) over (order by MDATE) IS NOT NULL AND lag(MSYMBOL,2) over (order by MDATE) IS NULL)
            THEN lag(MSYMBOL,2) over (order by MDATE)
            ELSE lag(MSYMBOL,1) over (order by MDATE)
            END
       ELSE MSYMBOL
       END as FLAG
from   MYTABLE

But you would probably be better to invert the comparison to use =.

select MYTABLE.*,
       CASE
       WHEN MSYMBOL = lag(MSYMBOL,1) over (order by MDATE)
       THEN MSYMBOL
       WHEN lag(MSYMBOL,1) over (order by MDATE) = lag(MSYMBOL,2) over (order by MDATE)
       THEN lag(MSYMBOL,1) over (order by MDATE)
       ELSE lag(MSYMBOL,2) over (order by MDATE)
       END as FLAG
from   MYTABLE

Which outputs:

MDATE	MSYMBOL	FLAG
01-NOV-21
02-NOV-21
03-NOV-21
04-NOV-21
05-NOV-21	Square
06-NOV-21	Circle
07-NOV-21	Circle	Circle
08-NOV-21	Circle	Circle
09-NOV-21	Square	Circle

db<>fiddle here