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
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.
Reply With Quote
Bookmarks