Unable to Get the correct results

Question

Post reply

Unable to Get the correct results

sekhar.gunta

Newbie

Points: 7
More actions
September 18, 2010 at 8:44 am

#226577

Select ((TOT_FCTED_DOL_CUR_3_MTH_AMT/SUM(TOT_FCTED_DOL_CUR_3_MTH_AMT)- (TOT_FCTED_DOL_CUR_12_MTH_AMT/SUM(TOT_FCTED_DOL_CUR_12_MTH_AMT)))) OVER (PARTITION BY TERR_CD, FCLT_IMS_ORG_ID, IMS_RXER_NBR, MKT_SEQ_NBR) AVG_3_MTH_VS_AVG_12_MTH_PCT_CHG,
From A
It's showing an error message like
Error:Incorecct syntax near over.
Please help me

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

The OVER () clause only works directly related to a SUM() or any other aggreagtion.

You can't apply it to (more or less) complex calculations.

What you could try is adding the OVER clause to each aggregation:

Select

(TOT_FCTED_DOL_CUR_3_MTH_AMT

/(SUM(TOT_FCTED_DOL_CUR_3_MTH_AMT) OVER (PARTITION BY TERR_CD, FCLT_IMS_ORG_ID, IMS_RXER_NBR, MKT_SEQ_NBR))

- (TOT_FCTED_DOL_CUR_12_MTH_AMT

/(SUM(TOT_FCTED_DOL_CUR_12_MTH_AMT) OVER (PARTITION BY TERR_CD, FCLT_IMS_ORG_ID, IMS_RXER_NBR, MKT_SEQ_NBR))

)

) AS AVG_3_MTH_VS_AVG_12_MTH_PCT_CHG,

Side note:

You might want to consider reformatting queries with rather long statements to improve readability...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 2

LutzM (9/18/2010)
Side note:
You might want to consider reformatting queries with rather long statements to improve readability...

Since it's all upper case with a pot wad of underscores, I wonder if this is actually an Oracle question. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)