1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-00904: "RN": invalid identifier

Discussion in 'SQL PL/SQL' started by balu2517, Aug 22, 2013.

  1. balu2517

    balu2517 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    select HOUR_ENDING"HE",TO_CHAR(Yesterday_Da_Mwh,'999,999')"Yesterday_(DA)",
    TO_CHAR(Yesterday_ACTUAL_MWH,'999,999')"Yesterday_(RT)",
    TO_CHAR(Yesterday_Rt_Variation,'999,999')"Yesterday_RT Variation",
    TO_CHAR(Today_DA_MWH,'999,999')"Today (DA)",
    TO_CHAR(Today_ACTUAL_MWH,'999,999')"Today (RT)",
    TO_CHAR(Today_Rt_Variation,'999,999')"Today_RT Variation"
    from (
    SELECT
    HOUR_ENDING,
    DA_MWH"Yesterday_Da_Mwh",
    ACTUAL_MWH"Yesterday_ACTUAL_MWH",
    ( DA_MWH - ACTUAL_MWH )"Yesterday_Rt_Variation",
    lead(DA_MWH) over (order by hour_ending ) as"Today_DA_MWH",
    lead(ACTUAL_MWH) over (order by hour_ending ) as"Today_ACTUAL_MWH",
    (lead(DA_MWH) over (order by hour_ending ) - lead(ACTUAL_MWH) over (order by hour_ending ) )"Today_Rt_Variation",
    DA_MWH_PRICE"Yes_DA_MWH_PRICE",
    ACTUAL_MWH_PRICE"Yes_ACTUAL_MWH_PRICE",
    ( ACTUAL_MWH_PRICE - DA_MWH_PRICE )"Yes_Rt_Price_Variation",
    lead(DA_MWH_PRICE) over (order by hour_ending ) as"Today_DA_Price_MWH",
    lead(ACTUAL_MWH_PRICE) over (order by hour_ending ) as"Today_ACTUAL_Price_MWH",
    (lead(ACTUAL_MWH_PRICE) over (order by hour_ending ) - lead(DA_MWH_PRICE) over (order by hour_ending ) )"Today_Rt_Price_Var",
    row_number() over (partition by hour_ending order by forecast_date asc)"rn"
    FROM USGEN_LOADS_HUB_PRICES
    Where
    forecast_date >= trunc(sysdate -1)
    and forecast_date <= trunc(sysdate) + 1/24
    order by HOUR_ENDING
    )
    where
    rn = 1
    and hour_ending < 25
     
  2. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi

    You are using rn = 1 in outer Query where clause . But you are not selecting that " RN " column.

    So select that RN in outer query then run it will work.


    Regards
    Sambasiva Reddy.k
     
    balu2517 likes this.
  3. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Hi,

    This is completely wrong, there is no requirement to have RN in the SELECT list. And here is an example that uses RN in the WHERE clause of the outer query but not in the SELECT list
    Code (SQL):

    SELECT  t2.employee_id, t2.first_name
    FROM
    (
        SELECT  t1.employee_id  ,
                t1.first_name   ,
                ROW_NUMBER()
                    OVER
                    (
                        PARTITION BY department_id
                        ORDER BY department_id
                    ) rn
        FROM    hr.employees t1
    ) t2
    WHERE rn <= 2;


    EMPLOYEE_ID FIRST_NAME
    ----------- --------------------
        200 Jennifer
        201 Michael
        202 Pat
        114 Den
        119 Karen
        203 Susan
        198 Donald
        197 Kevin
        103 Alexander
        107 Diana
        204 Hermann
        145 John
        179 Charles
        100 Steven
        102 Lex
        108 Nancy
        113 Luis
        205 Shelley
        206 William
        178 Kimberely

    20 ROWS selected.

    SQL>
     
    The problem in his code is that inside the query RN has been enclosed by double quotes but in the WHERE clause it has been referenced without double quotes.

    Therefore, instead of
    Code (SQL):
    WHERE rn = 1 AND hour_ending < 25
    He has to write
    Code (SQL):
    WHERE "rn" = 1 AND hour_ending < 25
    And above all, I don't see any reason to use double quotes for simple column aliases.

    Also, the forum gives the possibility of using code tags with a nice syntax highlighting so, IMHO, it would be a good idea to use code tags and indent the lines accordingly as it makes the code considerably more readable for those who try to read and understand the problem.
     
    balu2517 likes this.