filtering for records based on select criteria

padler · March 31

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 source codes match a certain value and not include those in the output dataset. I have attached a document with an example

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

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

SQL Data sample.xlsx

Thanks,

Pierre

Frank Kulash · March 31

Hi,

Do you mean inserting the raw dataset into an sql program? I'm kind of new to this.

Post CREATE TABLE and INSERT statements (or, if you prefer, a WITH clause).

See: How to Ask Questions in Developer Community Spaces - oracle-tech

Frank Kulash · March 31

Hi,

I provided a screenshot of what was contained in the excel spreadsheet.

Always provide sample data in a form that the people who want to help you can use to re-create the problem and test their ideas, such as CREATE TAABLE and INSERT statements. If you think it's helpful, you can post a screen shot in addition to CREATE TABLE and INSERT statements, but not instead of them.

mathguy · March 31

https://community.oracle.com/tech/developers/discussion/comment/16790598#Comment_16790598

Ok, you helped me “see” now: the image in the original post shows both the input data and the sample output.

if your guess is correct (I believe it is), a more efficient solution is to aggregate, and put the condition in the HAVING clause:

select id
from   the_table
group  by id
having count(case when status_code = 'A' then 1 end) = 0
;

Frank Kulash · April 1

Hi, @padler

Would you like to get answers that work, or would you be happy with answers that just cause errors? Make sure the CREATE TABLE and INSERT statements you post work, too. Test (and, if necessary, fix) them before you post them. In Oracle, string literals are enclosed in single-quotes (not double-quotes), and an INSERT statement can only insert one row if it's not selecting from a table. Here are some INSERT statements that work:

INSERT INTO table_sample (CADS_ID, email_type_code, email_status_code) VALUES
(2, 'P', 'I');
INSERT INTO table_sample (CADS_ID, email_type_code, email_status_code) VALUES
(2, 'H', 'A');
INSERT INTO table_sample (CADS_ID, email_type_code, email_status_code) VALUES
(2, 'Q', 'I');
INSERT INTO table_sample (CADS_ID, email_type_code, email_status_code) VALUES
(3, 'P', 'I');
INSERT INTO table_sample (CADS_ID, email_type_code, email_status_code) VALUES
(3, ' ', ' ');
INSERT INTO table_sample (CADS_ID, email_type_code, email_status_code) VALUES
(4, 'P', 'I');

The solution that Mathguy posted yesterday gets the requested results; just change the table- and column names, and (if you want to) add an ORDER BY clause, like this:

select   cads_id
from  	 table_sample
group by cads_id
having 	 count (case when email_status_code = 'A' then 1 end) = 0
order by cads_id
;

Output from the sample data above:

 CADS_ID
--------
       3
       4

If the output should have (at most) one row for each distinct cads_id, then you should say GROUP BY cads_id . The query you posted yesterday had other columns in the GROUP BY clause, including email_status_code itself.

mathguy · April 8

I believe the problem is sufficiently closely related to your original one so it is OK to post it here as a follow-up. How closely is "close enough" is a matter of opinion, of course, but in most cases users here do agree on what is "close enough" vs. "quite different".

In the new data you posted, you have an error, which you could have caught yourself if you tried to copy your code into your editor and tried to execute it. The last column definition is addr_status_code(30) when obviously you meant addr_status_code varchar(30) - you are actually missing the data type, varchar.

Note also, for future reference, that in Oracle (unlike other db products) it is best to use the VARCHAR2 data type rather than VARCHAR, for reasons that have to do with Oracle's non-standard way of treating empty strings (same as NULL in Oracle, unlike other db products and unlike the SQL standard). Oracle keeps promising that they may use VARCHAR in the future for some change, which they do not specify but is generally believed to have to do with empty strings, NULL, and adherence to the standard. Anyway - just use VARCHAR2, never VARCHAR, and you won't have to worry about any of this again.

Your current query is also incomplete - you would have caught that too if you tried to copy from your post and execute. Your query is starging at GROUP BY, you are missing the SELECT and FROM lines.

I added them back, and completed the query to give the desired result. It's based on a concatenation of hard-coded words depending on the same conditions you have in the HAVING clause.

