MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Searching value NOT IN a tabel from a list

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
maxisnowhere
Beginner


Joined: 25 May 2005
Posts: 59
Topics: 20
PostPosted: Wed Feb 14, 2007 3:34 am    Post subject: Searching value NOT IN a tabel from a list Reply with quote
Hi Guys,

I've a question and hope you can help me.

I have a table TAB1 like this:
Code:

COL1     COL2
------     -------
1           AAA
2           BBB
3           CCC
4           DDD


and a list of possible value for COL1 like

Code:
(1,2,3,4,5,6,7,8,9)


I would like to find out witch values of the list are NOT in COL1 of TAB1.

ehehe I could do a simply Pli program but I would like to do it with just a query.

Any idea?

Thank for all contribution!

Have a good one
_________________
Maxisnowhere
Back to top
View user's profile Send private message
maxisnowhere
Beginner


Joined: 25 May 2005
Posts: 59
Topics: 20
PostPosted: Wed Feb 14, 2007 3:49 am    Post subject: Reply with quote
Ops, I forgot: im'working with DB2! Very Happy
_________________
Maxisnowhere
Back to top
View user's profile Send private message
hareshh
Beginner


Joined: 13 Dec 2006
Posts: 16
Topics: 0
PostPosted: Wed Feb 14, 2007 4:26 am    Post subject: Reply with quote
Hi Maxisnowhere,

If your list is a column in a table for example COL3 of TAB2 then please refer link for solution

Arrow http://www.mvsforums.com/helpboards/viewtopic.php?t=4121&view=next


Regards,
Haresh Mr. Green
Back to top
View user's profile Send private message
maxisnowhere
Beginner


Joined: 25 May 2005
Posts: 59
Topics: 20
PostPosted: Wed Feb 14, 2007 4:54 am    Post subject: Reply with quote
Hi Haresh,

unfortunately it is just a list. I was trying to make them a table using something like:
Code:

(select 1 from sysibm.sysdummy1) union
(select 2 from sysibm.sysdummy1) union
(select 3 from sysibm.sysdummy1) union
(select 4 from sysibm.sysdummy1) union
(select 5 from sysibm.sysdummy1) union
(select 6 from sysibm.sysdummy1) union
(select 7 from sysibm.sysdummy1) union
(select 8 from sysibm.sysdummy1) union
(select 7 from sysibm.sysdummy1)


but I am having some problem by managing that. I'm not sure mine is a good solution...

Thank 4 your help!
_________________
Maxisnowhere
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
PostPosted: Wed Feb 14, 2007 6:43 am    Post subject: Reply with quote
maxisnowhere,

Try this untested sql
Code:

SELECT A.MISING_VAL FROM                                 
 TABLE (SELECT 1 FROM SYSIBM.SYSDUMMY1                   
         UNION                                           
         SELECT 2 FROM SYSIBM.SYSDUMMY1                 
         UNION                                           
         SELECT 3 FROM SYSIBM.SYSDUMMY1                 
         UNION                                           
         SELECT 4 FROM SYSIBM.SYSDUMMY1                 
         UNION                                           
         SELECT 5 FROM SYSIBM.SYSDUMMY1                 
         UNION                                           
         SELECT 6 FROM SYSIBM.SYSDUMMY1                 
         UNION                                           
         SELECT 7 FROM SYSIBM.SYSDUMMY1                 
         UNION                                           
         SELECT 8 FROM SYSIBM.SYSDUMMY1                 
         UNION                                           
         SELECT 9 FROM SYSIBM.SYSDUMMY1) AS A(MISING_VAL)
 WHERE A.MISING_VAL NOT IN (SELECT DISTINCT COL1 FROM TAB1)
 ;       


Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
maxisnowhere
Beginner


Joined: 25 May 2005
Posts: 59
Topics: 20
PostPosted: Wed Feb 14, 2007 7:54 am    Post subject: Reply with quote
hi Kolusu,

it fonction!

this is a good solution, now I want to find out how to define the
Code:
TABLE
more efficiently, but this is my job!

Thank you very much!!!!

have a good one
_________________
Maxisnowhere
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
PostPosted: Wed Feb 14, 2007 10:02 am    Post subject: Reply with quote
Quote:

this is a good solution, now I want to find out how to define the table
more efficiently, but this is my job!

maxisnowhere,

Efficiency would depend if COl1 has an index defined, or how big the table is and many other factors to be considered. Why don't you run an explain on the statement and see the cost of the statement.

Here is an alternate way of getting the desired results.

Code:

SELECT A.COL1                                     
  FROM (SELECT COL1 AS COL1                       
          FROM TAB1                               
         UNION ALL                                 
        SELECT 1 AS COL1 FROM SYSIBM.SYSDUMMY1     
         UNION ALL                                 
        SELECT 2 AS COL1 FROM SYSIBM.SYSDUMMY1     
         UNION ALL                                 
        SELECT 3 AS COL1 FROM SYSIBM.SYSDUMMY1     
         UNION ALL                                 
        SELECT 4 AS COL1 FROM SYSIBM.SYSDUMMY1     
         UNION ALL                                 
        SELECT 5 AS COL1 FROM SYSIBM.SYSDUMMY1     
         UNION ALL                                 
        SELECT 6 AS COL1 FROM SYSIBM.SYSDUMMY1     
         UNION ALL                                 
        SELECT 7 AS COL1 FROM SYSIBM.SYSDUMMY1     
         UNION ALL                                 
        SELECT 8 AS COL1 FROM SYSIBM.SYSDUMMY1     
         UNION ALL                                 
        SELECT 9 AS COL1 FROM SYSIBM.SYSDUMMY1) A 
GROUP BY A.COL1 HAVING COUNT(*) = 1               


Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
maxisnowhere
Beginner


Joined: 25 May 2005
Posts: 59
Topics: 20
PostPosted: Wed Feb 14, 2007 11:04 am    Post subject: Reply with quote
Thank you! You have really helped me !!!!

The performance is not really the problem, just my list is with 150 number and using the sysibm,.sysdummy1 bla bla is quite long.

See ya!!

Wih u a wonderfull ST Valantine day!!!
_________________
Maxisnowhere
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
PostPosted: Wed Feb 14, 2007 11:31 am    Post subject: Reply with quote
maxisnowhere,

What is the format of col1 and list all the possible 150 values , may be there is an alternative way to do it

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
maxisnowhere
Beginner


Joined: 25 May 2005
Posts: 59
Topics: 20
PostPosted: Wed Feb 14, 2007 11:47 am    Post subject: Reply with quote
kolusu,
col1 1 is a DECIMAL 11 with such values (here just some):
Code:

12304174002
12304174002
12304174505
12304344007
12302343406


Thanks a lot
_________________
Maxisnowhere
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group