Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
My table say EMP has rows as below

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sivatechdrive

Active User


Joined: 17 Oct 2004
Posts: 190
Location: hyderabad

PostPosted: Fri Oct 31, 2008 12:11 pm    Post subject: My table say EMP has rows as below
Reply with quote

Hi All,

I am in requirement of query as below, can some one help me.
My table say EMP has rows as below
Code:
EMPNO  EMPNAME
100        A
200        B
300       C

If i write a select as below
Code:
SELECT EMPNO from EMP where EMPNO in (100,250,300,500)
in general the output is
Code:
EMPNO
100
300

But i want the output as below
Code:
EMPNO   
100           
250       Not Found in table
300       
500       Not Found in table

Thanks ..prasad


Edited: Please use BBcode when You post some code, that's rather readable...Anuj
Back to top
View user's profile Send private message

nabarundas

New User


Joined: 21 Jun 2007
Posts: 28
Location: pune

PostPosted: Tue Nov 04, 2008 12:37 pm    Post subject:
Reply with quote

Hi sivatechdrive,

I can suggest you one query which acts in the reverse way

Code:
select empid,
            case when empid in ('1','2') then 'found in the list'
                             else 'not found in the list'
                    end as status
            from nabarun.tablea
   


You can have a look at it and see whether it will be helpful or not.

But I am not exactly sure whether only query can bring your desired output.
Back to top
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 356
Location: New York

PostPosted: Tue Nov 04, 2008 2:18 pm    Post subject:
Reply with quote

nabarundas,

Did you execute your query? How can you display '250' and '500' which are not present in table?
Back to top
View user's profile Send private message
nabarundas

New User


Joined: 21 Jun 2007
Posts: 28
Location: pune

PostPosted: Tue Nov 04, 2008 2:28 pm    Post subject:
Reply with quote

Hi ksk,

By the query we are not displaying '250' and '500'.

In the first line itself I have writen that it acts in the reverse way(display the EMPNO from the Table not from the list)

And the query was not the solution of the problem(I already mention that).
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Nov 04, 2008 9:41 pm    Post subject:
Reply with quote

iS the IN clause values always constant ?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts Conditional highlighting of rows in I... packerm CLIST & REXX 2 Thu Sep 21, 2017 6:50 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us