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
 
Peculiar query

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







PostPosted: Sat Dec 10, 2005 8:45 am    Post subject: Peculiar query
Reply with quote

Dear buddy,

this is a peculiar db2 query I was asked in one interview.
___________________________________________________________
there is a employee table having three columns. those columns are

NAME | SALARY | FLAG
aa..........20.............y
bb..........45............n
xs..........23............y
we.........34.............n
df..........11.............y
tv..........20.............y
a flag is simply 'Y' if salary is paid, and 'N' if not paid.
___________________________________________________________
Then my question is, unfortunately the person who updated the table made wrong.
He should set all 'y's inplace of all 'n's and Vise versa
How to make this correct with a single query.
___________________________________________________________
my table should be like this.
NAME | SALARY | FLAG
aa..........20.............n
bb..........45............y
xs..........23............n
we.........34.............y
df..........11.............n
tv..........20.............n
Back to top

andy6703

New User


Joined: 25 Jul 2005
Posts: 14

PostPosted: Sat Dec 10, 2005 11:43 am    Post subject:
Reply with quote

Hey buddy,

One solution is when u retrieve the table the values will be as per your need which is shown below:

SELECT NAME, SALARY, FLAG,
CASE FLAG
WHEN 'Y' THEN 'N'
ELSE 'Y'
END
FROM EMPLOYEE

This query may not update the table but while retrieving values will meet your current requirements.

Hope this helps.

God Bless!
Back to top
View user's profile Send private message
Jag

New User


Joined: 07 Dec 2005
Posts: 30
Location: India

PostPosted: Sun Dec 11, 2005 11:58 am    Post subject: Hi friend
Reply with quote

you have to follow three steps to get the work done..

1) First write a select query to get the employee names whose salary falg is "N"

Select empname
from emptable
where salaryfalg='N';


2)Update the table to set the salary flag to "N" whose flag is "y" earlier

Update emptable
set salaryfalg="N"
where salaryfalg="y";

This will set the salary flag to "n" whose flg is "y" before.By the query half work is done Right?

3) NOW set the salary falg of the employess (retrieved from 1 step) to "y"

update emptable
set salaryfalg='Y'
where empname="......(empnames fom first step)"


This will complete your job..But if anyone know better small process suggest the person who asked this ? as well as me too tyo improve my skills :-)

Cheers,
Jag.
Back to top
View user's profile Send private message
Guest







PostPosted: Tue Dec 13, 2005 12:17 am    Post subject: Re: Peculiar query
Reply with quote

to andy.

HI buddy thanks for the reply. For selecting it is okay. But how to update?

to jag.

hey thanks for the reply. Imagine yourself when there are millions of records.
Back to top
EnjoyMF

New User


Joined: 27 May 2005
Posts: 88

PostPosted: Tue Dec 13, 2005 11:59 am    Post subject:
Reply with quote

Hi Guys

I suppose we can also use the below query for this particular query, this solves our purpose in a single query,
this can be used in a program or spufi or QMF

update employee
set FLAG =
case
when FLAG = 'y' then 'n'
when area = 'n' then 'y'
end
;


Cheers
Prasad
Back to top
View user's profile Send private message
Guest







PostPosted: Tue Dec 13, 2005 10:53 pm    Post subject: Re: Peculiar query
Reply with quote

hi MR. enjoy,

hope that u r answer will solve the purpose.

Thanks
tvmkishore icon_lol.gif
Back to top
bhaskarbale

New User


Joined: 13 Dec 2005
Posts: 1

PostPosted: Wed Dec 14, 2005 11:04 am    Post subject:
Reply with quote

i want to know quary that will give second highest salary from emp table
Back to top
View user's profile Send private message
khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Wed Dec 14, 2005 11:58 am    Post subject: second highest salary
Reply with quote

Hi frnd,

SELECT MAX(SALARY) FROM EMP WHERE SALARY<(SELECT MAX(SALARY) FROM EMP);

Hope it works
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 HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm

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