Handling_Zero

Hi Expert,

I have a table CLAIM having column contains POLICY and RESULT  

POLICY      RESULT                POLICY      RESULT
-----------  ------------                -----------  ------------
P5                  1                           P5                  1
P4                  0                           P4                  2
P3                  3                           P3                  3
P2                  4                           P2                  0
P1                  5                           P1                  5


looking for O/P
----------------------
-- I want to remove the rows which coming after result "0" contains row. Please suggest me a query for the same.

POLICY      RESULT                          POLICY      RESULT
-----------  ------------                         -----------  ------------
P5                  1                                   P5                  1          
P4                  0                                   P4                  2
                                                         P3                  3
                                                         P2                  0

Thanks in advance .
Please let me know if need any additional info .
MIHIR KAR#Oracle_DB #UNIX beginnerAsked:
Who is Participating?
 
johnsoneSenior Oracle DBACommented:
Given the very minimal amount of information supplied...

This produces the result you are asking for.  However, I seriously doubt it will work the way you want it to
WITH claim (policy, result) 
     AS (SELECT 'P5', 
                1 
         FROM   dual 
         UNION ALL 
         SELECT 'P4', 
                0 
         FROM   dual 
         UNION ALL 
         SELECT 'P3', 
                3 
         FROM   dual 
         UNION ALL 
         SELECT 'P2', 
                4 
         FROM   dual 
         UNION ALL 
         SELECT 'P1', 
                5 
         FROM   dual) 
SELECT policy, 
       result 
FROM   claim 
WHERE  policy >= (SELECT policy 
                  FROM   claim 
                  WHERE  result = 0); 

Open in new window

And ...
WITH claim (policy, result) 
     AS (SELECT 'P5', 
                1 
         FROM   dual 
         UNION ALL 
         SELECT 'P4', 
                2 
         FROM   dual 
         UNION ALL 
         SELECT 'P3', 
                3 
         FROM   dual 
         UNION ALL 
         SELECT 'P2', 
                0 
         FROM   dual 
         UNION ALL 
         SELECT 'P1', 
                5 
         FROM   dual) 
SELECT policy, 
       result 
FROM   claim 
WHERE  policy >= (SELECT policy 
                  FROM   claim 
                  WHERE  result = 0); 

Open in new window

You need to supply better sample data.  I doubt there are only 2 columns in the table, and I'm sure the breakup of the data relies on more than just the 2 columns.
0
 
slightwv (䄆 Netminder) Commented:
Depending on the size of the table, this might perform better since it only hits the table once.

select policy, result from (
select policy, result, min(case when result=0 then policy end) over() min_policy
from claim
)
where policy >= min_policy
/

Open in new window

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.