Analytic function and where clause

Asked Feb 26 '15 at 20:41

Active Feb 26 '15 at 20:41

Viewed 60 times

I have simple code like:

Select distinct a.Symbol,b.Symbol, corr(a.logp,b.logp) 
over (partition by a.symbol,b.symbol)  Correlation
    From logprofittest a, logprofittest b
    where a.Symbol<>b.symbol
    And  To_date(a.Tdate,'YYYYMMDD') between  DATE'2013-12-01' AND DATE'2013-12-31'
    and  To_date(b.Tdate,'YYYYMMDD') between  DATE'2013-12-01' AND DATE'2013-12-31'
    and  To_date(a.Tdate,'YYYYMMDD')=To_date(b.Tdate,'YYYYMMDD')
    and correlation>0.5
    Order by correlation desc;

However, error says correlation>0.5 is wrong, how to fix it please?

asked Feb 26 '15 at 20:41

user4441082

1

Are you trying to use **field alias** in a `WHERE` clause? – PM 77-1 Feb 26 '15 at 20:45
See if [this helps](http://stackoverflow.com/questions/260399/using-an-alias-column-in-the-where-clause-in-ms-sql-2000) – mustaccio Feb 26 '15 at 20:47
Thanks for all your reply. I create a new a table to avoid this error – user4441082 Feb 26 '15 at 20:53

Analytic function and where clause

0 Answers0