Is there an opposite of the “IN” function?

I have this query:

Select
Sysdate,Sum(Count(P.Init_Dtime))
From Player p,player_source ps
Where
Ps.Group_Id In (44,9,42,15,23,73,45,33,69,63,7,49,96,81,28,57,98,74,92,38) 
And P.Player_Id=Ps.Player_Id 
and
Trunc(p.Init_Dtime) > Trunc(Sysdate) - 7 
And
Trunc(P.Create_Dtime) >= To_Date('2012-mar-01','yyyy-mon-dd')
And Trunc(P.Create_Dtime) < To_Date('2015-sep-9','yyyy-mon-dd')
Group By Trunc(P.Init_Dtime)
Order By Trunc(P.Init_Dtime) Asc

I am using the "IN" function to include only Group_IDs that have certain numbers. The Group_ID column can have a value from 1-100. How do I change this query so the result includes all Group_Id numbers that are not (44,9,42,15,23,73,45,33,69,63,7,49,96,81,28,57,98,74,92,38)?

Answers


As Peter wrote, using the "NOT IN" is the real answer. The question is why you are using this list as a list... I assumed that this list have some meaning and that you may have few lists like this that "grouped" some groups together. If this is the case, you may want to have another table with those groups and do a join between Ps.Group_Id and this new table...


Edit: On another note, If you have indexs on p.Init_Dtime or on P.Create_Dtime, Oracle can not use those indexes because you are using those columns inside a function. You might want to consider how to work without a function (you can add 2 more columns that will hold the truncate values for those column, indexed them as well and use them on the query)


Edit2: If you want a quick and dirty replacing of the list with information from a table, just remember that you can use select inside the not in part. If you have table t1 with col Group_Id and col Valid_Groups, you can do:

... Where 
    Ps.Group_Id In (Select Group_Id from T1 where Valid_Groups = 'true') And ...

Same goes to "not in" ...


You can use NOT to negate the expression.

So you could say :

Where
Ps.Group_Id NOT IN (44,9 ...)

Need Your Help

Which scripting language to support in an existing codebase?

scripting data-driven

I'm looking at adding scripting functionality to an existing codebase and am weighing up the pros/cons of various packages. Lua is probably the most obvious choice, but I was wondering if people ha...

Base64Encoder cuts off last 4 characters of string

actionscript-3 flex flex4 base64

Edit Completely changed question after finding that the problem was elsewhere in the application.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.