how to restrict set of data in oracle [message #655232] |
Thu, 25 August 2016 06:11 |
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 #655235 is a reply to message #655234] |
Thu, 25 August 2016 07:37 |
|
Littlefoot
Messages: 21811 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 |
|
Michel Cadot
Messages: 68665 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.
|
|
|