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
 

 

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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


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