Home » SQL & PL/SQL » SQL & PL/SQL » how to restrict set of data in oracle (oracle,12.1.0.2.0 ,windows7)
how to restrict set of data in oracle [message #655232] Thu, 25 August 2016 06:11 Go to next message
saini006
Messages: 9
Registered: July 2008
Location: hyderabad
Junior Member
Hi

below is my query


SELECT AIF.*,
NVL((SELECT 1 FROM ACCOUNT WHERE ACCOUNT_ID=AIF.ACCOUNT_ID),0) REC_SEQ
FROM AIF WHERE IFB_ID=11161


I have data set like below when i run above query

ACCOUNT_ID DESCRIPTION REC_SEQ
10092 dfgdfg 1
10092 aa 1
10092 Testing 1
10092 cash 1
10092 testing screens 1
10092 Spousal RSP 0
10092 Canadian Cash 1



In the above data set I want write the query to restrict if one value in REC_SEQ = 0 then total data set needs to restrict in my query
Can you please help me on this


thanks in advance.
Re: how to restrict set of data in oracle [message #655234 is a reply to message #655232] Thu, 25 August 2016 06:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>In the above data set I want write the query to restrict if one value in REC_SEQ = 0 then total data set needs to restrict in my query
I do not understand what above means.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: how to restrict set of data in oracle [message #655235 is a reply to message #655234] Thu, 25 August 2016 07:37 Go to previous messageGo to next message
Littlefoot
Messages: 21674
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's one option; ACCOUNT_ID 10092 and 99 aren't displayed as at least one row contains REC_SEQ = 0.

SQL> WITH test
  2       AS (SELECT 10092 account_id, 'dfg' description, 1 rec_seq FROM DUAL
  3           UNION
  4           SELECT 10092 account_id, 'aa' description, 1 rec_seq FROM DUAL
  5           UNION
  6           SELECT 10092 account_id, 'Spousal RSP' description, 0 rec_seq
  7             FROM DUAL
  8           UNION
  9           --
 10           SELECT 20 account_id, 'aaa' description, 1 rec_seq FROM DUAL
 11           UNION
 12           SELECT 20 account_id, 'bbb' description, 1 rec_seq FROM DUAL
 13           UNION
 14           --
 15           SELECT 99 account_id, 'cc' description, 0 rec_seq FROM DUAL
 16           UNION
 17           SELECT 99 account_id, 'ddd' description, 0 rec_seq FROM DUAL)
 18  SELECT account_id, description, rec_seq
 19    FROM test
 20   WHERE account_id IN (  SELECT t.account_id
 21                            FROM test t
 22                        GROUP BY account_id
 23                          HAVING 0 =
 24                                    (SELECT COUNT (*)
 25                                       FROM test t1
 26                                      WHERE     t1.account_id = t.account_id
 27                                            AND rec_seq = 0));

ACCOUNT_ID DESCRIPTION    REC_SEQ
---------- ----------- ----------
        20 bbb                  1
        20 aaa                  1

SQL>
Wait for someone else's opinion (as it'll most probably be a better one).
Re: how to restrict set of data in oracle [message #655236 is a reply to message #655232] Thu, 25 August 2016 07:51 Go to previous message
Michel Cadot
Messages: 67879
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Once more:

Quote:
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

And BEFORE posting a new question, feedback to your previous ones explaining how it helps and give the solution, and thank people who spent time o help you.
Previous Topic: Query to get Middle Record from a table
Next Topic: Script Help
Goto Forum:
  


Current Time: Sat Jun 12 22:14:54 CDT 2021