DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: ORA-30483: window functions are not allowed here

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

    ORA-30483: window functions are not allowed here

    Hi All,
    Wht am i doin wrong?
    This is URGENT trow some light...

    This is wht i have added
    "trim(to_char(SUM(MIS_NVALUE + LAG(MIS_NVALUE) OVER (MIS_NVALUE ORDER BY YEAR_MONTH)) / 2/1000000,'99999999999990.99'))" after this iam getting error.

    SELECT /*+FIRST_ROWS*/
    REF_RPT_FORMAT.rpt_seq_code,MV_MIS_SOB2.year_month,case when
    ref_rpt_format.rpt_seq_code in(1,7,21,36) THEN
    trim(to_char(decode(SUM(mis_nvalue),0,' '))) when
    ref_rpt_format.rpt_seq_code in(2,3,8,15,16,17,37,38,46,50,52,54) THEN
    trim(to_char(SUM(mis_nvalue))) when ref_rpt_format.rpt_seq_code in(29) THEN
    trim(to_char(SUM(mis_nvalue),'99999999999990.99')) when
    ref_rpt_format.rpt_seq_code in(9,31,32,33,40,43,44,30) THEN
    trim(to_char(SUM(mis_nvalue)/1000000,'99999999999990.99')) when
    ref_rpt_format.rpt_seq_code in(40) THEN
    --trim(to_char(SUM(mis_nvalue)/1000000,'99999999999990.99')) when
    trim(to_char(SUM(MIS_NVALUE + LAG(MIS_NVALUE) OVER (MIS_NVALUE ORDER BY YEAR_MONTH)) / 2/1000000,'99999999999990.99')) when
    ref_rpt_format.rpt_seq_code
    in(4,10,11,12,13,14,22,23,24,25,26,27,28,42,48,49,51,53,55,18,19,20) THEN
    trim(to_char( decode(SUM(mis_dvalue),0,0,( SUM(mis_nvalue) /
    SUM(mis_dvalue) )*100 ),'99999999999990.99')) when
    ref_rpt_format.rpt_seq_code in(34,47) THEN
    trim(to_char( decode(SUM(mis_dvalue),0,0,( SUM(mis_nvalue) /
    (SUM(mis_dvalue)/MAX(NO_MONTHS)))*100 ),'99999999999990.99')) when
    ref_rpt_format.rpt_seq_code in(45) THEN
    trim(to_char( decode(SUM(mis_dvalue),0,0,( (1 -
    (SUM(mis_nvalue)/SUM(mis_dvalue))) *100 )),'99999999999990.99' )) when
    ref_rpt_format.rpt_seq_code in(5,6,35,56,57,39) THEN
    trim(to_char( decode(SUM(mis_dvalue),0,0,SUM(mis_nvalue) /
    SUM(mis_dvalue) ),'99999999999990.99')) end case FROM
    REF_RPT_FORMAT,MV_MIS_SOB2 WHERE MIS_ID='1SOB02' AND
    REF_RPT_FORMAT.rpt_seq_code = MV_MIS_SOB2.mis_seq_code AND
    MV_MIS_SOB2.year_month IN ('200303','200302','200301','200212','200211','200210') AND
    REF_RPT_FORMAT.rpt_id = MV_MIS_SOB2.mis_id GROUP BY
    ref_rpt_format.rpt_sort_seq,
    REF_RPT_FORMAT.rpt_seq_code,MV_MIS_SOB2.year_month ORDER BY
    ref_rpt_format.rpt_sort_seq,year_month desc;

    ERROR MESSAGE:
    --------------

    in(4,10,11,12,13,14,22,23,24,25,26,27,28,42,48,49,51,53,55,18,19,20) THEN
    *
    ERROR at line 15:
    ORA-30483: window functions are not allowed here

    Cheers!
    Cheers!
    OraKid.

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Yikes, but you need to learn how to format your SQL.

    Basically, you can't mix and match aggregates and non-aggregates. Windowed Analytical functions, while sharing the name of aggregate functions, are actually *not* aggregate functions. So, you can't say... If the sum of all the records is 5, then return 6, if each record is 5, then return 7.

    You can't have SUM() at the same level as SUM() OVER.

    It's pretty self-evident, so I'm not sure what else to say.

    Also, there is absolutely no point to that nasty TRIM(TO_CHAR thing you're doing. Since we're already dealing with a number, there's no point to converting it to a char so you can trim it. You're only making things slower for no good reason.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Thnx Chris
    I'll show this to my BOSS... hez resposible for ALL this.
    This Query is executed form JAVA.

    One thing I leant now is Don't share ur user_name and pwd

    Now iam screwd
    Cheers!
    OraKid.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width