Peculiar query

Guest · Posted: Sat Dec 10, 2005 8:45 am

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

andy6703 · New User Joined: 25 Jul 2005 Posts: 14

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!

Jag · New User Joined: 07 Dec 2005 Posts: 30 Location: India

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.

Guest · Posted: Tue Dec 13, 2005 12:17 am

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.

EnjoyMF · New User Joined: 27 May 2005 Posts: 88

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

Guest · Posted: Tue Dec 13, 2005 10:53 pm

hi MR. enjoy,

hope that u r answer will solve the purpose.

Thanks
tvmkishore

bhaskarbale · New User Joined: 13 Dec 2005 Posts: 1

i want to know quary that will give second highest salary from emp table

khamarutheen · Active Member Joined: 23 Aug 2005 Posts: 677 Location: NJ

Hi frnd,

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

Hope it works