Toad World® Forums

Oracle query excluding certain ID's in count

Hello,

I am trying to count records in a table and exclude 22 ID’s from the count. From what I have looked up this should work, but doesn’t. Any help is appreciated. Using Toad Data Point 4.2.0.207, if that is needed.

SELECT COUNT(ID)

FROM TABLE A

WHERE ID NOT IN (‘0x1234’, ‘0x12345’, etc…);

Also tried it this way

SELECT COUNT(ID)

FROM TABLE A

WHERE ID <> ‘0x1234’ AND ID <> ‘0x12345’ etc

Thanks Dan

I can never get that to work either.

You can create a standalone table with the IDs in then link the tables and say you only want to pull the records that don’t match.

From: dnieb [mailto:bounce-danielniebuhr1@toadworld.com]

Sent: Thursday, August 17, 2017 10:04 AM

To: toaddatapoint@toadworld.com

Subject: [Toad Data Point - Discussion Forum] Oracle query excluding certain ID’s in count

Oracle query excluding certain ID’s in count

Thread created by dnieb

Hello,

I am trying to count records in a table and exclude 22 ID’s from the count. From what I have looked up this should work, but doesn’t. Any help is appreciated. Using Toad Data Point 4.2.0.207, if that is needed.

SELECT COUNT(ID)

FROM TABLE A

WHERE ID NOT IN (‘0x1234’, ‘0x12345’, etc…);

Also tried it this way

SELECT COUNT(ID)

FROM TABLE A

WHERE ID <> ‘0x1234’ AND ID <> ‘0x12345’ etc

Thanks Dan

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point Forum
notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

Thanks for the response. That is not good news to hear.

Syntax seems to be okay to me. Makes me wonder if the issue is in the WHERE values (maybe your system is case sensitive?).

What happens if you SELECT * FROM TABLEA WHERE ID = ‘0x1234’?

If the single record does not return, then the values that you are providing in the WHERE clause are not in the TABLEA.ID column.

Strange, using Toad for Oracle I did this query …

SELECT COUNT(MYID) FROM MYTABLE;

… and got back 12. Then I issued …

SELECT COUNT(MYID) FROM MYTABLE WHERE MYID NOT IN (1)

… and got 11, so it appears to work. Perhaps the ids you have in the IN clause are not matching, which would mean the counts would be the same.

Try SELECT COUNT(ID) FROM A WHERE ID IN (‘0x1234’,‘0x12345’, etc…) and make sure you get a count of 22.

Edit: Beaten to the punch by InfoMan. [:P]

It’s something with the NOT IN (‘0x1234’, ‘0x12345’, etc…);

Where you can’t use multiple with NOT… in works fine, but it won’t let you use the NOT with the IN

From: hharwood [mailto:bounce-hharwood@toadworld.com]

Sent: Thursday, August 17, 2017 10:34 AM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] Oracle query excluding certain ID’s in count

RE: Oracle query excluding certain ID’s in count

Reply by hharwood

Strange, using Toad for Oracle I did this query …

SELECT COUNT(MYID) FROM MYTABLE;

… and got back 12. Then I issued …

SELECT COUNT(MYID) FROM MYTABLE WHERE MYID NOT IN (1)

… and got 11, so it appears to work. Perhaps the ids you have in the IN clause are not matching, which would mean the counts would be the same.

Try SELECT COUNT(ID) FROM A WHERE ID IN (‘0x1234’,‘0x12345’, etc…) and make sure you get a count of 22.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point Forum
notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

NOT IN with multiple values is permitted. The explicit purpose of IN and NOT IN is that they are to be used with multiple values, instead of using multiple AND <>'s.

I have used NOT IN, with multiple values, hitting SQL Server, Oracle, MySQL, Maria, and DB2. It’s standard syntax.

I realize that, but it usually won’t work for me in Toad

From: InfoMan [mailto:bounce-InfoMan@toadworld.com]

Sent: Thursday, August 17, 2017 10:57 AM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] Oracle query excluding certain ID’s in count

RE: Oracle query excluding certain ID’s in count

Reply by InfoMan

NOT IN with multiple values is permitted. The explicit purpose of IN and NOT IN is that they are to be used with multiple values, instead of using multiple AND <>'s.

I have used NOT IN, with multiple values, hitting SQL Server, Oracle, MySQL, Maria, and DB2. It’s standard syntax.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point Forum
notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

I selected all the records from the table and grabbed a random record. Then, tried bringing back only that record. Nothing came back. So, i think it is something to do with database table. Since i could see data with everything but not with just that 1 record.

Hello,

I was talking to someone at work and they said that if you get rid of the 0x. It does work. The data type is RAW. Anyone have an idea why this would happen with that type of field. No experience with this type of field.

Thanks Dan

Dan,

Looks like to me that the database ID numbers you are using are coded in hexadecimal.

See: http://www.hexadecimaldictionary.com/hexadecimal/0x3412/

Then, depending on your database, putting this number in quotes in your WHERE clause may turn it into a string instead of a number which is the wrong type for your index.

Marc

Update to the RAW datatype. This is what was causing the problem. After searching online. Here is the solution to move datatype to HEX.

Use RAWTOHEX(ID) - this converts RAW data type into HEX format which then you can use for queries. It works!! the ‘0x’ it puts in front of the rest of the values. When doing RAWTOHEX is removes the ‘0x’ from the data.

Thanks for your response. See my last response to this. Problem was the RAW datatype. I needed to convert to HEX.