select id_number,
       'no active ' ||
       substr(
         case when count(case when email_status_code     = 'A' then 1 end) = 0 then '/email'     end ||
         case when count(case when telephone_status_code = 'A' then 1 end) = 0 then '/telephone' end ||
         case when count(case when addr_status_code      = 'A' then 1 end) = 0 then '/address'   end
         , 2) as missing_contact_info
from   table_sample e
group  by id_number
having count(case when telephone_status_code = 'A' then 1 end) = 0
    or count(case when addr_status_code      = 'A' then 1 end) = 0
    or count(case when email_status_code     = 'A' then 1 end) = 0
order  by e.id_number;

 ID_NUMBER MISSING_CONTACT_INFO            
---------- ---------------------------------
         2 no active address               
         4 no active telephone/address     
         6 no active email                 
         7 no active email/telephone/address
        10 no active email/telephone       
        11 no active email/address

Frank Kulash · April 8

Hi, @padler

is it ok to submit a follow-up question regarding this query:

Yes, it's okay, but it could be counter-productive. You'll probably get replies faster if you start a new thread. Once a thread has several replies (especially as many as this one), people who haven't already participated in it are less likely to look at it. In this case, since you need to post new sample data, it would be just as easy for you to start a new thread.

Here's one way to do what you requested:

WITH  got_counts  AS
(
	SELECT   id_number
	,        COUNT (CASE WHEN email_status_code     = 'A' THEN 1 END) AS email_cnt
	,        COUNT (CASE WHEN telephone_status_code = 'A' THEN 1 END) AS telephone_cnt
	,        COUNT (CASE WHEN addr_status_code      = 'A' THEN 1 END) AS addr_cnt
	FROM	 table_sample
	GROUP BY id_number
)
SELECT   id_number
,	 'no active ' || RTRIM (  CASE WHEN email_cnt     = 0 THEN 'email/'     END
	 	               || CASE WHEN telephone_cnt = 0 THEN 'telephone/' END
	   	               || CASE WHEN addr_cnt      = 0 THEN 'address/'   END
				, '/'
				) AS missing_contact_info  
FROM	 got_counts
WHERE	 0 IN (email_cnt, telephone_cnt, addr_cnt)
ORDER BY id_number
;

WHERE 0 IN (x, y, z) is just a concise way of saying WHERE x = 0 OR y = 0 OR z = 0.

Most people will test their proposed solutions before posting them. Please show them the same courtesy. Test (and, if necessary, fix) your CREATE TABLE and INSERT statements before you post them. The CREATE TABLE statement you posted has an error (and I don't mean using VARCHAR instead of VARCHAR2).

Frank Kulash · April 8

Hi, @padler

oracle pl/sql says I have insufficient privileges

There's no PL/SQL involved in this problem, just SQL.

If your employer (or teacher) expects you to write queries like this then they should know you need the ability to create small sample tables in a development database. Have them give you the CREATE TABLE system privilege and a small space quota.

Failing that, in many cases, a WITH clause is almost as good as CREATE TABLE and INSERT statements. Instead of

create table table_sample (id_number INT, email_status_code VARCHAR(30));

INSERT INTO table_saample (id_number, email_status_code) VALUES (1, 'A')
INSERT ...

you can say

WITH    table_sample (id_number, email_status_code)  AS
(   SELECT 1, 'A' FROM dual UNION ALL
    SELECT ...
)
SELECT  *
FROM    table_sample;

I wonder if there is a website I can use for free to run sql programs

Live SQL is one: Oracle Live SQL

mathguy · March 31

Are you an IT professional? I ask because any professional who will open an xslx file (or, really, any attachment) from an unknown source on an insecure web site (like this one) should be immediately fired.

If you want us to help, please post your data in the form of CREATE TABLE and INSERT statements, or CREATE TABLE AS SELECT ... statement, or WITH clause.

padler · March 31

I wasn't aware of this. The emails I provided are not real. I just came up with dummy ones so I'm not sure what you'r referring to with the website.

Do you mean inserting the raw dataset into an sql program? I'm kind of new to this.

Thanks,

Pierre

Frank Kulash · March 31

Hi, @padler

For example, say column A has ID "1" and has 3 rows of data. Each row has a different email address and a different source code. The first row has email1 and source code of "A",

In the sample data you posted, there is no column A, and no column with a name resembling "source code", and I don't see the values "A" or "1" anywhere. It would help if you give examples using the sample data you post. (The sample data you post right on this site, of course. You can't expect anyone to open attachments.)

