Home » SQL & PL/SQL » SQL & PL/SQL » filtering for records based on select criteria (oracle plsql)
filtering for records based on select criteria [message #684069] Wed, 31 March 2021 15:22 Go to next message
padler
Messages: 1
Registered: March 2021
Junior Member
/foru/forum/fa/14508/0/

Hi,

I'm looking to build a sql query that will filter for IDs where the IDs are in multiple rows each with different emails and email source codes and want to test whether any of the email source codes match a certain value and not include those in the output dataset. I have attached a screenshot and pasted the dataset below.

For example, say column A has ID "111" and has 3 rows of data. Each row has a different email address and a different email source code. The first row has email1 and email source code of "Active", the second row has email2 and email source code of "Inactive", and the 3rd row has no email or email source code. In this case, I want to test if ID 111 has any email source code of "Active" and exclude the ID in the output dataset because it includes the value of "Active". In this case, this should be excluded from the output because one of the rows has "Active" as the email source code

Alternatively, let's say there is another ID of "222" with 3 rows. The first row has email3 and email source code of "Inactive", the second row has email4 and email source code of "Inactive", and the third row has no email or email source code. In this case, because none of the rows for ID of "222" has a value of "Active", I want to INCLUDE it in my output dataset.

Thanks,
Pierre
/foru/forum/fa/14508/0/



Raw Data Set ID EMAIL email source code Desired Output ID
111 email1 Active 222
111 email2 Inactive
111
222 email3 Inactive
222 email4 Inactive
222



  • Attachment: Capture1.PNG
    (Size: 21.10KB, Downloaded 111 times)
Re: filtering for records based on select criteria [message #684071 is a reply to message #684069] Thu, 01 April 2021 00:09 Go to previous messageGo to next message
Michel Cadot
Messages: 67881
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often 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.

Re: filtering for records based on select criteria [message #684075 is a reply to message #684069] Thu, 01 April 2021 09:23 Go to previous message
John Watson
Messages: 8559
Registered: January 2010
Location: Global Village
Senior Member
Without the test case, I can't help much. However, the way I would approach it would be to use a compound query. The first subquery would select all the IDs that are not active, then MINUS a subquery that selects all the IDs that are active. Aggregate to remove the duplicates.

Have a go! There are probably more sophisticated solutions.

--update: come to think of it, you won't need to aggregate. The minus will already have removed duplicates.

[Updated on: Thu, 01 April 2021 09:27]

Report message to a moderator

Previous Topic: Disperse credit notes between groups of invoices
Next Topic: Query PL/SQL Urgent
Goto Forum:
  


Current Time: Tue Jun 15 02:41:29 CDT 2021