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.