Want to remove some data values on the basis of count second column

General
Enthusiast

Want to remove some data values on the basis of count second column

Hi,

I have below query

sel ent_cust_id , count(*)

from UD466.TAR_Inst a,

SECURED.EC_CUST_SUMRY b

where a.acct_id=b.acct_id

and a.sor_id=b.sor_id

and b.ent_cust_id is not null

group by 1 having count(*) >10

I want to remove all the acct_id from UD466.TAR_Inst table where ent_cust_id  are coming more than 10 times but in UD466.TAR_Inst  table there is no ENT_CUST_ID column , i can write query by using simple sub queries but i want to write OLAP query using Partition by clause.

Please suggest something?

Tags (1)
3 REPLIES
Enthusiast

Re: Want to remove some data values on the basis of count second column

Thanks

Enthusiast

Re: Want to remove some data values on the basis of count second column

If you can solve it using simple subqueries, you should solve it that way.  These things tend to get complex enough without developers trying to prove their SQL skills.

The subtitle and first section of every datawarehouse standards document should be, "Everything should be kept as simple as possible." 

Always keep the sanity of the run/fix maintenance coder that has to maintain your code in mind when you write it.  You never know when you might run into them in a dark alley....

:-)

Enthusiast

Re: Want to remove some data values on the basis of count second column

Thanks Vande:)

i will write subquery , but only point of asking solution OLAP query if i get  into some other complex scenario which need OLAP solution then. I may not need to post another post.

Please can you give me the syntax of writing that query.

Thanks,

Nilesh