Analytics

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-16-2015
01:56 AM

03-16-2015
01:56 AM

Failed [5484 : HY000]No ordered analytical function allowed in search condition for a joined table

Hi

From the PHRM_TBL.EFF_DT, i want to derive END_DT (next (Max (EFF_DT)-1) and check if CLM_TBL.FILLD_DT is between EFF_DT and END_DT .

I want to derive END_DT as below and compare with Filld_dt from CLM_TBL.

EFF_DT END_DT

2015-02-10 2015-02-24

2015-02-25 2015-02-28

2015-03-01 9999-12-31

I am able to derive the END_DT but not able to compare with Filld_dt.

Used the following query to derive END_DT

Sel EFF_DT, Max (EFF_DT) over (order by EFF_DT rows between current row and 1 following) -1 as END_DT from PHRM_TBL group by EFF_DT.

When i try to join and compare with CLM_TBL.FILLD_DT i get the error

SEL FILLD_DT FROM CLM_TBL C

INNER JOIN

PHRM_TBL P ON

C.FILLD_DT BETWEEN P.EFF_DT AND Max (P.EFF_DT ) over (order by P.EFF_DT rows between current row and 1 following )-1;

Can someone help me in query for retrieving EFF_DT next row value minus with 1 and compare with Filld_dt.

Thanks

2 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-16-2015
10:18 AM

03-16-2015
10:18 AM

OLAP-functions are calculated **after** where/join/group by/having, so you have move it to a Derifed Table:

SEL FILLD_DT FROM CLM_TBL C

INNER JOIN

(

Sel EFF_DT,

Max (EFF_DT)

over (order by EFF_DT

rows between current row and 1 following) -1 as END_DT

from PHRM_TBL group by EFF_DT

) P

ON C.FILLD_DT BETWEEN P.EFF_DT AND P.END_DT;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-19-2015
05:17 AM

03-19-2015
05:17 AM

Thanks a lot..above query worked..:)