FAQ
Hi,

I have a following sql in a pl/sql function against oracle 8i and 9i.
However I am only interested to see if count(*) >0 or not for a particular
USERID-COMPANYRID pair. This means, after the sql bulk collect into arrays,
I will loop through the arrays to do something like

if array_count (i) = 0 Then

do something
End if;

Right now this sql will get all the counts (say 165), but I only need one to
make decision. So is there a way to optimize the sql (say using rownum=1
somewhere) so that I would get this:

USERID COMPANYRID COUNT(*)

---------- ---------- ----------

15 7 1
35 7 1
90 7 1
293 7 1
320 7 1
434 7 1
535 7 1

here is the actual result from the query:

SQL> select A.UserId, A.CompanyRid, Count(*)
2 from usercompanyapplications A, (select distinct COMPANYRID,UserId

3 from
usercompanyapplications
4 where Application = 1
5 and UserId in (select

RID

6
from Users
7
where Customer = 1)

8 ) B
9 Where A.UserId = B.UserId
10 and A.CompanyRid = B.CompanyRid

11 and A.Application != 1
12 Group By A.UserId, A.CompanyRid;

USERID COMPANYRID COUNT(*)

---------- ---------- ----------

15 7 7
35 7 5
90 7 5
293 7 7
320 7 165
434 7 165
535 7 9

7 rows selected.

Guang

PRIVILEGED AND CONFIDENTIAL:

This communication, including attachments, is for the exclusive use of
addressee and may contain proprietary, confidential and/or privileged
information. If you are not the intended recipient, any use, copying,
disclosure, dissemination or distribution is strictly prohibited. If you
are not the intended recipient, please notify the sender immediately by
return e-mail, delete this communication and destroy all copies.

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 22, '05 at 10:54a
activeFeb 22, '05 at 10:54a
posts1
users1
websiteoracle.com

1 user in discussion

Guang Mei: 1 post

People

Translate

site design / logo © 2021 Grokbase