Oracle Sql Query - how report policie that only has one set of forms.

This may be very very easy for experts, but I need help with this one.
I have a table that with policyid and form_number as its column.
I want to report policyids that only have form '555.5'.  If other form_numbers exist under that policy then I don't want to report it.
Here's my table:
CREATE TABLE SIMDATA.TEMP_FORM_NUMBERS
(
  POLICYID     NUMBER(11)                       NOT NULL,
  FORM_NUMBER  VARCHAR2(10 BYTE)
)
TABLESPACE SIMDATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
MONITORING;
--- and sample data:

SET DEFINE OFF;
Insert into SIMDATA.TEMP_FORM_NUMBERS
   (POLICYID, FORM_NUMBER)
 Values
   (1952284, '091.0');
Insert into SIMDATA.TEMP_FORM_NUMBERS
   (POLICYID, FORM_NUMBER)
 Values
   (1952284, '091.0');
Insert into SIMDATA.TEMP_FORM_NUMBERS
   (POLICYID, FORM_NUMBER)
 Values
   (1952284, '091.0');
Insert into SIMDATA.TEMP_FORM_NUMBERS
   (POLICYID, FORM_NUMBER)
 Values
   (1952284, '555.5');
Insert into SIMDATA.TEMP_FORM_NUMBERS
   (POLICYID, FORM_NUMBER)
 Values
   (105, '555.5');
Insert into SIMDATA.TEMP_FORM_NUMBERS
   (POLICYID, FORM_NUMBER)
 Values
   (105, '555.5');
Insert into SIMDATA.TEMP_FORM_NUMBERS
   (POLICYID, FORM_NUMBER)
 Values
   (106, '555.5');
Insert into SIMDATA.TEMP_FORM_NUMBERS
   (POLICYID, FORM_NUMBER)
 Values
   (107, '555.5');
Insert into SIMDATA.TEMP_FORM_NUMBERS
   (POLICYID, FORM_NUMBER)
 Values
   (107, '323.0');
COMMIT;
---
Please help thanks.
ALad2005Asked:
Who is Participating?
 
Jan LouwerensSoftware EngineerCommented:
Here's a couple of other ways to write that query:

select * from TEMP_FORM_NUMBERS TFN1 where
   FORM_NUMBER = '555.5' and
   not exists
   (
      select 0 from TEMP_FORM_NUMBERS TFN2 where
         TFN2.POLICYID = TFN1.POLICYID AND
         FORM_NUMBER != '555.5'
   )

Open in new window


select POLICYID, FORM_NUMBER from
(
   select POLICYID, FORM_NUMBER,
      count(distinct(FORM_NUMBER)) over (partition by POLICYID) as POLICY_COUNT
   from
      TEMP_FORM_NUMBERS
)
where
   FORM_NUMBER = '555.5' and
   POLICY_COUNT = 1

Open in new window

0
 
Jan LouwerensSoftware EngineerCommented:
select * from TEMP_FORM_NUMBERS where
   POLICYID = '555.5'

Open in new window

0
 
ALad2005Author Commented:
nope, that will report policy id that has 555.5 and other form as well.
I don't want that.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jan LouwerensSoftware EngineerCommented:
I think I misunderstood the question.

Is this more like what you're asking for?

select * from TEMP_FORM_NUMBERS where
   POLICYID = '555.5' and
   FORM_NUMBER not in
   (
      select FORM_NUMBER from TEMP_FORM_NUMBERS where
         POLICYID != '555.5'
   )

Open in new window

0
 
ALad2005Author Commented:
Thx Jan this may work,   but I think policyid, form_number should be swapped.
I have over 1000, 000 records.
Is there  fast query, as this one will take lot of time.
Thanks.
Al
0
 
ALad2005Author Commented:
Thank you v much
0
 
ALad2005Author Commented:
th;ank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.