Frank Kulash · March 31

Hi,

Do you mean inserting the raw dataset into an sql program? I'm kind of new to this.

Post CREATE TABLE and INSERT statements (or, if you prefer, a WITH clause).

See: How to Ask Questions in Developer Community Spaces - oracle-tech

mathguy · March 31

https://community.oracle.com/tech/developers/discussion/comment/16790578#Comment_16790578

No, I didn't mean that you are posting email addresses to the public (meaning, a concern about the confidentiality of those emails). Nothing like that.

Rather, the way that viruses, worms and other nasty malware spreads is by people opening attachments from unknown sources on the web (or in email). Anyone who works with computers who doesn't know that, and who opens such attachments without worrying about safety, should work in a different industry.

We don't know you. You may be a crook trying to fool us. That's not likely - but, more likely, you may not follow safe practices yourself (for example: YOU may have no worries about opening attachments from unknown sources), in which case your attachments can be infected, without you even knowing it. Makes sense?

padler · March 31

I appreciate the information and background. However, I don't think the backhanded insults about working in the wrong industry or accusing me of being a crook or being deliberatley reckless is called for. You didn't have to respond to my post if you didn't want to. I didn't know the protocol for this forum and I've particpated in other forums for excel and access and didn't encounter this issue. In addition, I provided a screenshot of what was contained in the excel spreadsheet. Maybe you didn't see the screenshot but I included it as well.

Frank Kulash · March 31

Hi,

I provided a screenshot of what was contained in the excel spreadsheet.

Always provide sample data in a form that the people who want to help you can use to re-create the problem and test their ideas, such as CREATE TAABLE and INSERT statements. If you think it's helpful, you can post a screen shot in addition to CREATE TABLE and INSERT statements, but not instead of them.

mathguy · March 31

Wait - I thought the XLSX file is for input data (sample data) and the screenshot is the output. Did I get that wrong?

I didn't mean to insult you; as I said, I believe it is unlikely that you are a crook. I do believe that anyone who does not follow safe practices should not work in the industry; if you consider that an insult, then I did insult you; not what I wanted to do, but it is an unavoidable side effect. Sorry!

I do sincerely believe that people who share attachments freely on any kind of forum are reckless. If that is an insult, again, - what I said above. Sorry about that.

James Su · March 31

I didn't see your table ddl and sample data with output, so here's the best I can guess from your description:

select * from (

select t.*

,count(case when source_code='A' then 1 end) over(partition by id) as cnt

from my_table t

)

where cnt=0;

mathguy · March 31

https://community.oracle.com/tech/developers/discussion/comment/16790598#Comment_16790598

Ok, you helped me “see” now: the image in the original post shows both the input data and the sample output.

if your guess is correct (I believe it is), a more efficient solution is to aggregate, and put the condition in the HAVING clause:

select id
from   the_table
group  by id
having count(case when status_code = 'A' then 1 end) = 0
;

James Su · March 31

https://community.oracle.com/tech/developers/discussion/comment/16790603#Comment_16790603

Yes, if I only need id in the output, I would use your way.

padler · March 31

Thanks again for your help. I think it's not excluding IDs in the output that have an 'A' status_code in at least one of the rows.

Frank Kulash · March 31

Hi,

I think it's not excluding IDs in the output that have an 'A' status_code in at least one of the rows.

That's what happens when people can't test their ideas: they make the best guess they can, but guessing is not as good as testing.

Post some sample data (CREATE TABLE and INSERT statements), and the results you want from the given sample data. Point out where the query's results are different from what you need, given that data.

mathguy · March 31

https://community.oracle.com/tech/developers/discussion/comment/16790620#Comment_16790620

I think you are asking us to guess again.

If the output you showed in the image in the original post is truly your desired output, there is no reason to change the GROUP BY clause. In my query I had only GROUP BY ID. You changed it to include more columns in GROUP BY. Why?

I assume it is for a reason - it's not a random change. The SELECT columns that you commented out suggest something - you tried to add them to SELECT, but then the query threw an error because those columns are not in GROUP BY. So then you added them to GROUP BY - but that changes the query completely, and now the query doesn't produce the result you expected.

