Re: help with select statement

From: Gerard H. Pille <ghp_at_skynet.be>
Date: Mon, 19 Jan 2009 21:03:03 +0100
Message-ID: <4974dc76$0$2863$ba620e4c_at_news.skynet.be>



wibni schreef:
> Brilliant! It works! Thanks Gerard!
>
> I also got it working in an analytic function but Gerard's statement
> is actually quicker then my function. I always thought a function is
> the fastest way.
> Never mind! Thanks again all!
>
> select distinct name, swcode
> from
> (select name, swcode,
> count(case when relkeyword.swcode = 173 then 1 end) over (partition by
> cscontact.name) as cnt173
> from cscontact,relkeyword
> where cscontact.pubindex = relkeyword.relpubindex(+))
> where cnt173 = 0
> order by name

Joel's remark got me thinking: my "distinct" is hiding information in case the name is not unique. So drop the distinct, and add the pubindex to the select.

Using a stored function in a where-clause is the greatest evil since the apple. Watch your cpu's during a big select: they start glowing in the dark. I am not talking about SQL built-in functions, they are great. But if Oracle has to call a stored procedure, function, package or user type for each row, you are history. Received on Mon Jan 19 2009 - 14:03:03 CST

Original text of this message