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#Hadoop #Oracle_DB #UNIX beginnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.