You must make up your mind. Is the Desired Output from your original post, showing only distinct ID's that satisfy your condition, truly the desired output, or is it not? If it is, leave the GROUP BY clause alone, don't add more columns to it. If it is not, please tell us what the desired output really is - I don't see it anywhere. It's possible that James Su's guess was even better than I thought - try his query, perhaps that is the true "desired output" (after a few minor changes you may need to make, for example to exclude the "count" column from the result).

padler · April 1

Hi,

Thanks for your patience while I familiarize myself with the protocols on this community forum. I'm in the process of using the create and insert statements to generate a dataset for you all to look at but am having trouble with the coding. What would be the best way to share the code I am constructing? I understand I can't attach an excel file but do I paste it directly into the comment or do a screenshot?

Thanks,

Pierre

Frank Kulash · April 1

Hi, @padler

do I paste it directly into the comment or do a screenshot?

Paste it directly into the comment. Other people need to run the statements on their own systems, and they can't copy and paste a screen shot.

padler · April 1

create table table_sample (CADS_ID INT, email_type_code VARCHAR(30), email_status_code varchar(30));

INSERT INTO table_sample

(CADS_ID, email_type_code, email_status_code)

VALUES

(2, "P", "I"),

(2, "H", "A")

(2, "Q", "I")

(3, "P", "I")

(3, " ", " ")

(4, "P", "I")

;

/*desired output

because CADS_ID value of 2 has one row of email_status_code of "A", it needs to be excluded from the output. This is the desired output even though the other rows have "I".

CADS_ID

3

4

*/

Frank Kulash · April 1

Hi, @padler

Would you like to get answers that work, or would you be happy with answers that just cause errors? Make sure the CREATE TABLE and INSERT statements you post work, too. Test (and, if necessary, fix) them before you post them. In Oracle, string literals are enclosed in single-quotes (not double-quotes), and an INSERT statement can only insert one row if it's not selecting from a table. Here are some INSERT statements that work:

INSERT INTO table_sample (CADS_ID, email_type_code, email_status_code) VALUES
(2, 'P', 'I');
INSERT INTO table_sample (CADS_ID, email_type_code, email_status_code) VALUES
(2, 'H', 'A');
INSERT INTO table_sample (CADS_ID, email_type_code, email_status_code) VALUES
(2, 'Q', 'I');
INSERT INTO table_sample (CADS_ID, email_type_code, email_status_code) VALUES
(3, 'P', 'I');
INSERT INTO table_sample (CADS_ID, email_type_code, email_status_code) VALUES
(3, ' ', ' ');
INSERT INTO table_sample (CADS_ID, email_type_code, email_status_code) VALUES
(4, 'P', 'I');

The solution that Mathguy posted yesterday gets the requested results; just change the table- and column names, and (if you want to) add an ORDER BY clause, like this:

select   cads_id
from  	 table_sample
group by cads_id
having 	 count (case when email_status_code = 'A' then 1 end) = 0
order by cads_id
;

Output from the sample data above:

 CADS_ID
--------
       3
       4

If the output should have (at most) one row for each distinct cads_id, then you should say GROUP BY cads_id . The query you posted yesterday had other columns in the GROUP BY clause, including email_status_code itself.

padler · April 1

that works, thank you for your help

Frank Kulash · April 1

Hi, @padler

that works, thank you for your help

I'm glad you're question is answered. Whenever you post a problem here, when it gets solved. mark it "Answered". Doing that can help people with similar problems, and save time for people looking for problems to solve.

Here's how to mark the thread as "Answered". At the bottom of each comment it says Did this answer the question? Yes No

After each comment that helped you (e.g., the one where Mathguy posted the correct answer) click on "Yes".

padler · April 8

Hi, is it ok to submit a follow-up question regarding this query: I have refined the query so as to test for 3 different forms of contact information (email, address, telephone) and want to create a new column indicating which if any three forms of active information is missing. I want the output to show any ID where there is no active ('A') telephone OR no active email OR no active address and then a new column that describes which contact field has no active information. I have copied the SQL below as well as desired output based on the table constructed:

create table table_sample (id_number INT, email_status_code VARCHAR(30), telephone_status_code varchar(30), addr_status_code(30));