Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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 unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us