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!

From Keyword not found where expected . Am struggling with this error :(

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,
    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
     
  2. sambuduk

    sambuduk Forum Advisor

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

    Put the alias names in " " instead of '' ..

    Ex: DA_MWH "Yesterday_Da_Mwh",


    Regards
    Sambasiva Reddy.K
     
    Ecor and balu2517 like this.
  3. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Please use code tags "["highlight=sql"]" . . . your code here . . . "["/highlight"]" and indent lines in order to make your code readable.
     
    Ecor and balu2517 like this.
  4. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    In addition, given the fact that there is no space or special character within the column aliases in your code, you can simply write them without double quotes

    Code (SQL):
    SELECT  t1.first_name empFname
    FROM    hr.employees t1
    WHERE   rownum <= 5;

    EMPFNAME
    --------------------
    Ellen
    Sundar
    Mozhe
    David
    Hermann
     
    Which does the same thing as
    Code (SQL):

    SELECT  t1.first_name "empFname"
    FROM    hr.employees t1
    WHERE   rownum <= 5;

    empFname
    --------------------
    Ellen
    Sundar
    Mozhe
    David
    Hermann

    SQL>
     
     
    balu2517 likes this.
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Not really, as the quoted string preserves the case as the unquoted string does not. Also when quoting column aliases in mixed case you'll need to use the quotes when referencing those column names:

    Code (SQL):
    SQL> SELECT * FROM
      2  (SELECT empno "enbr", ename enm, sal salary, hiredate hdt
      3  FROM emp)
      4  WHERE enbr < 7400;
    WHERE enbr < 7400
          *
    ERROR at line 4:
    ORA-00904: "ENBR": invalid identifier

    SQL>
    SQL> SELECT * FROM
      2  (SELECT empno "enbr", ename enm, sal salary, hiredate hdt
      3  FROM emp)
      4  WHERE "enbr" < 7400;
     
          enbr ENM            SALARY HDT
    ---------- ---------- ---------- ---------
          7369 SMITH             800 17-DEC-80
     
    SQL>
     
    balu2517 likes this.
  6. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Thanks for the remark,

    Personally I use

    - lowercase for column/alias and
    - uppercase for SQL/PL/SQL keywords and also PL/SQL constants and exception names

    IMHO, it makes the code more readable
     
    balu2517 likes this.
  7. balu2517

    balu2517 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    It helped me a lot.. :)